September 16, 2009 at 2:43 am
I've a problem with a query when i run it with parameters in MSSQL Server 2005. If I substitute parameters with the equivalent value, the query is more fast. This happen even if I execute each query more times.
What is strange is that the same slow query became fast if I run it using the exec command (with parameters).
Can anyone help me, please?
I attach an example script where i describe:
- My server configurazion;
- MSSQL Server Profiler result time
- My queries
- Table schema
I can provide also a sample database with data, if needed.
Thank you
September 16, 2009 at 4:37 am
September 16, 2009 at 6:57 am
What you're describing sure sounds like a classic case of parameter sniffing. Do a search for that term and you'll find a lot of options for fixing it. Sometimes, the easiest option is to just update the statistics on the tables or indexes in question, but usually you'll need to work through the options outlined in the article above. You can do a search for that term and see lots of methods for dealing with it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 16, 2009 at 8:24 am
Thanks for your answer.
I have read something about parameter sniffing but I think my problem is a bit different.
I execute twice the same query with the same parameters: the first query use constants
=> WHERE T.IdDitta = 'PROVA' AND R.ContoTp = 'C' AND R.IdConto = 'ABCIDE' AND T.Eserc = T.Eserc AND IsNull(S.Scad_Partita,'') = IsNull(S.Scad_Partita,''),
while the second use parameters with declare and set in the same script
=> WHERE T.IdDitta = @IdDitta AND R.ContoTp = @ContoTp AND R.IdConto = (CASE WHEN @IdConto = '' THEN R.IdConto ELSE @IdConto END) AND T.Eserc = (CASE WHEN @Eserc = 0 THEN T.Eserc ELSE @Eserc END) AND IsNull(S.Scad_Partita,'') = (CASE WHEN @Scad_Partita = '' THEN IsNull(S.Scad_Partita,'') ELSE @Scad_Partita END)
The values of parameters are the same (and also the query and the results), so I don't understand why SQL Server change the execution plan with a duration increase of 300%.
September 16, 2009 at 8:39 am
SQL Server doesn't know the best plan to use because the values are dynamic and therefore can't take into account value distribution etc
September 16, 2009 at 8:52 am
Baratella Marco (9/16/2009)
Thanks for your answer.I have read something about parameter sniffing but I think my problem is a bit different.
I execute twice the same query with the same parameters: the first query use constants
=> WHERE T.IdDitta = 'PROVA' AND R.ContoTp = 'C' AND R.IdConto = 'ABCIDE' AND T.Eserc = T.Eserc AND IsNull(S.Scad_Partita,'') = IsNull(S.Scad_Partita,''),
while the second use parameters with declare and set in the same script
=> WHERE T.IdDitta = @IdDitta AND R.ContoTp = @ContoTp AND R.IdConto = (CASE WHEN @IdConto = '' THEN R.IdConto ELSE @IdConto END) AND T.Eserc = (CASE WHEN @Eserc = 0 THEN T.Eserc ELSE @Eserc END) AND IsNull(S.Scad_Partita,'') = (CASE WHEN @Scad_Partita = '' THEN IsNull(S.Scad_Partita,'') ELSE @Scad_Partita END)
The values of parameters are the same (and also the query and the results), so I don't understand why SQL Server change the execution plan with a duration increase of 300%.
Whoa! I guess I should have read the code before I responded. You're comparing apples to cannon balls here. You don't have a situation of a query with constants and a query with parameters. You're adding case statements to the WHERE clause in the second set of code. That's a game changer. SQL Server won't be able to make use of indexes because it can't know what it will need to feed into the query. I'll bet you're getting statement recompiles when this runs too. I'm not surprised they're radically different in performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 17, 2009 at 9:29 am
>> "apples to cannonballs"
I like that one! 🙂 Curious why the OP would even imply these are equivalent...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply