sp runs longer then vs straight running

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp runs longer then vs straight running

Author

Topic

lleemon
Starting Member

24 Posts

Posted - 2009-01-16 : 16:01:50

I have a stored procedure that if in the query window I run it takes over 2 minutes to run.

If I copy all the sql and put in query window, set variables, highlight and execute it takes 1 maybe 2 seconds.

Does anyone have any ideas on why when I hightlight and run it only takes a few seconds vs minutes with the sp?

Here is the stored procedure way:
EXEC sp_Report_Test NULL,NULL,'1/16/2009','1/17/2009',NULL


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Report_Test]

@Client_ID int=NULL,
@JobPosting_ID int=NULL,
@StartDate datetime=NULL,
@EndDate datetime=NULL,
@Personnel_ID int=NULL

AS
BEGIN
SET NOCOUNT ON;

SELECT DISTINCT 
  cjp.Candidate_ID, 
  c.Candidate_LastName, 
  c.Candidate_FirstName, 
  c.Candidate_Email, 
  c.Candidate_PhonePrimary, 
  c.Candidate_City, 
  c.Candidate_StateProvince, 
  c.Candidate_ZipPostalCode, 
  cjp.CandidateJobPosting_DateCreated, 
  (p.Personnel_LastName + ', ' + p.Personnel_FirstName) AS Personnel_Name, 
  jp.JobPosting_ID, 
  jp.JobPosting_Name, 
  ec.EmploymentCategory_Name, 
  ess.EmploymentSearchSource_Name,
  Len(vcr.CandidateResume_Resume) as ResumeChars,
	vcr.CandidateResume_ID

FROM 
  CandidateJobPosting cjp inner join Candidate c on cjp.Candidate_ID = c.Candidate_ID
                          inner join JobPosting jp on cjp.JobPosting_ID = jp.JobPosting_ID 
                          inner join Personnel p on cjp.Personnel_ID = p.Personnel_ID 
                          inner join EmploymentCategory ec on cjp.EmploymentCategory_ID = ec.EmploymentCategory_ID 
                          inner join EmploymentSearchSource ess on cjp.EmploymentSearchSource_ID = ess.EmploymentSearchSource_ID
						  LEFT OUTER JOIN vwCandidateResume vcr on cjp.Candidate_ID = vcr.Candidate_ID
WHERE 
  (@Client_ID is null or jp.Client_ID=@Client_ID)
  AND (@JobPosting_ID is null or cjp.JobPosting_ID=@JobPosting_ID)
  AND (@StartDate is null or cjp.CandidateJobPosting_DateCreated >= @StartDate)
  AND (@EndDate is null or cjp.CandidateJobPosting_DateCreated <= @EndDate)
  AND (@Personnel_ID is null or cjp.Personnel_ID=@Personnel_ID)
ORDER BY 
  c.Candidate_LastName, 
  c.Candidate_FirstName,
  cjp.Candidate_ID, 
  cjp.CandidateJobPosting_DateCreated DESC

END

Here is the code I run manually:


DECLARE @Client_ID int
DECLARE @JobPosting_ID int
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @Personnel_ID int

SET @Client_ID = NULL
SET @JobPosting_ID = NULL
SET @StartDate = '1/16/2009'
SET @EndDate = '1/17/2009'
SET @Personnel_ID = NULL

SELECT DISTINCT 
  cjp.Candidate_ID, 
  c.Candidate_LastName, 
  c.Candidate_FirstName, 
  c.Candidate_Email, 
  c.Candidate_PhonePrimary, 
  c.Candidate_City, 
  c.Candidate_StateProvince, 
  c.Candidate_ZipPostalCode, 
  cjp.CandidateJobPosting_DateCreated, 
  (p.Personnel_LastName + ', ' + p.Personnel_FirstName) AS Personnel_Name, 
  jp.JobPosting_ID, 
  jp.JobPosting_Name, 
  ec.EmploymentCategory_Name, 
  ess.EmploymentSearchSource_Name,
  Len(vcr.CandidateResume_Resume) as ResumeChars,
	vcr.CandidateResume_ID

FROM 
  CandidateJobPosting cjp inner join Candidate c on cjp.Candidate_ID = c.Candidate_ID
                          inner join JobPosting jp on cjp.JobPosting_ID = jp.JobPosting_ID 
                          inner join Personnel p on cjp.Personnel_ID = p.Personnel_ID 
                          inner join EmploymentCategory ec on cjp.EmploymentCategory_ID = ec.EmploymentCategory_ID 
                          inner join EmploymentSearchSource ess on cjp.EmploymentSearchSource_ID = ess.EmploymentSearchSource_ID
						  LEFT OUTER JOIN vwCandidateResume vcr on cjp.Candidate_ID = vcr.Candidate_ID
WHERE 
  (@Client_ID is null or jp.Client_ID=@Client_ID)
  AND (@JobPosting_ID is null or cjp.JobPosting_ID=@JobPosting_ID)
  AND (@StartDate is null or cjp.CandidateJobPosting_DateCreated >= @StartDate)
  AND (@EndDate is null or cjp.CandidateJobPosting_DateCreated <= @EndDate)
  AND (@Personnel_ID is null or cjp.Personnel_ID=@Personnel_ID)
ORDER BY 
  c.Candidate_LastName, 
  c.Candidate_FirstName,
  cjp.Candidate_ID, 
  cjp.CandidateJobPosting_DateCreated DESC

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-16 : 16:17:14

The optimizer is no doubt deciding on different execution plans for these two scenarios. You can confirm by looking at the actual plan both ways (set showplan_text on)

Sometimes in an adhoc query the optimizer can take advantage of knowing the parameter values set in a previous statement to devise the ultimate plan for the subsequent statement.

One thing you may try for all these params is replacing this style:
(@Client_ID is null or jp.Client_ID=@Client_ID)

with this method:
jp.Client_ID = Coalesce(@Client_ID, jp.Client_ID)

That probably won't have any impact on your problem because the challenge with many optional parameters in a SP is that different calls may require different plans but sql server likes to cache the plan(s) for SPs to re-use.

Be One with the Optimizer
TG

lleemon
Starting Member

24 Posts

Posted - 2009-01-16 : 16:57:19

More information. If I remove the 'LEFT OUTER JOIN vwCandidateResume' from the stored procedure it runs much quicker.

What vwCandidateResume is:


SELECT     crd.Candidate_ID, cr.CandidateResume_Resume, cr.CandidateResume_ID
FROM         dbo.CandidateResumeDetail AS crd INNER JOIN
                      dbo.CandidateResume AS cr ON crd.CandidateResumeDetail_ID = cr.CandidateResumeDetail_ID

From those tables here are my primary keys:

Table: CandidateResume
PK = CandidateResume_ID

Table CandidateResumeDetail
PK = CandidateResumeDetail_ID

If I changed the CandidateResume PK to CandidateResumeDetail_ID would that speed it out?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 23:33:16

seems like this is due to parameter sniffing
http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources