Can I avoid using cursors

  • I need to update/create records in a Notes table based upon changes to data in a separate table.

    I wrote a cursor to solve this problem it follows the following basic outline

    It loops through a table that list the type of 'test code' we need to look for to create notes as well as a template of the note to be created.

    Next it loops through all patient records that have this 'test code' the notes are patient specific and not order specific so I added this loop to cut down on the number of function calls to set a particular variable.

    Next it loops through all orders for the patient that has this 'test code' and calls the appropriate stored procedure to update or create the note.

    Is there a better way to do this without using nested cursors? It takes about 1hr for this query to run.

    code follows

    DECLARE @OrderCode as varchar(50)

    DECLARE @DaysBetweenTest as int

    DECLARE @NoteText as varchar(max)

    DECLARE @TemplateNote as varchar(max)

    DECLARE @PatientId as varchar(50)

    DECLARE @LastPerformedDate as DATE

    DECLARE @OrderEventId as INT

    DECLARE @FirstPayableDate as DATE

    DECLARE @NoteId as INT

    DECLARE @FrequencyCheckNoteId as INT

    -- Loop through order codes in FrequencyCheck table

    DECLARE FreqOCLoop CURSOR FOR SELECT OrderCode, DaysBetweenTest, NoteText from dbo.FrequencyCheck

    OPEN FreqOCLoop

    FETCH NEXT FROM FreqOCLoop

    INTO @OrderCode, @DaysBetweenTest, @TemplateNote

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Loop through patients with future events for order code

    DECLARE PatientLoop CURSOR FOR select distinct t3.PatientId from dbo.rvw_OrderEventOrderCodes as t1 inner join

    dbo.[Order] as t2 on t1.OrderId = t2.id inner join

    dbo.Patient as t3 on t2.PatientId = t3.id

    where t1.ScheduledDueDate > getdate() and t1.OrderCode=@OrderCode and t1.[Status]=2

    OPEN PatientLoop

    FETCH NEXT FROM PatientLoop

    INTO @PatientId

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Initialize values

    SET @LastPerformedDate = NULL

    SET @FirstPayableDate = NULL

    SET @NoteText = NULL

    -- Find last performed date for this patient

    SET @LastPerformedDate = (SELECT dbo.TestLastPerformedDate(@OrderCode, @PatientId))

    -- Calculate next valid date

    SET @FirstPayableDate = DATEADD(dd,@DaysBetweenTest,@LastPerformedDate)

    -- Set note value

    IF @LastPerformedDate IS NOT NULL

    BEGIN

    SET @NoteText = REPLACE(@TemplateNote,'<FirstPayableDate>',@FirstPayableDate)

    END

    ELSE

    SET @NoteText = 'No previous results for test code ' + @OrderCode

    -- Find all future OrderEvents for this patient and loop through them to add or update notes

    DECLARE EventLoop CURSOR FOR select distinct t1.OrderEventId from dbo.rvw_OrderEventOrderCodes as t1 inner join

    dbo.[Order] as t2 on t1.OrderId = t2.id inner join

    dbo.Patient as t3 on t2.PatientId = t3.id

    where t1.ScheduledDueDate > getdate() and t1.OrderCode=@OrderCode and t1.[Status]=2 and t3.PatientId = @PatientId

    OPEN EventLoop

    FETCH NEXT FROM EventLoop

    INTO @OrderEventId

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Check to see if we have already added a note for this order

    if (select count(id) from dbo.FrequencyCheckNotes where NoteOrderEventId=@OrderEventId)>0

    BEGIN

    -- Check to see if the currently existing note is the same

    if @LastPerformedDate <> (select LastPerformedDate from dbo.FrequencyCheckNotes where NoteOrderEventId = @OrderEventId)

    BEGIN

    -- Set values

    SET @NoteId = (SELECT NoteId FROM dbo.FrequencyCheckNotes WHERE NoteOrderEventId = @OrderEventId)

    SET @FrequencyCheckNoteId = (SELECT id FROM dbo.FrequencyCheckNotes WHERE NoteOrderEventId = @OrderEventId)

    -- Update OrderEvent note

    EXEC [dbo].[UpdateFrequencyCheckNote]

    @NoteText = @NoteText,

    @NoteId = @NoteId,

    @LastPerformedDate = @LastPerformedDate,

    @FrequencyCheckNoteId = @FrequencyCheckNoteId

    END

    END

    ELSE

    -- Create new note

    EXEC [dbo].[CreateFrequencyCheckNote]

    @NoteText = @NoteText,

    @OrderEventId = @OrderEventId,

    @LastPerformedDate = @LastPerformedDate

    FETCH NEXT FROM EventLoop

    INTO @OrderEventId

    END

    CLOSE EventLoop

    DEALLOCATE EventLoop

    FETCH NEXT FROM PatientLoop

    INTO @PatientId

    END

    CLOSE PatientLoop

    DEALLOCATE PatientLoop

    FETCH NEXT FROM FreqOCLoop

    INTO @OrderCode, @DaysBetweenTest, @TemplateNote

    END

    CLOSE FreqOCLoop

    DEALLOCATE FreqOCLoop

  • there's two procedures that make it a bit complicated to convert to a set based operaiton, becaus3e both procs look like they fiddle with one row at a time:

    [dbo].[UpdateFrequencyCheckNote]

    [dbo].[CreateFrequencyCheckNote]

    based on the name , i guess they insert or update a table...if it's the same table, unless there are some complex calculations in them, i think they can be replaced too; the devil is in the details.

    i think it would be replaced by two set based operations, based on whatever table(s) those procs fiddle with(? OrderEvent from the comment?)

    I'd only be guessing at this point, but i wanted to at least provide a somewhat intellgent example;

    this will not pass syntax, but it kind of shows how you'd update from multiple sources:

    UPDATE MyTargetTable

    SET MyTargetTable.NoteText = OneOfTheSubQueryTables.NoteText,

    MyTargetTable.NoteId = OneOfTheSubQueryTables.NoteId,

    MyTargetTable.LastPerformedDate = OneOfTheSubQueryTables.LastPerformedDate,

    MyTargetTable.FrequencyCheckNoteId = OneOfTheSubQueryTables.FrequencyCheckNoteId

    --SANITY Check: run this SELECT to confirm we would updat ethe right stuff!

    --SELECT *

    FROM dbo.OrderEvent MyTargetTable

    INNER JOIN dbo.FrequencyCheckNotes ON NoteOrderEventId = @OrderEventId

    INNER JOIN (SELECT ColumnList FROM dbo.rvw_OrderEventOrderCodes as t1

    inner join dbo.[Order] as t2 on t1.OrderId = t2.id inner join

    dbo.Patient as t3 on t2.PatientId = t3.id

    where t1.ScheduledDueDate > getdate() and t1.OrderCode=@OrderCode and t1.[Status]=2 and t3.PatientId = @PatientId

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Here's my take:

    Q: Can I avoid using cursors?

    A: Most probably.

    Q: How?

    A: DDL and sample data needed along with expected results to get some working code. An explanation of any calculation rules would also be helpful if they're not obvious from the expected results.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 3 posts - 1 through 2 (of 2 total)

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