SQL Server, trying to optimize my query, using TOP, greater than and order by?
This is my query
SELECT TOP 3 guid FROM event开发者_如何学编程log
WHERE (guid > 2291399 AND batch_uid = -1) ORDER BY date_created ASC
I'm running this qquery inside a stored procedure cursor about 25 times. Which causes the SP to run for 10 seconds plus.
Can someone advise me where I could optimize my query ?
I have tried the following indexes
CREATE INDEX eventlog_003 ON eventlog (batch_uid,date_created,guid);
CREATE INDEX eventlog_004 ON eventlog (date_created,guid);
CREATE INDEX eventlog_005 ON eventlog (guid,batch_uid,date_created ASC);
CREATE INDEX eventlog_006 ON eventlog (batch_uid,date_created ASC,guid);
Heres some stats.
It looks like most of the time is spent doing that index scan. I found some info in this question. In short, it is better to get the database to try and do an index seek rather than a scan (a scan requiring checking of fields in the table that aren't in the index). It looks like one way you could reduce the time taken would be to create an ascending index on guid
, batch_uid
and date_created
.
精彩评论