tuning the query which is having cursors

  • 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

  • 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

  • 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'

  • 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