sql: how to improve this statment.

Question

Hi,

I have the following sql statement that I need to make quicker. There are 500k rows, and I an index for 'HARDWARE_ID', but this still take up to a second to perform.

Does anyone have any ideas?

    select 
        * 
    from 
        DEVICE_MONITOR DM 
    where 
            DM.DM_ID = (
        select 
            max(DM_ID)
        from 
            DEVICE_MONITOR  
        where
            HARDWARE_ID=#value#
    ) 

---

I've found the following index is also a great help...

CREATE INDEX DM_IX4 ON DEVICE_MONITOR (DM_ID, HARDWARE_ID);

In my test it drops the runtime from 26seconds to 20 seconds.

Thanks for all your help.

Answer 1

If you have a clustered index on DM_ID, then that looks like the fastest query.

Edit: Ack. Daniel has the correct answer. Missed that.

Answer 2

Consider changing '*' to only list of attributes you need. Very often this can give you substantial increase in speed.

Answer 3

The index for the DM_ID should be created as asc

The problem might be in this that You found very fast match form HARDWARE_ID, but then those records have to sorted to fetch max from them and this operation is time consuming.

Try to compare this statements:

1    #result = select max(DM_ID) from DEVICE_MONITOR where HARDWARE_ID=#value#

2    select * from  DEVICE_MONITOR DM where DM.DM_ID = #result

The query 1 is the problem, as you shall see that the 2 is working faster

if the index is created, and the query still works slowly than, you may update the statistics. But other queries will probably work only slower.

If is possible instead of * use only column that You really need