更换光标以获得更好的性能 [英] Replacing cursor for better performance
本文介绍了更换光标以获得更好的性能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我必须为每一行调用一个sp，并且sp本身有2个光标（游标中有光标）需要更多时间。请给我一些想法来替换curosr并提高性能。
Hi,
I have a sp which has to be called for each and every row and that sp itself having 2 cursor(Cursor with in a cursor) which takes more time. Please give me some idea to replace the curosr and improve the performance.
DECLARE S_Item CURSOR FOR
SELECT a.TitleNo,a.StartDate,a.StartTime,case when a.EndTime =0 then 84600 else a.EndTime end as EndTime
FROM ScheduleItem a, schedules b
WHERE a.ScheduleNo = b.ScheduleNo
and a.TitleNo = @TitleNo
and a.PromoRun = 'N'
and b.StatusNo = 3
--and b.FeedNo not in (7,9)
and b.FeedNo <> (case b.networkNo WHEN 7 THEN -1 ELSE 7 END)
and b.FeedNo <> (case b.networkNo WHEN 7 THEN -1 ELSE 9 END)
and b.FeedNo <> (case b.networkNo WHEN 7 THEN -1 ELSE 19 END) --SEPCM00098068
and b.Networkno = @NetworkNo -- Network filter is added for fetching the Network wise details CQ# SEPCM00082905
ORDER BY a.StartDate, a.StartTime
SET @RunsScheduled = 0
SET @PDEndDate = NULL
SET @PDEndTime = -1
SET @PDStartTime = -1 --SEPCM00072793 Implementation of logic for single play day definition
SET @PrevPlayWindowNo = -1
OPEN S_Item
FETCH NEXT FROM S_Item
INTO @TitleNo, @SchedDate, @StartTime, @EndTime
--Starting of the Logic to check whether the Title has a play window CQ# SEPCM00082905
WHILE @@FETCH_STATUS = 0
BEGIN
--If it is a single calendar play day and if the air date is equal to the window end date then do not consider airings after 12:00 AM - starts - CQ# 72793
IF EXISTS (SELECT 1
FROM PlayWindows p, Playwindownetwork n
WHERE @SchedDate = p.WindowEndDate
AND p.PlayDayDefnNo = 4
AND @StartTime >= 64800
AND p.TitleNo = @TitleNo
and p.playwindowno=n.playwindowno
and n.delflag='N'
and n.networkno=@NetworkNo
AND p.DelFlag = 'N')
BEGIN
SET @SchedDate = DATEADD(day,1,@SchedDate)
END
--If it is a single calendar play day and if the air date is equal to the window end date then do not consider airings after 12:00 AM - ends - CQ# 72793
IF Exists(SELECT 1
FROM PlayWindows p, Playwindownetwork n
WHERE p.TitleNo = @TitleNo
AND @SchedDate BETWEEN p.WindowStartDate and p.WindowEndDate
and p.playwindowno=n.playwindowno
and n.delflag='N'
and n.networkno=@NetworkNo
AND p.DelFlag = 'N' )
BEGIN
SET @BoolWindowExists = 1
END
ELSE
BEGIN
SET @BoolWindowExists = 0
END
--End of the Logic to check whether the Title has a play window CQ# SEPCM00082905
SELECT
@PlayWindowNo = p.PlayWindowNo,
@PlaydayDefnNo = p.PlaydayDefnNo
FROM PlayWindows p, Playwindownetwork n
WHERE p.TitleNo = @TitleNo
AND (CASE WHEN EXISTS (SELECT 1
FROM PlayWindows t, Playwindownetwork tn
WHERE DATEADD(day,1,@SchedDate) = t.WindowStartDate
AND t.PlayDayDefnNo = 4
AND @StartTime >= 64800
AND t.TitleNo = @TitleNo
and t.playwindowno=tn.playwindowno
and tn.delflag='N'
and tn.networkno=@NetworkNo
AND t.DelFlag = 'N' ) THEN DATEADD(day,1,@SchedDate)
ELSE @SchedDate END) BETWEEN p.WindowStartDate AND p.WindowEndDate
and p.playwindowno=n.playwindowno
and n.delflag='N'
and n.networkno=@NetworkNo
AND p.DelFlag = 'N'
/**********************************************************************************************************************************
Revert back the date added to the air date due to single calendar playday. This addition and subtraction in end date happens because
the airings after 12 AM in the window end date for single play day should not be considered for discount. --CQ# 72793
***********************************************************************************************************************************/
IF EXISTS (SELECT 1
FROM PlayWindows p, Playwindownetwork n
WHERE DATEADD(day,-1,@SchedDate) = p.WindowEndDate
AND p.PlayDayDefnNo = 4
AND @StartTime >= 64800
AND p.TitleNo = @TitleNo
and p.playwindowno=n.playwindowno
and n.delflag='N'
and n.networkno=@NetworkNo
AND p.DelFlag = 'N' )
BEGIN
SET @SchedDate = DATEADD(day,-1,@SchedDate)
END
IF @PrevPlayWindowNo <> -1
BEGIN
IF @PrevPlayWindowNo <> @PlayWindowNo
BEGIN
--Check for runs remaining
SELECT @RunsRemaining = (ISNULL(NoOfPlayDays,0) - ISNULL(PlaysTaken,0))
FROM Playwindows
WHERE PlaywindowNo = @PrevPlayWindowNo and TitleNo = @TitleNo
IF IsNull(@RunsRemaining, 0) < @RunsScheduled
BEGIN
SET @RunsCarryOver = @RunsScheduled - ISNULL(@RunsRemaining, 0)
--EXEC sp_update_CarryOverRuns @TitleNo,@PrevPlayWindowNo, @RunsCarryOver,@userid
END
--********************************
--Change for CQ# SEPCM00082905 - Add the new No.Of Runs with the value present in the play window table - Starts
UPDATE Playwindows set NoRunsScheduled = ISNULL(NoRunsScheduled,0)+@RunsScheduled WHERE PlaywindowNo = @PrevPlayWindowNo and TitleNo = @TitleNo
-
SET @RunsScheduled = 0
SET @PDEndDate = NULL
SET @PDEndTime = -1
END
END
SELECT @PlayDaytype = PlayDayDefn, @HoursInPlayDay = HoursInPlayDay --Selected Playdaydefn instead of playdaytype - 72793
FROM PlaydayDefinition
WHERE PlayDayDefnNo = @PlaydayDefnNo
SELECT @RunsInPlayday = RunsInPlayDay FROM PlayWindows WHERE PlayWindowNo = @PlayWindowNo
IF @BoolWindowExists = 1
BEGIN
if @PlayDaytype = 'Runs'
set @RunsScheduled = @RunsScheduled + 1
/******************************************************************************************************
Implementation of logic for single play day definition starts
If the next airing ends before 12:00 AM of the calendar day, discount will be applied - SEPCM00072793
*******************************************************************************************************/
ELSE IF @PlayDaytype = 'Single'
BEGIN
SET @PDStartTime = 64800 --Start Time of a calendar day 12:00 AM
SET @PDEndTime = 64800 --End Time of a calendar day 12:00 AM
IF @PDEndDate IS NULL --The very first airing entering inside loop - @Runsscheduled will be incremented
BEGIN
IF @starttime >= @PDStartTime
SET @PDEndDate = DATEADD(day,1,@Scheddate)
ELSE
set @PDEndDate = @Scheddate
SET @RunsInPlaydayTemp = @RunsInPlayday
SET @RunsScheduled = @RunsScheduled + 1
SET @RunsInPlaydayTemp = @RunsInPlaydayTemp - 1
END
ELSE
BEGIN
--Check for the calendar day condition and if then apply the discount
IF @PDEndDate = (CASE WHEN @starttime >= @PDStartTime THEN DATEADD(day,1,@Scheddate) ELSE @Scheddate END) AND @EndTime <= (CASE WHEN @starttime >= @PDStartTime THEN 86400 ELSE @PDEndTime END)
BEGIN
IF @RunsInPlaydayTemp > 0 set @RunsInPlaydayTemp = @RunsInPlaydayTemp - 1 --Condition to apply discount (@Runsscheduled will not be incremented)
ELSE
BEGIN
IF @starttime >= @PDStartTime
SET @PDEndDate = DATEADD(day,1,@Scheddate)
ELSE
SET @PDEndDate = @Scheddate
SET @RunsInPlaydayTemp = @RunsInPlayday
SET @RunsScheduled = @RunsScheduled + 1
SET @RunsInPlaydayTemp = @RunsInPlaydayTemp - 1
END
END
ELSE
BEGIN --In this loop, no discount will be applied because the airing didnt satisfy the discount logic condition
IF @starttime >= @PDStartTime
SET @PDEndDate = DATEADD(day,1,@Scheddate)
ELSE
SET @PDEndDate = @Scheddate
SET @RunsInPlaydayTemp = @RunsInPlayday
SET @RunsScheduled = @RunsScheduled + 1
SET @RunsInPlaydayTemp = @RunsInPlaydayTemp - 1
END
END
END
/***********************************************************************************
Implementation of logic for single play day definition ends - SEPCM00072793
***********************************************************************************/
else
if @PDEndDate IS NULL
begin
set @PDEndDate = dateadd(dd,@HoursInPlayDay/24,@SchedDate)
set @PDEndTime = @StartTime
set @RunsInPlaydayTemp = @RunsInPlayday
set @RunsScheduled = @RunsScheduled + 1
set @RunsInPlaydayTemp = @RunsInPlaydayTemp - 1
end
else if @SchedDate < @PDEndDate
begin
if @RunsInPlaydayTemp > 0 set @RunsInPlaydayTemp = @RunsInPlaydayTemp - 1
else
begin
set @PDEndDate = dateadd(dd,@HoursInPlayDay/24,@SchedDate)
set @PDEndTime = @StartTime
set @RunsInPlaydayTemp = @RunsInPlayday
set @RunsScheduled = @RunsScheduled + 1
set @RunsInPlaydayTemp = @RunsInPlaydayTemp - 1
end
end
else if @SchedDate = @PDEndDate
begin
if @EndTime <= @PDEndTime and @RunsInPlaydayTemp > 0 set @RunsInPlaydayTemp = @RunsInPlaydayTemp - 1
else
begin
set @PDEndDate = dateadd(dd,@HoursInPlayDay/24,@SchedDate)
set @PDEndTime = @StartTime
set @RunsInPlaydayTemp = @RunsInPlayday
set @RunsScheduled = @RunsScheduled + 1
set @RunsInPlaydayTemp = @RunsInPlaydayTemp - 1
end
end
else if @SchedDate > @PDEndDate
begin
set @PDEndDate = dateadd(dd,@HoursInPlayDay/24,@SchedDate)
set @PDEndTime = @StartTime
set @RunsInPlaydayTemp = @RunsInPlayday
set @RunsScheduled = @RunsScheduled + 1
set @RunsInPlaydayTemp = @RunsInPlaydayTemp - 1
end
END
set @PrevPlayWindowNo = @PlayWindowNo
FETCH NEXT FROM S_Item
INTO @TitleNo, @SchedDate, @StartTime, @EndTime
END 推荐答案
see the refs.
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/increase-your-sql-server-performance-by-replacing-cursors-with-set-operations.aspx[^]
http://shannonlowder.com/2012/01/improve-performance-by-replacing-cursors/[^]
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx[^]
see the refs.
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/increase-your-sql-server-performance-by-replacing-cursors-with-set-operations.aspx[^]
http://shannonlowder.com/2012/01/improve-performance-by-replacing-cursors/[^]
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx[^]
这篇关于更换光标以获得更好的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
