CODEWITHSUNDEEP
sql-serverquery-performance
Maury Markowitz
Maury Markowitz

Reputation: 9279

SQL Server ORDER BY seems surprisingly slow

Original query:

SELECT V.Date, V.Amount, I.Number
FROM Values V 
JOIN Items I ON V.ItemId = I.Id AND I.AssetId = V.AssetId
WHERE I.Type IN (10023, 10025) AND V.AssetId = 100
ORDER BY V.Date

Times out after some long time. After poking about a bit, I commented out the ORDER BY:

SELECT V.Date, V.Amount, I.Number
FROM Values V 
JOIN Items I ON V.ItemId = I.Id AND I.AssetId = V.AssetId
WHERE I.Type IN (10023, 10025) AND V.AssetId = 100
--ORDER BY V.Date

This returns two rows in zero millis.

I was under the impression that order by against a JOIN would occur after the query was complete, that is, it would make a temp (name?) table for the results and then order them. Apparently this impression is wrong.

Any suggestions? I don't have SHOWPLAN (et all) on this server, so I'm a bit in the dark.

Upvotes: 0

Views: 109

Answers (2)

under
under

Reputation: 3067

It's hard to troubleshoot without an execution plan.

First thing I would do is to make sure statistics are up to date. If stats are not up to date, SQL can produce inefficient plans.

If you cannot do this, you can change your query to force the correct plan.

For example, you can use a table variable to ensure ORDER BY is done last.

--declare staging table
declare @stage([Date] date, [Amount] decimal(19,4), [Number] int);

--insert data into staging table
INSERT INTO @stage([Date], [Amount], [Number])
SELECT V.Date, V.Amount, I.Number
FROM Values V 
JOIN Items I ON V.ItemId=I.Id AND I.AssetId=V.AssetId
WHERE I.Type IN (10023, 10025) AND V.AssetId=100) as t1;

--retrieve data from staging table with sorting
SELECT * FROM @stage ORDER BY Date;

This is not ideal, but if you don't have DBA permissions it's the best you can do.

Another thing to try is to use the MAXDOP 1 hint. This tells SQL engine not to use parallel execution which sometimes helps avoid inefficient plans.

SELECT V.Date, V.Amount, I.Number
FROM Values V 
JOIN Items I ON V.ItemId=I.Id AND I.AssetId=V.AssetId
WHERE I.Type IN (10023, 10025) AND V.AssetId=100) as t1
ORDER BY Date
OPTION (MAXDOP 1);

Note that I just added OPTION (MAXDOP 1) to your original query.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

ORDER BY can affect the execution plan. If the query does indeed only return two rows, then the timeout is surprising.

I would rewrite the query as:

SELECT V.Date, V.Amount, I.Number
FROM Values V  JOIN
     Items I
     ON V.ItemId = I.Id AND I.AssetId = V.AssetId
WHERE I.Type IN (10023, 10025) AND I.AssetId = 100
-----------------------------------^ the only change
ORDER BY V.Date;

Then the best indexes are Items(AssetId, Type, Id, Number) and Values(ItemId, Assetid, Date, Amount). These are covering indexes for the query.

Upvotes: 1

Related Questions