May 27, 2014 at 11:27 am
Hi guys,
This one has me a bit stumped... I have NEVER seen this before...
I have stored procedure
ALTER PROCEDURE dbo.usp_Create_Fact_Job (@startDate date, @endDate date) AS
/*--Debug--*/
--DECLARE @startDate date
--DECLARE @endDate date
--SET @startDate = '01 APR 2014'
--SET @endDate = '02 APR 2014'
;
/*-- end of Debug*/
WITH CTE_one AS ( blah blah blah)
...
SELECT a whole bunch of fields from the joined tables and CTEs
When I run the code inside the stored procedure by Declaring and setting the start and enddates manually the code runs in 4 minutes (missing some indexes 😉 )
When I call the stored procedure with the ExEC
DECLARE@return_value int
EXEC@return_value = [ClaimCenter].[usp_Create_Fact_Job]
@startDate = '01 apr 2014',
@endDate = '01 apr 2014'
SELECT'Return Value' = @return_value
It never returns a results set but doesn't error out either. I have left it for 40 minutes and still no joy.
Any idea what is going on as I am stumped as to why it never returns a result
The sproc is reasonably complicated; 6 CTEs to find the most recent version of records and some
2 joins to parent tables (parent and grandparent), 3 joins to child tables (child, grandchild and great grandchild) and 3 joins to lookup views each of which self references a table to filter for last version of a record.
May 27, 2014 at 12:32 pm
You may be suffering from 'parameter sniffing' - a quick search on Google will give you details of what that is and ways to get round it...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 27, 2014 at 12:37 pm
I agree with Phil. Here is an excellent series of articles about the topic.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
_______________________________________________________________
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/
May 28, 2014 at 3:43 am
@Phil, @sean
You were spot on guys. Parameter sniffing was the culprit. I have 'fixed' it by assigning the parameters to local variables. This will work for me as this is a once a day extract query and performance is not critical as it happens overnight when the server load is quite low and the extract criteria are consistent (range from 1 to 3 days) and the data distribution is pretty even.
I also found the following good posts on parameter sniffing.
http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/[/url]
http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx
NOTE: This is MY solution for MY problem, Do your own research! If you have high frequency queries or wildly different data distributions you may want a different solution
Aaron
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply