开发者

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.

SQL Server, trying to optimize my query, using TOP, greater than and order by?


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜