looking for ideas to speed up SQL

[pauljt]

Have a piece of SQL:

select INSTNUM,'',IDXCOD,INSTFREE,INSTFREE2,YLD
from HIST h where DAT =
(select max(DAT) from HIST h2 where h.INSTNUM = h2.INSTNUM)
go

which runs fine, albeit a bit slow.


An unique clustered index exists for
INSTNUM, DAT

The subquery uses this, the outer query does not.


Just looking for ideas! to speed it up if possible.

 

[Cagliostro]

sometimes you can do some query to use the index if you put it in orderby.

Ion Filipski

 

[nigelrivett]

Depends on the database but maybe an index on DAT would be used for the outer query.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.