July 3, 2013 at 1:06 pm
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
July 3, 2013 at 1:44 pm
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
July 3, 2013 at 7:29 pm
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 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