WILL CURSOR AFFECT MY Performance

  • I have one scenario, I need to calcualte performance for each ID based on the updated value, so i used Cursor, IN Cursor I am calling the performance SP and passing ID, My cursor is running 35 times. Will it affect the perfomance in future. What is the another way to do this?

  • Cursors have bad reputation, but in my opinion it is more because they are being used where they shouldn't be used. There were many cases in the past that I was able to boost performance by modifying code that used cursor into code that used DML statement on all rows. Of course that doesn't mean that cursors should be avoided in all cost. There are times that it helps us. For me it is mostly on administration scripts (for example fixing fragmentation scripts). Since we have no idea about your code and what you are trying to do, no one can tell you if you should modify your code and logic.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Following on from Adi.

    Please detail exactly what you are trying to acheive.

    Supply DDL, DML and sample data along with the expected outcome (2nd link in my signature will help you on that).

    With this information we would be able to detail what the best way to tackle the problem is, be it in a cursor or a set based operation.

  • Without seeing & testing the code, it's hard to make a definitive answer. But based on what you've said, yes, I suspect strongly that the cursor is going to affect performance negatively. It sounds like you would be better off using an IN statement or using a tally table to turn a list into a derived table that you can join against. For details on the tally table approach, look up the words "tally table" and "Jeff Moden" in the search box here on SSC. You'll find what you need there.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi Thanks for all of your reply....

    Here is my code

    --- Cursor To EXECUTE PERFORMANCE SP FOR EACH METRICID

    DECLARE @METID INT

    DECLARE @METRICVALUE FLOAT

    DECLARE @GOAL FLOAT

    DECLARE @TARGET FLOAT

    DECLARE @PDID BIGINT

    DECLARE @PHASE BIGINT

    DECLARE @METRICID CURSOR

    SET @METRICID = CURSOR FOR

    SELECT MEtricsID,METRICSVALUE,GOAL,TARGET,PDID,PHASEID

    FROM @LEANTEMP

    OPEN @METRICID

    FETCH NEXT

    FROM @METRICID INTO @METID,@METRICVALUE,@GOAL,@TARGET,@PDID,@PHASE

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC [EFFORTVARIANCEPERFORMANCE] @LEVELVALUEID,@METID,@METRICVALUE,@GOAL,@TARGET,@FROMDATE,@TODATE,@PDID,@PHASE

    FETCH NEXT

    FROM @METRICID INTO @METID,@METRICVALUE,@GOAL,@TARGET,@PDID,@PHASE

    END

    CLOSE @METRICID

    DEALLOCATE @METRICID

  • Again, not knowing what's happening inside that procedure, I can't definitively say that yes, you can eliminate the cursor. But I'm still very strongly suggesting it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Here I am taking records from @LEANTEMP table and passing the values to SP to calculate performance for each Metric ID.

  • Hi,

    I don't know how it will affect your performance but definetely cursor is going to slow down the performance of your script above. In your case, how many records the table will grow up in the future?

    It would be better to optimize it by adding a ID (identity column) to @LEANTEMP table and do a incremental loop. This way you can improve the performance. Also, you can think of changing your logic into SET-based approach. You can go thru the below link for SET-based approach.

    http://www.simple-talk.com/sql/database-administration/the-road-to-professional-database-development-set-based-thinking/

    Regards,
    Ravi.

  • Thanks Ravi i have changed the code, I am using while loop now...Hope it will not affect my performance in future 🙂

  • What kind of result is your SP returning??....If you are doing it for the whole table then may be you can change the SP to a Table Valued Function and Apply it on the whole table(Cross Apply)... Depends on what your SP is returning.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vijayarani87.s (6/27/2012)


    Thanks Ravi i have changed the code, I am using while loop now...Hope it will not affect my performance in future 🙂

    Is all you have done replaced the CURSOR with a WHILE ?

    This is a common anti-pattern, and any performance improvement is due to the default cursor being a writable cursor not read only.

    Brad Shultz has a great 3 part article series starting here:

    http://bradsruminations.blogspot.co.uk/2010/05/truth-about-cursors-part-1.html

    Can you post the code of EFFORTVARIANCEPERFORMANCE , depending on complexity this could be turned into an inline table valued function ( or even a view) which will help considerably.



    Clear Sky SQL
    My Blog[/url]

  • Hi Dave here is my code for performance

    ALTER PROCEDURE [SCHEMAMANLOG].[PROC_TESTING_EFFORTVARIANCEPERFORMANCE]

    (

    @LEVELVALUEID BIGINT,

    @METRICSID INT ,

    @METRICSVALUE FLOAT,

    @GOAL FLOAT,

    @TARGET FLOAT,

    @FROMDATE DATE,

    @TODATE DATE,

    @ProcessDisciplineID INT,

    @PHASEID BIGINT

    )

    AS

    BEGIN

    DECLARE @COLORIND INT

    DECLARE @PERFORMANCE varchar(50)

    SELECT @COLORIND = HIGHERLOWERTHEBETTER

    FROM SCHEMAMANLOG.MASMETRICS

    WHERE METRICSID=@METRICSID

    IF(@COLORIND=3 or @COLORIND=1) -- Higher THE BETTER/NORMAL

    BEGIN

    IF(@METRICSVALUE >= @TARGET)

    set @PERFORMANCE='GREEN'

    ELSE IF(@METRICSVALUE < @TARGET)

    set @PERFORMANCE='RED'

    ELSE IF(@METRICSVALUE is NULL)

    set @PERFORMANCE='BLUE'

    END

    IF(@COLORIND=2) --Lower The Better

    BEGIN

    IF(@METRICSVALUE <= @TARGET)

    set @PERFORMANCE='GREEN'

    ELSE IF(@METRICSVALUE > @TARGET)

    set @PERFORMANCE='RED'

    ELSE IF(@METRICSVALUE is NULL)

    set @PERFORMANCE='BLUE'

    END

    --OVERALL

    Begin

    UPDATE MEAS

    SET MEAS.TSPP_PERFORMANCE = (CASE when @METRICSID=195 THEN @PERFORMANCE ELSE MEAS.TSPP_Performance END),

    MEAS.PCE_PERFORMANCE = (CASE when @METRICSID=143 THEN @PERFORMANCE ELSE MEAS.PCE_PERFORMANCE END),

    MEAS.ADLS3_PERFORMANCE = (CASE when @METRICSID=148 THEN @PERFORMANCE ELSE MEAS.ADLS3_PERFORMANCE END),

    MEAS.EDR_PERFORMANCE = (CASE when @METRICSID=149 THEN @PERFORMANCE ELSE MEAS.EDR_PERFORMANCE END),

    MEAS.CPUOM_PERFORMANCE = (CASE when @METRICSID=154 THEN @PERFORMANCE ELSE MEAS.CPUOM_PERFORMANCE END),

    MEAS.RDL_PERFORMANCE = (CASE when @METRICSID=155 THEN @PERFORMANCE ELSE MEAS.RDL_PERFORMANCE END),

    MEAS.ACOQ_PERFORMANCE = (CASE when @METRICSID=205 THEN @PERFORMANCE ELSE MEAS.ACOQ_PERFORMANCE END),

    MEAS.PCOQ_PERFORMANCE = (CASE when @METRICSID=206 THEN @PERFORMANCE ELSE MEAS.PCOQ_PERFORMANCE END),

    MEAS.FCOQ_PERFORMANCE = (CASE when @METRICSID=207 THEN @PERFORMANCE ELSE MEAS.FCOQ_PERFORMANCE END),

    MEAS.PREEFF_PERFORMANCE = (CASE when @METRICSID=208 THEN @PERFORMANCE ELSE MEAS.PREEFF_PERFORMANCE END),

    MEAS.ADD_PERFORMANCE = (CASE when @METRICSID=214 THEN @PERFORMANCE ELSE MEAS.ADD_PERFORMANCE END),

    MEAS.ADDS1_PERFORMANCE = (CASE when @METRICSID=218 THEN @PERFORMANCE ELSE MEAS.ADDS1_PERFORMANCE END),

    MEAS.ADDS2_PERFORMANCE = (CASE when @METRICSID=219 THEN @PERFORMANCE ELSE MEAS.ADDS2_PERFORMANCE END),

    MEAS.RDD_PERFORMANCE = (CASE when @METRICSID=220 THEN @PERFORMANCE ELSE MEAS.RDD_PERFORMANCE END),

    MEAS.SVTD_PERFORMANCE = (CASE when @METRICSID=221 THEN @PERFORMANCE ELSE MEAS.SVTD_PERFORMANCE END),

    MEAS.SVTE_PERFORMANCE = (CASE when @METRICSID=225 THEN @PERFORMANCE ELSE MEAS.SVTE_PERFORMANCE END),

    MEAS.TC_PERFORMANCE = (CASE when @METRICSID=229 THEN @PERFORMANCE ELSE MEAS.TC_PERFORMANCE END),

    MEAS.EC_PERFORMANCE = (CASE when @METRICSID=230 THEN @PERFORMANCE ELSE MEAS.EC_PERFORMANCE END),

    MEAS.TED_PERFORMANCE = (CASE when @METRICSID=234 THEN @PERFORMANCE ELSE MEAS.TED_PERFORMANCE END)

    FROM schemamanlog.trnleanmetricsperformance "MEAS"

    WHERE MEAS.c20phaseid IS NULL

    AND MEAS.c20processdisciplineid IS NULL

    AND MEAS.levelvalueID= @LEVELVALUEID

    AND MEAS.fromdate = @FROMDATE

    AND MEAS.todate = @TODATE

    AND MEAS.isActive = 1

    END

    END

  • vijayarani87.s (6/27/2012)


    Thanks Ravi i have changed the code, I am using while loop now...Hope it will not affect my performance in future 🙂

    Just so we're clear, a WHILE loop is another type of cursor. They may not perform any faster, and can, depending on how it's used, perform slower.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Based on that query code for the procedure, you can do the entire thing just by joining the query that fills your cursor to the update statement. You can do it in a single batch statement and be done.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I dont think i can do this stuff in a single statement, because i need to find which colouring ID each metrics belongs to based on that only i can update records for each metric id

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply