Sql: how to improve this status

Question

I have the following sql statement, which I need to do faster. There are 500k rows, and I have an index for HARDWARE_ID, but it still takes up to a second to execute.

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 found the following index, also great help ...

CREATE INDEX DM_IX4 ON DEVICE_MONITOR (DM_ID, HARDWARE_ID);

In my test, it disables runtime from 26 seconds to 20 seconds.

Thank you for your help.

Answer 1

Index for DM_ID must be created as asc

The problem may be that you found a very quick form of matching HARDWARE_ID, but then these records must be sorted to get the most out of them, and this operation takes a lot of time.

:

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

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

1 , , 2

, - , . , , .

* ,

Answer 2

"*" . .

Answer 3

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

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