Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can I avoid using cursors Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 1:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 13, 2013 2:04 PM
Points: 5, Visits: 73
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

Post #1470203
Posted Wednesday, July 3, 2013 1:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1470210
Posted Wednesday, July 3, 2013 7:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:07 PM
Points: 3,617, Visits: 5,236
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1470281
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse