CODEWITHSUNDEEP
sqlsql-server-2008
kosnkov
kosnkov

Reputation: 5941

What is the difference in sql performance between asc and desc

I have two very simmilar queries:

exec sp_executesql N'SELECT TOP (1) [t0].[Production]
FROM [dbo].[T_Production] AS [t0]
WHERE [t0].[InputID] = @p0
ORDER BY [t0].[Timestamp] DESC',N'@p0 int',@p0=1161

exec sp_executesql N'SELECT TOP (1) [t0].[Production]
FROM [dbo].[T_Production] AS [t0]
WHERE [t0].[InputID] = @p0
ORDER BY [t0].[Timestamp]',N'@p0 int',@p0=1161

first one executes in 1 second , the other one in 31 seconds, why?

The funny thing is that if I change the second query from store procedure into

SELECT TOP (1) [t0].[Production]
FROM [dbo].[T_Production] AS [t0]
WHERE [t0].[InputID] = 1161
ORDER BY [t0].[Timestamp]

it also exectues in 1 second

but the amazing is that if add white space after [Timestamp] so that the last line looks like this ORDER BY [t0].[Timestamp] ',N'@p0 int',@p0=1161 it also excecutes very fast.

EDIT: After some investigation i check the actual execution plan and cos was: select cost:0 -> top cost 6 -> index scan (NonClustered)[T_Production].[_dta_index_T_Production] cost 94

so I added new index on [Timestamp] with descending ordering. It took few minutes and sudenly now the query executes as fast as the first one.

But here i am really confused, i noticed now that the order of additional index should be ascending, casuse i allready have with descding, but creating another one helped? it confused me, so i delted this index which i just created, and now this query still executes as fast as first one. Maybe rebuilding of indexes helped? and this problem will return.

But now after adding and removing the index, the actual execution plan is different: select :cost 0 -> top cost: 0 -> nested loops (inner join) cost 0 -> Index seek (NonClustered)... cost 33% and Key Lookup (Clustered).. cost: 67%

Upvotes: 0

Views: 788

Answers (1)

Cade Roux
Cade Roux

Reputation: 89701

Indexes also have ASC and DESC on the columns in the keys which can have an effect on execution plans.

This seems like a big difference for such a simple query, but have a look at the index definition and the execution plans, one probably must have a costly additional sort operation.

Removal of the indexes almost certainly invalidated the execution plans in the cache.

To avoid parameter sniffing, you can use OPTION (RECOMPILE) with your inline parameterized query or move the code into a stored procedure and OPTIMIZE FOR UNKNOWN

Upvotes: 3

Related Questions