October 4, 2014 at 10:08 am
I currently going through one of our stored procedures. It has many updates. What I have done is commented out most of the query. I am then working my way down the code uncommenting each update and seeing what the run time and execution is for each. I am 3/4 of the way through and I got stumped on one of the updates. My query jumped from 30-40 seconds total run time to 8+ minutes. When I run the same basic update query by itself in another window it has a different execution plan and it runs in 1 second. I am so confused. Any help is appreciated.
The Update
UPDATE r
SET PrenatalPatient = 1
FROM #Results r
INNER JOIN PatientVisit pv ON r.PatientProfileID = pv.PatientProfileId
INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
INNER JOIN cusUDS6Detail_2014 ud ON Pvd.ICD9Code LIKE (ud.DiagnosisPrefix + '%')
WHERE (ud.LineNumber ='B') AND pv.Visit BETWEEN @vFromDate AND (DATEADD(DAY,1,@vToDate))
OR (r.Trimester > 0)
Query by itself in another window
SELECT pv.PatientProfileId
FROM
PatientVisit pv
INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
INNER JOIN cusUDS6Detail_2014 ud ON Pvd.ICD9Code LIKE (ud.DiagnosisPrefix + '%')
WHERE (ud.LineNumber ='B') AND pv.Visit BETWEEN @vFromDate AND (DATEADD(DAY,1,@vToDate))
***SQL born on date Spring 2013:-)
October 4, 2014 at 10:16 am
Oddly when I remove the OR (r.Trimester >0) it runs in 50-55 seconds not the 8+ minutes.. Now very, very confused.
***SQL born on date Spring 2013:-)
October 4, 2014 at 11:36 am
thomashohner (10/4/2014)
Oddly when I remove the OR (r.Trimester >0) it runs in 50-55 seconds not the 8+ minutes.. Now very, very confused.
Quick thought, by removing the alternative predicate from the filter, the number of rows passed to the nested loop are reduced significantly, with the predicate its evaluating 182100333 rows, without it the number will be lowered to few millions.
This indicates that it might be better to split this into two separate statements rather than having the combination of the two in one statement.
😎
October 4, 2014 at 12:00 pm
I just did that and I am currently running a 50-55 second query vs 8+ minutes. I just don't understand why such a small thing made such a huge difference. I really do need to start learning the execution plans better.
Thanks for your help Eric!
***SQL born on date Spring 2013:-)
October 4, 2014 at 2:37 pm
thomashohner (10/4/2014)
I just did that and I am currently running a 50-55 second query vs 8+ minutes. I just don't understand why such a small thing made such a huge difference. I really do need to start learning the execution plans better.Thanks for your help Eric!
No worries Thomas, my pleasure;-)
In (very) simple terms, what's happening in the query is that there is a set A which is evaluated against two sets, B1 and B2. In this case the server uses a nested loop for the matching, passing in every row from A and for each of those rows, evaluates it against the produce of B1 and B2. This is in order of few magnitudes more costly than evaluating A-B1 and A-B2 separately.
Hope this makes sense.
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply