July 23, 2010 at 2:31 pm
I am trouble-shooting an sp with series of 3 udfs generate comma-delimited strings of guids needed to populate parameters in a final-nested sp.
Each part runs (virtually) instantly if run as queries in QA, but takes several minutes when run in sp.
Anything I need to look at to speed this up?
Thanks.
July 23, 2010 at 2:53 pm
Are you asking for help optimizing code you did not post?
July 23, 2010 at 3:01 pm
Given the limited info, it's likely because of an improper query plan, typically due to "parameter sniffing" issues.
Try EXEC the stored proc WITH RECOMPILE and see if it runs fast again.
EXEC your_stored_proc @... = ... WITH RECOMPILE
Scott Pletcher, SQL Server MVP 2008-2010
July 23, 2010 at 3:35 pm
It would definately help if you posted your code. Hard to help when we can't see what you see.
July 23, 2010 at 4:14 pm
Sorry about the omissions....
USE [XXXX]
GO
/****** Object: StoredProcedure [dbo].[SP_XXXXX] Script Date: 07/22/2010 09:39:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_XXXXX]
@groupIDs VARCHAR(MAX)
,@agentIDs VARCHAR(MAX)
,@evaluatorIDs VARCHAR(MAX)
,@eventIDs VARCHAR(MAX)
,@evaluationOf TINYINT
,@evaluationHaving TINYINT
,@timeFrom DATETIME
,@timeTo DATETIME,
@currentUserID uniqueidentifier = NULL,
@taskIDs varchar(max) = NULL,
@includeInactiveAgents bit = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @groupIDs is NULL OR @groupIDs = ''
BEGIN
DECLARE @groups NVARCHAR(MAX)
SELECT @groups = ISNULL(@groups + ',', '') + CAST([groupID] AS NVARCHAR(MAX))
FROM dbo.GetGroupsTableByTasksIdsAndType(@currentUserID, @taskIDs, @includeInactiveAgents)
SET @groupIDs = @groups
END
IF @agentIDs is NULL OR @agentIDs = ''
BEGIN
DECLARE @agents NVARCHAR(MAX)
SELECT @agents = ISNULL(@agents + ',', '') + CAST([userID] AS NVARCHAR(MAX))
FROM dbo.GetUsersTableByTasksIdsAndType(@currentUserID, @taskIDs, @includeInactiveAgents, @groupIDs)
SET @agentIDs = isnull(@agents, '')
END
IF @evaluatorIDs is NULL OR @evaluatorIDs = ''
BEGIN
DECLARE @evaluators NVARCHAR(MAX)
SELECT @evaluators = ISNULL(@evaluators + ',', '') + CAST([ID] AS NVARCHAR(MAX))
FROM dbo.GetEvaluatorsTableByCurrentUserIdAndTasksIdsAndAgentsIds(@currentUserID, @taskIDs, @agentIDs)
SET @evaluatorIDs = @evaluators
END
EXECUTE dbo.acSPEvaluations_XXXX
@groupIDs
,@agentIDs
,@evaluatorIDs
,@eventIDs
,@evaluationOf
,@evaluationHaving
,@timeFrom
,@timeTo
,@currentUserID
,@taskIDs
,@includeInactiveAgents
END
--------------------------------------------------------------------------
USE [XXXX]
GO
/****** Object: StoredProcedure [dbo].[acSPEvaluations_XXXX] Script Date: 07/23/2010 14:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ acSPEvaluations_XXXX]
@groupIDs VARCHAR(MAX)
,@agentIDs VARCHAR(MAX)
,@evaluatorIDs VARCHAR(MAX)
,@eventIDs VARCHAR(MAX)
,@evaluationOf TINYINT
,@evaluationHaving TINYINT
,@timeFrom DATETIME
,@timeTo DATETIME
,@currentUserID uniqueidentifier = NULL
,@taskIDs varchar(max) = NULL
,@includeInactiveAgents bit = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @dbg BIT
SET @dbg = 0
-- 0-without None event, 1-include None event, 2-only None event
DECLARE @useNoneEvents SMALLINT
IF PATINDEX('%00000000-0000-0000-0000-000000000000%',@eventIDs) = 0
SET @useNoneEvents = 0
ELSE
BEGIN
IF LEN(@eventIDs) > LEN('00000000-0000-0000-0000-000000000000')
SET @useNoneEvents = 1
ELSE
SET @useNoneEvents = 2
END
DECLARE @query NVARCHAR(MAX), @interactions NVARCHAR(MAX), @where NVARCHAR(MAX), @queryTableDecl NVARCHAR(MAX)
SET @queryTableDecl =
N'DECLARE @groupIDsTable TABLE
(
IDs uniqueidentifier
)
DECLARE @agentIDsTable TABLE
(
IDs uniqueidentifier
)
DECLARE @evaluatorIDsTable TABLE
(
IDs uniqueidentifier
)
DECLARE @eventIDsTable TABLE
(
IDs uniqueidentifier
)
IF @groupIDs is NULL OR @groupIDs = '''' BEGIN
insert into @groupIDsTable select * from dbo.GetGroupsTableByTasksIdsAndType(@currentUserID, @taskIDs, @includeInactiveAgents)
END
ELSE BEGIN
insert into @groupIDsTable SELECT CAST([Value] AS UNIQUEIDENTIFIER) FROM dbo.Split('','',@groupIDs)
END
IF @agentIDs is NULL OR @agentIDs = '''' BEGIN
insert into @agentIDsTable select * from dbo.GetUsersTableByTasksIdsAndType(@currentUserID, @taskIDs, @includeInactiveAgents, @groupIDs)
END
ELSE BEGIN
insert into @agentIDsTable SELECT CAST([Value] AS UNIQUEIDENTIFIER) FROM dbo.Split('','',@agentIDs)
END
IF @evaluatorIDs is NULL OR @evaluatorIDs = '''' BEGIN
insert into @evaluatorIDsTable select * from dbo.GetEvaluatorsTableByCurrentUserIdAndTasksIdsAndAgentsIds(@currentUserID, @taskIDs, @agentIDs)
END
ELSE BEGIN
insert into @evaluatorIDsTable SELECT CAST([Value] AS UNIQUEIDENTIFIER) FROM dbo.Split('','',@evaluatorIDs)
END
IF @eventIDs is NOT NULL OR @eventIDs <> '''' BEGIN
insert into @eventIDsTable SELECT CAST([Value] AS UNIQUEIDENTIFIER) FROM dbo.Split('','',@eventIDs)
END
'
SET @query =
N'SELECT DISTINCT
g.groupID AS [groupID]
,g.groupName AS [groupName]
,ua.userID AS [agentID]
,ua.fullName AS [agentName]
,eo.objectID AS [evaluationID]
,eo.startTime AS [evalDateInProgress]
,t.dateScheduled AS [evalDateScheduled]
,eo.dateInReview AS [evalDateInReview]
,eo.dateFinalized AS [evalDateFinalized]
,es.evaluatorID AS [evaluatorID]
,es.evaluatorName AS [evaluatorName]
,es.adjustedScore AS [adjustedScore]
,es.possibleScore AS [possibleScore]
FROM dbo.vpGroups g (NOLOCK)
JOIN dbo.vpGroupMembers gm (NOLOCK) ON g.groupID = gm.groupID
AND gm.groupID IN (<#groupIDs#>)
AND gm.userID IN (<#agentIDs#>)
JOIN dbo.vpUsers ua (NOLOCK) ON gm.userID = ua.userID
JOIN
( dbo.vpEvaluationObjects eo (NOLOCK)
LEFT JOIN dbo.vpToDoTasks t (NOLOCK) ON eo.objectID = t.taskObjectID ) ON gm.userID = eo.userID
AND gm.groupID = eo.groupID
<#evalStartDateCondition#>
JOIN dbo.iqEvalSessions es (NOLOCK) ON eo.objectID = es.evalID
AND es.latestRevision = 1
AND es.evaluatorID IN (<#evaluatorIDs#>)
AND ( (eo.startTime BETWEEN <#timeFrom#> AND <#timeTo#>) OR
(eo.dateInReview BETWEEN <#timeFrom#> AND <#timeTo#>) OR
(eo.dateFinalized BETWEEN <#timeFrom#> AND <#timeTo#>) OR
(t.dateScheduled BETWEEN <#timeFrom#> AND <#timeTo#>) OR
(es.dateCompleted BETWEEN <#timeFrom#> AND <#timeTo#>)
)
-- <#evalCompletedCondition#>
<#Interactions#>
<#Where#>'
SET @interactions =
N'<#Interactions_Join#>JOIN
(
dbo.vpCollectionObjects co
JOIN dbo.vpCollectionMemberChildCache cm ON co.objectID = cm.parentCollectionID
<#Events_Join#>JOIN dbo.vpApplicationObjectEvents aoe ON cm.objectID = aoe.objectID AND aoe.eventTypeID IN (<#eventIDs#>)
) ON eo.mediaObjectID = co.objectID <#interactionDateCondition#>'
SET @where = ''
IF @useNoneEvents = 0
SET @interactions = REPLACE(@interactions, '<#Events_Join#>', '')
ELSE IF @useNoneEvents = 1
SET @interactions = REPLACE(@interactions, '<#Events_Join#>', 'LEFT ')
ELSE IF @useNoneEvents = 2
BEGIN
SET @interactions = REPLACE(@interactions, '<#Events_Join#>', 'LEFT ')
SET @where = N'WHERE aoe.objectID IS NULL'
END
/* 0 - Interactions; 1 - General Performance; 2 - All (0 and 1) */
IF @evaluationOf = 0
SET @interactions = REPLACE(@interactions,'<#Interactions_Join#>','')
ELSE IF @evaluationOf = 1
SET @interactions = 'AND eo.mediaobjectID is null'
ELSE IF @evaluationOf = 2
SET @interactions = REPLACE(@interactions,'<#Interactions_Join#>','LEFT ')
DECLARE @from NVARCHAR(50), @to NVARCHAR(50)
SET @from = dbo.GetFormattedTime(@timeFrom)
SET @to = dbo.GetFormattedTime(@timeTo)
/*0-Eval complete date(iqEvalSessions.dateCompleted); 1-Eval start date(vpEvaluationObjects.dateStarted); 2-Interaction date(vpCollectionObjects.startTime)*/
IF @evaluationHaving = 0
BEGIN
SET @query = REPLACE(@query, '<#evalCompletedCondition#>', 'AND es.dateCompleted BETWEEN ''' + @from + ''' AND ''' + @to + '''')
SET @query = REPLACE(@query, '<#evalStartDateCondition#>', '')
SET @interactions = REPLACE(@interactions, '<#interactionDateCondition#>', '')
END
ELSE IF @evaluationHaving = 1
BEGIN
SET @query = REPLACE(@query, '<#evalCompletedCondition#>', '')
SET @query = REPLACE(@query, '<#evalStartDateCondition#>', 'AND eo.dateStarted BETWEEN ''' + @from + ''' AND ''' + @to + '''')
SET @interactions = REPLACE(@interactions, '<#interactionDateCondition#>', '')
END
ELSE IF @evaluationHaving = 2
BEGIN
SET @query = REPLACE(@query, '<#evalCompletedCondition#>', '')
SET @query = REPLACE(@query, '<#evalStartDateCondition#>', '')
SET @interactions = REPLACE(@interactions,'<#interactionDateCondition#>', 'AND co.startTime BETWEEN ''' + @from + ''' AND ''' + @to + '''')
END
SET @interactions = REPLACE(@interactions,'<#eventIDs#>','SELECT IDs FROM @eventIDsTable')
SET @query = REPLACE(@query,'<#timeFrom#>', '@timeFrom')
SET @query = REPLACE(@query,'<#timeTo#>', '@timeTo')
SET @query = REPLACE(@query,'<#Where#>',@where)
SET @query = REPLACE(@query,'<#groupIDs#>','SELECT IDs FROM @groupIDsTable')
SET @query = REPLACE(@query,'<#agentIDs#>','SELECT IDs FROM @agentIDsTable')
SET @query = REPLACE(@query,'<#evaluatorIDs#>','SELECT IDs FROM @evaluatorIDsTable')
SET @query = REPLACE(@query,'<#Interactions#>',@interactions)
IF @dbg = 1 PRINT @query
SET @query = @queryTableDecl + @query
DECLARE @srcTable TABLE
(
[groupID] UNIQUEIDENTIFIER
,[groupName] NVARCHAR(255)
,[agentID] UNIQUEIDENTIFIER
,[agentName] NVARCHAR(255)
,[evaluationID] UNIQUEIDENTIFIER
,[evalDateInProgress] DATETIME
,[evalDateScheduled] DATETIME
,[evalDateInReview] DATETIME
,[evalDateFinalized] DATETIME
,[evaluatorID] UNIQUEIDENTIFIER
,[evaluatorName] NVARCHAR(255)
,[adjustedScore] INT
,[possibleScore] INT
)
--time , eventId ,
INSERT INTO @srcTable
EXEC sp_executesql @query,
N'@groupIDs varchar(max), @agentIDs varchar(max), @evaluatorIDs varchar(max), @currentUserID uniqueidentifier = NULL,@taskIDs varchar(max) = NULL,@includeInactiveAgents bit = NULL, @eventIDs varchar(max), @timeFrom datetime, @timeTo dateTime',
@groupIDs, @agentIDs, @evaluatorIDs, @currentUserID, @taskIDs, @includeInactiveAgents, @eventIDs, @timeFrom, @TimeTo
SELECT
S.[groupID]
,S.[groupName]
,S.[agentID]
,S.[agentName]
,S.[evaluationID]
,(CASE WHEN D.DateID = S.InProgressDateID THEN 1 ELSE 0 END) AS IsInProgress
,(CASE WHEN D.DateID = S.ScheduledDateID THEN 1 ELSE 0 END) AS IsScheduled
,(CASE WHEN D.DateID = S.InReviewDateID THEN 1 ELSE 0 END) AS IsInReview
,(CASE WHEN D.DateID = S.FinalizedDateID THEN 1 ELSE 0 END) AS IsFinalized
,S.[evaluatorID]
,ISNULL(S.[evaluatorName],'<None>') AS [evaluatorName]
,0 AS [adjustedScoreInReview]
,100 AS [possibleScoreInReview]
,S.[adjustedScore] AS [adjustedScoreFinalized]
,S.[possibleScore] AS [possibleScoreFinalized]
,D.[DayOfYearNumber]
,D.[DayStart]
,D.[DayEnd]
,D.[DayStartFormatted]
,D.[DayEndFormatted]
,D.[WeekNumber]
,D.[WeekStart]
,D.[WeekEnd]
,D.[WeekStartFormatted]
,D.[WeekEndFormatted]
,D.[MonthNumber]
,D.[MonthStart]
,D.[MonthEnd]
,D.[MonthStartFormatted]
,D.[MonthEndFormatted]
,D.[QuarterNumber]
,D.[QuarterStart]
,D.[QuarterEnd]
,D.[QuarterStartFormatted]
,D.[QuarterEndFormatted]
,D.[YearNumber]
,D.[YearStart]
,D.[YearEnd]
,D.[YearStartFormatted]
,D.[YearEndFormatted]
FROM
(
SELECT
[groupID]
,[groupName]
,[agentID]
,[agentName]
,[evaluationID]
,DATEPART(yyyy,[evalDateInProgress])*10000 + DATEPART(mm,[evalDateInProgress])*100 + DATEPART(dd,[evalDateInProgress]) AS InProgressDateID
,DATEPART(yyyy,[evalDateScheduled])*10000 + DATEPART(mm,[evalDateScheduled])*100 + DATEPART(dd,[evalDateScheduled]) AS ScheduledDateID
,DATEPART(yyyy,[evalDateInReview])*10000 + DATEPART(mm,[evalDateInReview])*100 + DATEPART(dd,[evalDateInReview]) AS InReviewDateID
,DATEPART(yyyy,[evalDateFinalized])*10000 + DATEPART(mm,[evalDateFinalized])*100 + DATEPART(dd,[evalDateFinalized]) AS FinalizedDateID
,[evaluatorID]
,[evaluatorName]
,[adjustedScore]
,[possibleScore]
FROM @srcTable
) S
JOIN
(
SELECT DISTINCT
dd.calendarID AS [DateID]
,dd.dayOfYear AS [DayOfYearNumber]
,dd.calendarDate AS [DayStart]
,DATEADD(ms,-2,DATEADD(dd,1,CAST(dd.calendarDate AS DATETIME))) AS [DayEnd]
,dbo.GetFormattedTime(dd.calendarDate) AS DayStartFormatted
,dbo.GetFormattedTime(DATEADD(ss,59,DATEADD(mi, 59,DATEADD(hh,23,dd.calendarDate)))) AS DayEndFormatted
,dd.weekNumber AS [WeekNumber]
,dd.weekStartDate AS [WeekStart]
,DATEADD(ms,-2,DATEADD(dd,1,CAST(dd.weekEndDate AS DATETIME))) AS [WeekEnd]
,dbo.GetFormattedTime(dd.weekStartDate) AS WeekStartFormatted
,dbo.GetFormattedTime(DATEADD(ss,59,DATEADD(mi, 58,DATEADD(hh,23,dd.weekEndDate)))) AS WeekEndFormatted
,dd.monthNumber AS [MonthNumber]
,dd.monthStartDate AS [MonthStart]
,DATEADD(ms,-2,DATEADD(dd,1,CAST(dd.monthEndDate AS DATETIME))) AS [MonthEnd]
,dbo.GetFormattedTime(dd.monthStartDate) AS MonthStartFormatted
,dbo.GetFormattedTime(DATEADD(ss,59,DATEADD(mi, 58,DATEADD(hh,23,dd.monthEndDate)))) AS MonthEndFormatted
,dd.quarterNumber AS [QuarterNumber]
,dd.quarterStartDate AS [QuarterStart]
,DATEADD(ms,-2,DATEADD(dd,1,CAST(dd.quarterEndDate AS DATETIME))) AS [QuarterEnd]
,dbo.GetFormattedTime(dd.quarterStartDate) AS QuarterStartFormatted
,dbo.GetFormattedTime(DATEADD(ss,59,DATEADD(mi, 58,DATEADD(hh,23,dd.quarterEndDate)))) AS QuarterEndFormatted
,dd.yearNumber AS [YearNumber]
,dd.yearStartDate AS [YearStart]
,DATEADD(ms,-2,DATEADD(year,1,CAST(dd.yearStartDate AS DATETIME))) AS [YearEnd]
,(CAST(dd.yearNumber AS VARCHAR(100)) + '-01-01 00:00:00') AS YearStartFormatted
,(CAST(dd.yearNumber AS VARCHAR(100)) + '-12-31 23:59:59') AS YearEndFormatted
FROM
(
SELECT DISTINCT s.calendarID
FROM
(
SELECT DISTINCT DATEPART(yyyy,[evalDateInProgress])*10000 + DATEPART(mm,[evalDateInProgress])*100 + DATEPART(dd,[evalDateInProgress]) AS calendarID
FROM @srcTable
WHERE [evalDateInProgress] BETWEEN @timeFrom AND @timeTo
UNION ALL
SELECT DISTINCT DATEPART(yyyy,[evalDateScheduled])*10000 + DATEPART(mm,[evalDateScheduled])*100 + DATEPART(dd,[evalDateScheduled]) AS calendarID
FROM @srcTable
WHERE [evalDateScheduled] BETWEEN @timeFrom AND @timeTo
UNION ALL
SELECT DISTINCT DATEPART(yyyy,[evalDateInReview])*10000 + DATEPART(mm,[evalDateInReview])*100 + DATEPART(dd,[evalDateInReview]) AS calendarID
FROM @srcTable
WHERE [evalDateInReview] BETWEEN @timeFrom AND @timeTo
UNION ALL
SELECT DISTINCT DATEPART(yyyy,[evalDateFinalized])*10000 + DATEPART(mm,[evalDateFinalized])*100 + DATEPART(dd,[evalDateFinalized]) AS calendarID
FROM @srcTable
WHERE [evalDateFinalized] BETWEEN @timeFrom AND @timeTo
) s
) dates JOIN agDateDimension dd ON dates.calendarID = dd.calendarID
) D ON D.DateID = S.InProgressDateID OR D.DateID = S.ScheduledDateID OR D.DateID = S.InReviewDateID OR D.DateID = S.FinalizedDateID
ORDER BY
S.[agentName]
,S.[groupName]
,S.[evaluatorName]
,D.[DayStart]
END
July 27, 2010 at 10:29 am
Sorry to waste anyone's time, but the problem was the (final) udf itself.
It was a table udf with a scalar udf in the where clause; re-working the scalar udf results into a #temp table with an index and joining to that reduced stored procedure run time from 10 minutes to 20 seconds.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply