CODEWITHSUNDEEP
sqlperformancet-sql
Christopher
Christopher

Reputation: 121

Performance tuning on ORDER BY statement with filter for one result

I have the following simple query:

SELECT TOP 1 message 
FROM Log l 
WHERE l.id_fk = '##Guid##'
AND l.id_category IN ('Category 1', 'Category 2') 
ORDER BY l.timestamp DESC

The execution time on large Log tables without an nonclustered descending index on timestamp is very long because the complete list has to be ordered first to get the top 1 entry.

Is it possible to get rid of the order by statement or is creating the index completely the best solution?

Upvotes: 0

Views: 72

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

The query is fine. You want this index:

 CREATE INDEX idx ON log (id_fk, id_category, timestamp DESC);

Or even this:

 CREATE INDEX idx ON log (id_fk, id_category, timestamp DESC, message);

Thus the greatest timestamp for the two categories can be quickly looked up. With the latter index it is not even necessary to read the table.

Upvotes: 0

Sparky
Sparky

Reputation: 15075

Try this:

SELECT TOP 1 message 
FROM (select l.timestamp,l.message
        FROM Log l 
        WHERE l.id_fk = '##Guid##'
        AND l.id_category IN ('Category 1', 'Category 2') 
        ) x
ORDER BY x.timestamp DESC

Just written off top of head, basically the inner query filters your data conditions to a smaller set, then orders to get the top 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think the fastest approach is this more complex query:

SELECT TOP (1) message
FROM ((SELECT TOP 1 l.message, l.timestamp
       FROM Log l 
       WHERE l.id_fk = '##Guid##' AND
             l.id_category = 'Category 1'
       ORDER BY l.timestamp DESC
      ) UNION ALL
      (SELECT TOP 1 l.message, l.timestamp
       FROM Log l 
       WHERE l.id_fk = '##Guid##' AND
             l.id_category = 'Category 2'
       ORDER BY l.timestamp DESC
      ) 
     ) l
ORDER BY timestamp DESC

This can take advantage of an index on log(id_fk, id_category, l.timestamp). Unfortunately, SQL cannot use the index on timestamp with IN.

Upvotes: 1

Related Questions