Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

stored procedure not running to completion and not returning results set Expand / Collapse
Author
Message
Posted Tuesday, May 27, 2014 11:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:16 AM
Points: 461, Visits: 693
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.






Post #1574897
Posted Tuesday, May 27, 2014 12:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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...


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1574916
Posted Tuesday, May 27, 2014 12:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1574923
Posted Wednesday, May 28, 2014 3:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:16 AM
Points: 461, Visits: 693
@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/

http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices-150-Parameter-Sniffing.htm

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
Post #1575105
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse