Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Stored procedure with dynamic SQL takes longer to execute than same query executed directly in query analyzer

Answered Stored procedure with dynamic SQL takes longer to execute than same query executed directly in query analyzer

  • Thursday, January 10, 2013 11:36 AM
     
     
    Sign In to Vote
    0
     

    I have a stored procedure which builds a sql statement and executes it using 'sp_executesql'. It takes 1 min 53 sec to return around 8000 records. Let’s call it Main procedure.

    I wrote temporary stored procedure using the dynamic query generated in main procedure, which also take same time to return same records.

    When I directly execute the dynamic query generated in main procedure it took 2 seconds to retrieve same set of records.

    Does anyone know why am I getting huge difference in execution time while using stored procedure for same query.

    Thanks-Jyoti

     

All Replies

  • Thursday, January 10, 2013 12:01 PM
     
     
    Sign In to Vote
    0

    Very high level, it must be an issue with parameter sniffing. Can you try giving option recompile to the dynamic query.

    Also provide your query.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Thursday, January 10, 2013 12:24 PM
     
      Has Code
    Sign In to Vote
    0
     
        
ALTER procedure [dbo].[LSAM_Get_Pending_Productionjobs_temp]   
( 
	@ProcessId bigINT,    
	@userId bigINT,
	@superviorsID varchar(20),
	@roleID tinyint    
)    
as 
begin    
--check if requesting user is team cordinator. If yes the get details of TL he is reporting to, to pull reports
IF(@roleID = 5)
BEGIN
	SELECT @superviorsID = SupervisorID FROM TBL_User_Role_Process_Map WHERE UserId = @userId and Deleted = 0
	SELECT @userId = UserId FROM TBL_Master_User WHERE CognizantID = @superviorsID and Deleted = 0
END
--check target configuration
DECLARE @TATType tinyint
DECLARE @TATField bigint
DECLARE @TATFieldName varchar(100)
SELECT @TATType = TATType, @TATField = TATFieldID
FROM TBL_WorkflowConfiguration WHERE ProcessID = @ProcessID
SELECT @TATFieldName = FieldName FROM TBL_DynamicFieldMaster 
WHERE FieldID = @TATField 
IF(@TATType IS NOT NULL)
BEGIN    
	--do changes from here    
	Declare @JobUploadTable tinyint    
	Select @JobUploadTable = 1--Job upload sheet    
	    
	Declare @JobAllocationTable tinyint    
	Select @JobAllocationTable = 2--Job allocation sheet      
	    
	/*********get table name for job upload data************/    
	Declare @TableName varchar(200)    
	Select @TableName = TableName from dbo.TBL_MASTER_DYNAMIC_TABLE    
	where processID = @processID AND SheetType = @JobUploadTable    
	    
	--Select @TableName--table name 'JobUpload_12'    
	/*********************End****************************/    
	    
	/*********get table name for job allocation data************/    
	Declare @JobAllocationTableName varchar(200)    
	Select @JobAllocationTableName = TableName from dbo.TBL_MASTER_DYNAMIC_TABLE    
	where processID = @processID AND SheetType = @JobAllocationTable    
	    
	--Select @JobAllocationTableName--table name 'JobUpload_12'    
	/*********************End****************************/     
	    
	declare @col_val_upload nvarchar(4000)    
	set @col_val_upload = ''    
	Select @col_val_upload = @col_val_upload + ',[' + FieldName + ']' from dbo.TBL_DynamicFieldMaster where processid = @ProcessId and sheettype = 1 and    
	deletedstatus = 0    
	    
	Select @col_val_upload = substring(@col_val_upload,2,len(@col_val_upload))    
	    
	--Select @col_val_upload    
	    
	if(@col_val_upload <> '')--if column available then only prefix comma    
	 select @col_val_upload = ', ' + @col_val_upload    
	     
	--define expected closure time field
	DECLARE @ExpectedClosureTimeFieldName varchar(100) = 'ExpectedClosureDate'
	IF(@TATType = 1)
	BEGIN
		SELECT @ExpectedClosureTimeFieldName = @TATFieldName
	END
	    
	DECLARE @Task VARCHAR(100)    
	Declare @Query nvarchar(4000)    
	Declare @params nvarchar(4000)    
	    
	    
	SELECT @Query = null   
	Select @Query = 'select jobs.JobID,TaskID,splits.task,splits.Priority,Comments,[Current Allocation To],CONVERT(DATETIME, CONVERT(VARCHAR(11),AllocatedOn)) as AllocatedOn,[' + @ExpectedClosureTimeFieldName + '] AS TargetCompletionDate,dbo.ufn_GetHorsMinSecFormat(CASE WHEN (DATEDIFF(s,GETDATE(),ExpectedClosureDate)) < 0 THEN 0 ELSE (DATEDIFF(s,GETDATE(),ExpectedClosureDate)) END) as timeRemain,isnull(statusmaster.JobStatus,''Assignment Pending OR in QC stage'') as JobStatus,TemplateName, SheetName,ExcelFileName' + @col_val_upload + ' from ( select *  from UsrLSAutoMetrics.' + quotename(@TableName) + '  where deleted = 0 AND JOBID NOT IN (select JobID from TBL_JobFlowTrack where ProcessID = @ProcessId group by JobID having MAX(ToStatus) in (8,23)))jobs  left outer join (select ScheduleID,JobID,TaskID,Comments,allocatedto,AllocatedOn,[Status] from UsrLSAutoMetrics.' + quotename(@JobAllocationTableName) + ' where status in (2,3,5,6) and deleted = 0)jobsAlloc on jobs.JobID = jobsAlloc.JobID left outer join ( SELECT splitid,task,Priority FROM TBL_Master_JobSplit WHERE ProcessID = @ProcessId )splits on jobsAlloc.taskid = splits.splitid left outer join (SELECT TemplateID,TemplateName FROM TBL_Master_Template WHERE DeletedStatus = 0 AND ProcessID = @ProcessId) template ON jobs.TemplateID = template.TemplateID left outer join (select userid, FirstName + '' '' + LastName + ''['' + Cognizantid + '']'' as [Current Allocation To] from tbl_master_user where userid in (select distinct userid from dbo.TBL_User_Role_Process_Map where processid= @ProcessId and deleted = 0))users on jobsAlloc.allocatedto = users.userid LEFT OUTER join ( SELECT JobStatus,JobStatusID FROM TBL_Master_JobStatus )statusmaster on jobsAlloc.[Status] = statusmaster.JobStatusID where AllocatedTo is null or AllocatedTo = @userId or AllocatedTo in (select UserId from TBL_User_Role_Process_Map where Deleted = 0 and SupervisorID = @superviorsID and ProcessId = @ProcessId) order by (DATEDIFF(s,GETDATE(),ExpectedClosureDate))'   
	    
	SELECT @params = N'@ProcessId bigint, ' + N'@userId bigint, ' + N'@superviorsID varchar(20)'
	    
	--Select @Query    
	    
	EXEC sp_executesql @Query, @params,@ProcessId,@userId,@superviorsID    
END 
   
end

    Above is the mail query.

    Below is the SQL created dynamically in this procedure:

    declare @ProcessId bigint = 49
declare @userId bigint = 187
declare @superviorsID varchar(20) = '144196'
select jobs.JobID,TaskID,splits.task,splits.Priority,Comments,[Current Allocation To],CONVERT(DATETIME, CONVERT(VARCHAR(11),AllocatedOn)) as AllocatedOn,[TGT Date - Client submission Date] AS TargetCompletionDate,dbo.ufn_GetHorsMinSecFormat(CASE WHEN (DATEDIFF(s,GETDATE(),ExpectedClosureDate)) < 0 THEN 0 ELSE (DATEDIFF(s,GETDATE(),ExpectedClosureDate)) END) as timeRemain,isnull(statusmaster.JobStatus,'Assignment Pending OR in QC stage') as JobStatus,TemplateName, SheetName,ExcelFileName, [Study ID],[Date of Review],[Patient ID],[eCRF Folder Name],[eCRF Name],[Plan End Date - Internal submission Date],[TGT Date - Client submission Date],[EID] from ( select *  from UsrLSAutoMetrics.[JobUpload_49]  where deleted = 0 AND JOBID NOT IN (select JobID from TBL_JobFlowTrack where ProcessID = @ProcessId group by JobID having MAX(ToStatus) in (8,23)))jobs  left outer join (select ScheduleID,JobID,TaskID,Comments,allocatedto,AllocatedOn,[Status] from UsrLSAutoMetrics.[JobAllocation_49] where status in (2,3,5,6) and deleted = 0)jobsAlloc on jobs.JobID = jobsAlloc.JobID left outer join ( SELECT splitid,task,Priority FROM TBL_Master_JobSplit WHERE ProcessID = @ProcessId )splits on jobsAlloc.taskid = splits.splitid left outer join (SELECT TemplateID,TemplateName FROM TBL_Master_Template WHERE DeletedStatus = 0 AND ProcessID = @ProcessId) template ON jobs.TemplateID = template.TemplateID left outer join (select userid, FirstName + ' ' + LastName + '[' + Cognizantid + ']' as [Current Allocation To] from tbl_master_user where userid in (select distinct userid from dbo.TBL_User_Role_Process_Map where processid= @ProcessId and deleted = 0))users on jobsAlloc.allocatedto = users.userid LEFT OUTER join ( SELECT JobStatus,JobStatusID FROM TBL_Master_JobStatus )statusmaster on jobsAlloc.[Status] = statusmaster.JobStatusID where AllocatedTo is null or AllocatedTo = @userId or AllocatedTo in (select UserId from TBL_User_Role_Process_Map where Deleted = 0 and SupervisorID = @superviorsID and ProcessId = @ProcessId) order by (DATEDIFF(s,GETDATE(),ExpectedClosureDate))

    Thanks-Jyoti

     
  • Thursday, January 10, 2013 12:43 PM
     
     
    Sign In to Vote
    0

    Hi,

    Ok so the problem is not with the dynamic sql, it is with in the temp stored procedure which you have created to execute the dynamic sql. You can do following,

    1) Comment the exe the line "EXEC sp_executesql @Query, @params,@ProcessId,@userId,@superviorsID "  dynamic sql from the main procedure LSAM_Get_Pending_Productionjobs_temp

    also keep the Select @Query    commented

    2) Now try to execute the main query and see how much time it is taking (too see the actual query performance you can use this link (http://insqlserver.com/content/how-check-actual-performance-sql-query)

    3) If your main query is taking more time, then comment some select statement in the main query, as I can see there are many select statement you have used, so just try commenting one by one and try executing the query, this way you will figure out which select statement is taking the hit.

    You need to fine tune your implementation accordingly

    Mark this post as answer if this resolves your issue.

    Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin

     
  • Thursday, January 10, 2013 10:59 PM
     
     Answered
    Sign In to Vote
    0

    Permit me to ask one thing: are you able to read that code? I am not. Therefore it is difficult to address your problems.

    One important detail is of course, if you ran your two alternatives against the same set of tables.

    Even with the same set of tables, you may still get different plans. With dynamic SQL the values are parameters, and SQL Server can sniff these values. With the other query, you have variable, and the optimizer has no informtion at all about their values and make blind assumptions.

    Usually, more information gives better plans, but sometimes it backfires. And of course, there may already be a plan for the parameterised query in the cache, but optimized for a different set of parameters.

    I have a longer article on my web site that may give you some ideas, or at least understanding of how plans and caches work:
    http://www.sommarskog.se/query-plan-mysteries.html

    I were to suggest something out of the blue, I would test what happens if you take out the ORDER BY clause.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, January 11, 2013 4:54 AM
     
      Has Code
    Sign In to Vote
    0

    Try the below to replace your dynamic statement: I added OPTION(RECOMPILE) to the dynamic SQL.

    Select @Query = 'select jobs.JobID,TaskID,splits.task,splits.Priority,Comments,[Current Allocation To],CONVERT(DATETIME, CONVERT(VARCHAR(11),AllocatedOn)) as AllocatedOn,[' + @ExpectedClosureTimeFieldName + '] AS TargetCompletionDate,dbo.ufn_GetHorsMinSecFormat(CASE WHEN (DATEDIFF(s,GETDATE(),ExpectedClosureDate)) < 0 THEN 0 ELSE (DATEDIFF(s,GETDATE(),ExpectedClosureDate)) END) as timeRemain,isnull(statusmaster.JobStatus,''Assignment Pending OR in QC stage'') as JobStatus,TemplateName, SheetName,ExcelFileName' + @col_val_upload + ' from ( select *  from UsrLSAutoMetrics.' + quotename(@TableName) + '  where deleted = 0 AND JOBID NOT IN (select JobID from TBL_JobFlowTrack where ProcessID = @ProcessId group by JobID having MAX(ToStatus) in (8,23)))jobs  left outer join (select ScheduleID,JobID,TaskID,Comments,allocatedto,AllocatedOn,[Status] from UsrLSAutoMetrics.' + quotename(@JobAllocationTableName) + ' where status in (2,3,5,6) and deleted = 0)jobsAlloc on jobs.JobID = jobsAlloc.JobID left outer join ( SELECT splitid,task,Priority FROM TBL_Master_JobSplit WHERE ProcessID = @ProcessId )splits on jobsAlloc.taskid = splits.splitid left outer join (SELECT TemplateID,TemplateName FROM TBL_Master_Template WHERE DeletedStatus = 0 AND ProcessID = @ProcessId) template ON jobs.TemplateID = template.TemplateID left outer join (select userid, FirstName + '' '' + LastName + ''['' + Cognizantid + '']'' as [Current Allocation To] from tbl_master_user where userid in (select distinct userid from dbo.TBL_User_Role_Process_Map where processid= @ProcessId and deleted = 0))users on jobsAlloc.allocatedto = users.userid LEFT OUTER join ( SELECT JobStatus,JobStatusID FROM TBL_Master_JobStatus )statusmaster on jobsAlloc.[Status] = statusmaster.JobStatusID where AllocatedTo is null or AllocatedTo = @userId or AllocatedTo in (select UserId from TBL_User_Role_Process_Map where Deleted = 0 and SupervisorID = @superviorsID and ProcessId = @ProcessId) order by (DATEDIFF(s,GETDATE(),ExpectedClosureDate)) OPTION(RECOMPILE)'

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.