cursor performance

  • I have a stored procedure that uses about 15 different cursors that look like this:

    DECLARE file_cursor CURSOR FOR

    SELECT fileID FROM fileList

    WHERE patientID = @patientID

    AND (fileType = '1' OR fileType = '5')

    OPEN file_cursor

    FETCH NEXT FROM file_cursor

    INTO @fileID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @newfileID = NEWID()

    INSERT INTO XMLtemppatientMap(oldpatientID, newpatientID, oldfileID, newfileID) VALUES

    (@patientID, @newpatientID, @fileID, @newfileID)

    FETCH NEXT FROM file_cursor

    INTO @fileID

    END

    CLOSE file_cursor

    DEALLOCATE file_cursor

    I have read that using cursors can hurt performance, and I was wondering if there was a better way of re-writing my cursors.

    I've seen some examples of using loops instead of cursors, but I don't think I can use a loop for the above cursor because I am using the cursor to fill a temporary table.

    If anyone has any suggestions, please let me know...

    Thanks!

  • It would help to see all the code, not just a snippet. Based on the snippet alone, hard to say as there are variables being used in the INSERT that aren't defined anywhere in the snippet.

  • Based on what you provided so far the cursor could be elminated by a single insert statement like this:

    INSERT INTO XMLtemppatientMap(oldpatientID, newpatientID, oldfileID, newfileID)

    SELECT patientID,'@newpatientID',fileID,NEWID()

    FROM fileList

    WHERE patientID = @patientID

    AND (fileType = '1' OR fileType = '5')

    Please note that due to the missing declaration of @newpatientID I used it as a string.

    To extend it even further:

    If this cursor is called from another cursor providing the value for @patientID this outer cursor most probably could be removed as well.

    But like Lynn stated before: there's not enough information...

    Best case: remaining number of cursors after improvement = Zero 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Here is one block of code containing multiple cursors.

    /*Variable declarations*/

    DECLARE @newpatientID uniqueidentifier

    DECLARE @newfileID NVARCHAR(50)

    DECLARE @newLinkID NVARCHAR(50)

    DECLARE @fileID NVARCHAR(50)

    DECLARE @linkID NVARCHAR(50)

    DECLARE @tempprocedureID UNIQUEIDENTIFIER

    DECLARE @parentfileID NVARCHAR(50)

    DECLARE @childfileID NVARCHAR(50)

    DECLARE @orderID INT

    DECLARE @linkText NVARCHAR(MAX)

    DECLARE @xraysID UNIQUEIDENTIFIER

    DECLARE @newxraysID UNIQUEIDENTIFIER

    DECLARE @examID UNIQUEIDENTIFIER

    DECLARE @newexamID UNIQUEIDENTIFIER

    DECLARE @newprocedureID UNIQUEIDENTIFIER

    DECLARE @procedureID UNIQUEIDENTIFIER

    DECLARE @procedureText NVARCHAR(MAX)

    DECLARE @fileType TINYINT

    /*temporary fileID Mapping*/

    DECLARE file_cursor CURSOR FOR

    SELECT fileID FROM fileList

    WHERE patientID = @patientID

    AND (fileType = '1' OR fileType = '5')

    OPEN file_cursor

    FETCH NEXT FROM file_cursor

    INTO @fileID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @newfileID = NEWID()

    INSERT INTO XMLtemporarypatientMap(oldpatientID, newpatientID, oldfileID, newfileID) VALUES

    (@patientID, @newpatientID, @fileID, @newfileID)

    FETCH NEXT FROM file_cursor

    INTO @fileID

    END

    CLOSE file_cursor

    DEALLOCATE file_cursor

    /*linkID Mapping*/

    DECLARE link_cursor CURSOR FOR

    SELECT fileLinkID, parentfileID, childfileID, procedureID FROM fileLinks

    WHERE parentfileID IN(SELECT oldfileID FROM XMLtemporarypatientMap)

    OR childfileID IN(SELECT oldfileID FROM XMLtemporarypatientMap)

    OPEN link_cursor

    FETCH NEXT FROM link_cursor

    INTO @linkID, @parentfileID, @childfileID, @tempprocedureID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @newLinkID = NEWID()

    INSERT INTO XMLtemporaryLinkMap(oldpatientID, newpatientID, oldLinkID, newLinkID, oldprocedureID)

    VALUES (@patientID, @newpatientID, @linkID, @newLinkID, @tempprocedureID)

    FETCH NEXT FROM link_cursor

    INTO @linkID, @parentfileID, @childfileID, @tempprocedureID

    END

    CLOSE link_cursor

    DEALLOCATE link_cursor

    /*xraysID Mapping*/

    DECLARE xrays_cursor CURSOR FOR

    SELECT xraysID, fileID, examID

    FROM fileList WHERE fileID IN (SELECT fileID FROM fileList WHERE patientID = @patientID)

    OPEN xrays_cursor

    FETCH NEXT FROM xrays_cursor

    INTO @xraysID, @fileID, @examID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    /*check for NULL values*/

    IF @xraysID IS NULL

    BEGIN

    SET @newxraysID = NULL

    END

    ELSE

    SET @newxraysID = NEWID()

    IF @examID IS NULL

    BEGIN

    SET @newexamID = NULL

    END

    ELSE

    SET @newexamID = NEWID()

    INSERT INTO XMLtemporaryexamMap(oldexamID, newexamID, fileID, oldxraysID, newxraysID)

    VALUES (@examID, @newexamID, @fileID, @xraysID, @newxraysID)

    FETCH NEXT FROM xrays_cursor

    INTO @xraysID, @fileID, @examID

    END

    CLOSE xrays_cursor

    DEALLOCATE xrays_cursor

    /*procedureID Mapping*/

    DECLARE procedure_cursor CURSOR FOR

    SELECT a.procedureID, a.examID, b.newexamID, b.fileID, a.procedureText

    FROM examprocedures a, XMLtemporaryexamMap b

    WHERE a.examID = b.oldexamID

    OPEN procedure_cursor

    FETCH NEXT FROM procedure_cursor

    INTO @procedureID, @examID, @newexamID, @fileID, @procedureText

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @newprocedureID = NEWID()

    INSERT INTO XMLtemporaryprocedureMap(sessionID, oldfileID, oldprocedureID, newprocedureID, procedureText)

    VALUES (@mySessionID, @fileID, @procedureID, @newprocedureID, @procedureText)

    FETCH NEXT FROM procedure_cursor

    INTO @procedureID, @examID, @newexamID, @fileID, @procedureText

    END

    CLOSE procedure_cursor

    DEALLOCATE procedure_cursor

    Thanks!

  • Step 1: Document what the requirements are for each cursor at a business level.

    Step 2: Write new set based code as if you never saw the cursor... just the requirements.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Oh ok, so I should just replace the cursors with statements like Imu92 suggested above? How would that work when I need to insert a new variable value into each row? With the cursor, I just used something like: SET @newfileID = NEWID() before each row insert....

    Thanks!

  • NewID gives a new value per row, so you can just put that in the INSERT .. SELECT statement, exactly as Lutz did in his post.

    If it's something other than NewID, give us the details and we can advise.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Try this to illustrate Gail's explanation:

    SELECT TOP 10 NEWID() FROM sys.objects

  • Oh ok, I see, that makes sense

    Thanks for all the help!

  • Seems that most of your code follows a similar pattern for each of the cursors, so can be replaced by code similar to Lutz's example earlier.

    The section marked "xraysID Mapping" differs slightly but can be handled easily by using CASE something like this:

    INSERT

    XMLtemporaryexamMap

    (

    oldexamID,

    newexamID,

    fileID,

    oldxraysID,

    newxraysID

    )

    SELECT

    examID,

    CASE WHEN examID IS NULL THEN NULL ELSE NEWID() END,

    fileID,

    xraysID,

    CASE WHEN xraysID IS NULL THEN NULL ELSE NEWID() END

    FROM

    fileList

    WHERE

    fileID IN

    (

    SELECT

    fileID

    FROM

    fileList

    WHERE

    patientID = @patientID

    )

    Hope this helps.

    Please note that I've not tested the code.

  • One thing I will add. If you have values (such as the GUIDs) from previous inserted data that you do in a set-based statement that you need to use for subsequent set-based operations take a look at the OUTPUT clause, which you can use to get at the newly inserted data.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 11 posts - 1 through 10 (of 10 total)

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