March 31, 2010 at 7:30 am
There is one procedure which is written with cursors within cursors. The code is copied in this. Can anyone tell me how we can tune the procedure so that it runs faster.
ALTER PROCEDURE [dbo].[PerStat_Starts]
----------------------------------------------------------------------------------
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
-- declare variables
DECLARE @EquipmentNumber_FKBIGINT
DECLARE @SNRule_FKINT
DECLARE @CEN_FKINT
DECLARE @AttributeVARCHAR(50)
DECLARE @LimitFaultsPerStartsDECIMAL (18, 4)
DECLARE @MessageType_FKINT
DECLARE @RemoteMonitoringType_FKVARCHAR(10)
DECLARE @EquipmentClass_FKVARCHAR(10)
DECLARE @AnalyzerType_FKVARCHAR(10)
DECLARE @StatisticsPeriodStartTimeDATETIME
DECLARE @StatisticsPeriodEndTimeDATETIME
DECLARE @StatisticsStartTimeDATETIME
DECLARE @StatisticsEndTimeDATETIME
DECLARE @TotalStartsINT
DECLARE @StartsInPeriodINT
DECLARE @StopLoopBIT
DECLARE @LatestPeriodBIT
DECLARE @FloorNbrINT
DECLARE @PeriodHoursINT
DECLARE @CurrentDateTimeDATETIME
SET @CurrentDateTime = GetDate()
DECLARE @LimitFixedStartsINT
SET @LimitFixedStarts = 10
-- declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0
-- declare and initialize a variable to hold @@ROWCOUNT.
DECLARE @RowsAffectedTotal INT
SET @RowsAffectedTotal = 0
DECLARE @RowsAffected INT
-- declare a variable to hold description of the current operation
DECLARE @OperationVARCHAR(500)
SELECT @ErrorSave = @@ERROR
SET @Operation = 'START'
EXEC InsertTaskLog_p 1,'SNRuleFaultsPerStat_Starts_FloorByFloor_p', @Operation, 0, @ErrorSave
-- get service need rules for equipments from table SNRuleFaultsPerStat:
-- selects only rows with LimitFaultsPerFixedStarts > 0
DECLARE SNRuleTable_cursor CURSOR FOR
SELECT DISTINCT
E.EquipmentNumber_FK,
E.Floor,
P.SNRule_PK,
P.AttributeStat,
P.CEN_FK,
P.LimitCoefficient,
P.MessageType_FK,
P.EquipmentClass_FK,
P.RemoteMonitoringType_FK,
P.AnalyzerType_FK
FROM
SNRuleFaultsPerStat P
INNER JOIN Event E ON P.CEN_FK = E.CEN AND P.RemoteMonitoringType_FK = E.RemoteMonitoringType_FK
INNER JOIN Equipment EQ ON P.EquipmentClass_FK = EQ.EquipmentClass_FK AND P.RemoteMonitoringType_FK = EQ.RemoteMonitoringType_FK AND P.AnalyzerType_FK = EQ.AnalyzerType_FK AND E.EquipmentNumber_FK = EQ.EquipmentNumber
WHERE
ISNULL(LimitCoefficient, 0) > 0 AND
AttributeStat = 'STARTS' AND
E.StartTimestamp BETWEEN DATEADD(month, -1, @CurrentDateTime) AND DATEADD(day, 1, @CurrentDateTime) AND
P.FloorByFloor = '1'
OPEN SNRuleTable_cursor
FETCH NEXT FROM SNRuleTable_cursor INTO
@EquipmentNumber_FK,
@FloorNbr,
@SNRule_FK,
@Attribute,
@CEN_FK,
@LimitFaultsPerStarts,
@MessageType_FK,
@EquipmentClass_FK,
@RemoteMonitoringType_FK,
@AnalyzerType_FK
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalStarts = 0
SET @StopLoop = 0
SET @LatestPeriod = 1
-- get count of total starts for the equipment:
DECLARE TotalStats_cursor CURSOR FOR
SELECT
StatisticsStartTime,
StatisticsEndTime,
Starts
FROM
TotalStats_FloorByFloor_v
WHERE
EquipmentNumber_FK = @EquipmentNumber_FK AND
FloorNbr = @FloorNbr
ORDER BY
StatisticsEndTime DESC
OPEN TotalStats_cursor
FETCH NEXT FROM TotalStats_cursor INTO
@StatisticsPeriodStartTime,
@StatisticsPeriodEndTime,
@StartsInPeriod
WHILE @@FETCH_STATUS = 0 AND @StopLoop = 0
BEGIN
IF @LatestPeriod = 1
BEGIN
-- save the StatisticsEndTime for setting the timelimits when counting events
SET @LatestPeriod = 0
SET @StatisticsEndTime = @StatisticsPeriodEndTime
END
-- loop periods until the sum of total starts > Limit values (minimun period)
SET @TotalStarts = @TotalStarts + @StartsInPeriod
IF @TotalStarts > @LimitFixedStarts
SET @StopLoop = 1
IF @StopLoop = 1
BEGIN
-- now: total starts > Limit values
-- save the StatisticsStartTime for setting the timelimits when counting events
SET @StatisticsStartTime = @StatisticsPeriodStartTime
SET @PeriodHours = DATEDIFF(hour, @StatisticsStartTime, @StatisticsEndTime)
EXEC @RowsAffected = SN_EventCount_p
'SNRuleFaultsPerStat',
'SN_FPS_F',
@CurrentDateTime,
@EquipmentNumber_FK,
@SNRule_FK,
@CEN_FK,
@Attribute,
@MessageType_FK,
@RemoteMonitoringType_FK,
@EquipmentClass_FK,
@AnalyzerType_FK,
@StatisticsStartTime,
@StatisticsEndTime,
@LimitFixedStarts,
@LimitFaultsPerStarts,
NULL, -- @LimitFaultsPerPeriodHours
NULL, -- @LimitDurationPerPeriod
@PeriodHours,
NULL, -- @RequiredIntervalInMinutes
@TotalStarts,
1, -- @FloorByFloor
@FloorNbr
SET @RowsAffectedTotal = @RowsAffectedTotal + @RowsAffected
END
-- Get the row in TotalStats_cursor
FETCH NEXT FROM TotalStats_cursor INTO
@StatisticsPeriodStartTime,
@StatisticsPeriodEndTime,
@StartsInPeriod
END
CLOSE TotalStats_cursor
DEALLOCATE TotalStats_cursor
-- Get the row in SNRuleTable_cursor
FETCH NEXT FROM SNRuleTable_cursor INTO
@EquipmentNumber_FK,
@FloorNbr,
@SNRule_FK,
@Attribute,
@CEN_FK,
@LimitFaultsPerStarts,
@MessageType_FK,
@EquipmentClass_FK,
@RemoteMonitoringType_FK,
@AnalyzerType_FK
END
CLOSE SNRuleTable_cursor
DEALLOCATE SNRuleTable_cursor
SET @Operation = 'Insert new SNRuleFaultsPerStat_Starts_FloorByFloor_p service needs.'
EXEC InsertTaskLog_p 0,'SNRuleFaultsPerStat_Starts_FloorByFloor_p', @Operation, @RowsAffectedTotal, @ErrorSave
SET @Operation = 'END'
EXEC InsertTaskLog_p 1,'SNRuleFaultsPerStat_Starts_FloorByFloor_p', @Operation, 0, @ErrorSave
---
SET NOCOUNT OFF
Thanks in advance
NAveen
March 31, 2010 at 7:51 am
I don't think you will get any useful replies this way. Many things are missing.
You're not saying which tables are involved and what they look like, and, first of all, you're not saying what you're after.
Your code does lots of calls to other stored procedures, but you didn't include the code.
I suggest that you cut down this code into pieces and try to get rid of the cursors, using set-based code instead.
If you want advice on how to go down that road, don't simply post your code and wait for us to tune it for you. You'd better find the most problematic part of the procedure and ask for help on how to turn it into set based updates.
You may find useful taking a look at the article linked in my signature.
-- Gianluca Sartori
April 8, 2010 at 10:06 am
Without knowing your output, here's some things that jump out to me as typically costly syntax:
- Replace CURSORS with Set-based logic (as the previous post suggested), or, try a LOOP instead
- Do you need the "Select distinct" in your cursor? ... Distinct is a more expensive command
- For Where ISNULL(LimitCoefficient, 0) > 0 ... Could this instead read "where LimitCoefficient > 0" (preventing RBAR)
- Can parts of your where criteria be done on the tables prior to the joins by including them as Virtual tables? (see example below)
- Is a Order by necessary? .... For millions of rows this is VERY costly
Try running your execution plan to see if you can identify a particular task that is taking the bulk of the processing time
Here's a VT example:
declare @StartDate datetime
set @StartDate = DATEADD(month, -1, @CurrentDateTime)
declare @EndDate datetime
set @EndDate = DATEADD(day, 1, @CurrentDateTime)
SELECT DISTINCT
E.EquipmentNumber_FK,
E.Floor,
P.SNRule_PK,
P.AttributeStat,
P.CEN_FK,
P.LimitCoefficient,
P.MessageType_FK,
P.EquipmentClass_FK,
P.RemoteMonitoringType_FK,
P.AnalyzerType_FK
FROM SNRuleFaultsPerStat P
INNER JOIN
(Select EquipmentNumber_FK,
[Floor],
Cen,
RemoteMonitoringType_FK
from Event
where StartTimestamp BETWEEN @StartDate AND @EndDate
) E
ON P.CEN_FK = E.CEN
AND P.RemoteMonitoringType_FK = E.RemoteMonitoringType_FK
INNER JOIN Equipment EQ
ON P.EquipmentClass_FK = EQ.EquipmentClass_FK
AND P.RemoteMonitoringType_FK = EQ.RemoteMonitoringType_FK
AND P.AnalyzerType_FK = EQ.AnalyzerType_FK
AND E.EquipmentNumber_FK = EQ.EquipmentNumber
WHERE
ISNULL(LimitCoefficient, 0) > 0 AND
AttributeStat = 'STARTS' AND
--E.StartTimestamp BETWEEN DATEADD(month, -1, @CurrentDateTime) AND DATEADD(day, 1, @CurrentDateTime) AND
P.FloorByFloor = '1'
April 9, 2010 at 9:53 am
Forums are designed for short, quick, targeted advice since they are 'staffed' by volunteers. This isn't any of those. 🙂 It could take many hours of time to help you refactor this sproc, and I advise you to look at getting some professional help to do it.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply