June 11, 2013 at 8:33 am
Well I was stumped for a title to this, as I am stumped as to the cause !
With the code below, the issue is in the where statement "LB.DB = @DB".
As written, this variable is equal to the string 'NA', and the execution time is horrendous.
However, if I hard code "LB.DB = 'NA'" , then the code executes in about 2 minutes, which is as expected.
For the life of me, I cannot figure out WHY the execution is affected by the use of a variable to carry in the required 'where' value.
If it helps, the field [DB] is a char(2), originating 3 levels of nesting down in a set of views.
Any insights as to what is happening here would be appreciated !!
Many Thanks
Simon
-------------------------------------------------------------------
Declare
@DB as char(2)
select @DB = 'NA'
SELECT
LB.DB, LB.CaseKey AS [Case Key], LB.Month AS [Month]
, LB.RollUpClass AS [Product], LB.SourceOfProduct as [Plant]
, LB.DeliveredQty AS [Delivered Qty]
into
#LB
FROM
[CJ_LBSVTP].[3_LB_ProductSourcePlant] AS LB
WHERE
(LB.CaseKey = 8751)
AND
(LB.Month = CONVERT(datetime,'2012-10-01'))
And
(LB.DB = @DB)
June 11, 2013 at 8:38 am
This sounds like parameter sniffing to me.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
Make sure you read through all 3 segments of this article.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2013 at 9:03 am
It could be parameter sniffing but SQL server can do some peculiar things when you have number of nested views, such that it doesnt use the optimal plan at the lowest level where the filtering is done using a variable.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
June 11, 2013 at 11:15 am
It's actually the lack of parameter sniffing that's the problem here.
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2013 at 1:33 pm
Brilliant guys, thank you.
Solution was to pass the variable to a 2nd procedure where it is accepeted as a parameter.
As expected, I no longer get the short execution time as previous, but that's simply because my hard coding was giving 'unrealistic' results. (false positive).
I can deal with that, and now direct my efforts to optimising the underlying queries.
Main thing is, the behaviour is explained and understood.
Once again, thanks for being there !!
June 11, 2013 at 1:46 pm
Talking about optimizing the query itself:
You might want to replace the "nested view approach" with the "early filter concept":
Instead of creating a view querying tables that might not even be required for the current task and apply the filter at the end you could use a few cte's with the WHERE condition already applied inside the cte.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply