0

I have this below query which calls a stored procedure that takes only 1 item's attributes at a time. But because of performance problems we are required to remove the cursor. How can I replace the below cursor logic with set operations or CTE? Please advice.

DECLARE db_cursor_ava CURSOR  
    FOR
        SELECT t.[agent-id],
               t.[start-date],
               t.[end-date],
               t.[monitor-days],
               t.[monitor-start],
               t.[monitor-end],
               t.[timezone-offset]
        FROM   @tmpAgentPeriodTimeRange t

    OPEN db_cursor_ava 
    FETCH NEXT FROM db_cursor_ava INTO @agentID_ava,
    @stDateTime_ava,
    @endDateTime_ava,
    @monDays_ava,
    @monSt_ava,
    @monEnd_ava,
    @offset_ava

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE 
        FROM   @tmpMonitorPeriod

        DELETE 
        FROM   @tmpFinalResult

        SET @runID = 1

        IF(@endDateTime_ava>DATEADD(MI,@offset_ava, GETUTCDATE()))
        BEGIN
            SET @endDateTime_ava=DATEADD(MI,@offset_ava, GETUTCDATE())
        END


        INSERT INTO @tmpMonitorPeriod
        EXEC core.usp_GetMonitoringPeriod 
             @startDate = @stDateTime_ava,
             @endDate = @endDateTime_ava,
             @monitoringDays = @monDays_ava,
             @monitoringStart = @monSt_ava,
             @monitoringEnd = @monEnd_ava

        SELECT @maxID = MAX(tm.id)
        FROM   @tmpMonitorPeriod tm



        FETCH NEXT FROM db_cursor_ava INTO @agentID_ava,
        @stDateTime_ava,
        @endDateTime_ava,
        @monDays_ava,
        @monSt_ava,
        @monEnd_ava,
        @offset_ava
    END 

    CLOSE db_cursor_ava 
    DEALLOCATE db_cursor_ava
  • Is the procedure call ( core.usp_GetMonitoringPeriod ) required ? If so, you can't really avoid using a row-by-row processing method. – druzin Apr 16 '15 at 10:07
  • yes its a procedure call. Is there any other ways to do it? Like if its a function can I? – mayooran Apr 16 '15 at 10:08
  • 1
    Possibly if you deconstruct the statement within the procedure you could rewrite this a set based operation. How complex is the logic in the procedure ? – druzin Apr 16 '15 at 10:11
  • 3
    At least start by changing the cursor to DECLARE db_cursor_ava CURSOR FAST_FORWARD but to optimize this we need to know what the procedure is doing – Spörri Apr 16 '15 at 10:13
  • 1
    @mayooran depends on what it's doing but that doesn't bode well – Tom V - Team Monica Apr 16 '15 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.