0

I'm struggling with a query that runs fast when ORDER BY is ASC but not when it's DESC.

Pseudo code:

TABLE Logs (
   Id UNIQUEIDENTIFIER,
   Time DateTime,
   AccountId UNIQUEIDENTIFIER,
   Deleted BIT,
   /* Other columns */
)

Because this is a multi-tenant system, Logs are inserted with Time ascending most of time and queries are with Time descending most of the time, I also have:

CLUSTER INDEX 
FOR TABLE Logs
(Deleted ASC, AccountId ASC, Time ASC)

The problem is now with the following query:

SELECT TOP 100 *
FROM Logs
WHERE Deleted = 0
AND   AccountId = 'id-of-an-account'
ORDER BY Time DESC

That query is slow (17 secs), but if I change to ORDER BY Time ASC it runs in <1seg.

I've been reading I think I can change the CLUSTER INDEX to be DESC on Time. However, based on this answer, that can create a lot of fragmentation. I could also create another non-clustered index, but that would impact the inserts performance.

Running SQL Server 2008 R2. Any idea? Thanks.

UPDATE:

The query is actually slow because of a combination of what I've mentioned and that I have an INNER JOIN, the actual query is something like this:

SELECT TOP 100 *
FROM Logs l
INNER JOIN Other o
    ON o.Id = l.Id
WHERE Deleted = 0
AND   AccountId = 'id-of-an-account'
ORDER BY Time DESC

I believe that the INNER JOIN is not letting the query use the index.

Community
  • 1
  • 1
Diego Jancic
  • 7,280
  • 7
  • 52
  • 80
  • Adding a non-clustered index just on [Time] DESC would have a negligible impact on INSERT performance. Have you looked at the Execution plan to verify that the CI is being used in the slow query? – Tab Alleman Dec 08 '15 at 18:12
  • @TabAlleman yes, it's funny that the execution plan does not even change (at least I didn't find any differences) and the estimated time is the same (50% if I put both queries). The index you suggested is fine but it would need to be on Deleted, AccountId, Time I believe. – Diego Jancic Dec 08 '15 at 18:20
  • if you're using SSMS, just include the actual execution plan and it will normally tell you if an index will increase the performance – JamieD77 Dec 08 '15 at 19:08

0 Answers0