0

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%

kosnkov
  • 5,609
  • 13
  • 66
  • 107
  • what's the native sort order for the underlying table? if it's asc, then the desc query would require building the entire result set first before it can be sorted desc. – Marc B Feb 19 '13 at 17:48
  • `explain plan` or `execution plan` :you can check there.. – aspiring Feb 19 '13 at 17:49
  • @MarcB What is the "native sort order for the underlying table"? Do you mean the key to the clustered index? – Cade Roux Feb 19 '13 at 17:52
  • Your first two examples are not stored procedures, they are simply parametrized queries. They could still have a cached execution plan, but they are not stored procedures. – Cade Roux Feb 19 '13 at 17:58
  • ok so the second parametrized query excecutes very long while simple select statement really fast. – kosnkov Feb 19 '13 at 18:01

1 Answers1

3

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

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • look on my question once again, if i change store procedure into normal select it exectues very fast, i checkED this query with tuning engine and din't find any possible way to upgrade. But this is really true, normal select very fast, store procedure 31 seconds. – kosnkov Feb 19 '13 at 17:53
  • @kosnkov This sounds like it might be parameter sniffing, then (an execution plan stored which is not optimal for later execution). Either mask the parameter with a variable or use OPTIMIZE FOR UNKNOWN http://www.sqlservercentral.com/blogs/practicalsqldba/2012/06/27/how-local-variable-or-optimize-for-unknown-resolve-parameter-sniffing/ – Cade Roux Feb 19 '13 at 17:56
  • @kosnkov Another question about parameter sniffing particular in relation to an inline parameterized query using OPTION (RECOMPILE): http://stackoverflow.com/questions/10933366/sp-executesql-is-slow-with-parameters – Cade Roux Feb 19 '13 at 18:04
  • I will tell you even more, if I add while space after [Timestamp] that the last line looks like this: ORDER BY [t0].[Timestamp] ',N'@p0 int',@p0=1161 then it also works only one second, this is the matter of white space, amazing. – kosnkov Feb 19 '13 at 18:11
  • @kosnkov any changes to the query would result in a different plan being generated. Has your data profile changed significantly so that the execution plan would be suboptimal? Have you reviewed the actual execution plan? – Cade Roux Feb 19 '13 at 18:24
  • If i right click on this parametric query and press dispaly estimated execution plan i have only : Execute proc cost 0% – kosnkov Feb 19 '13 at 18:27
  • @kosnkov Try the ACTUAL execution plan. – Cade Roux Feb 19 '13 at 18:33
  • select cost:0 -> top cost 6 -> index scan (NonClustered)[T_Production].[_dta_index_T_Production] cost 94 this index created for me tuning advisor, any advise what can I do? – kosnkov Feb 19 '13 at 18:36
  • @kosnkov Can you post the table and index DDL and the execution plan with your question? – Cade Roux Feb 19 '13 at 18:54
  • I did so, finally the problem disapered after adding and removing index and the execution plan has also changed. – kosnkov Feb 19 '13 at 19:05