September 29, 2008 at 10:35 am
If I run the stored procedure below, it takes much longer to run than if I run the query by itself with the exact same values as parameters used in the call to the stored procedure.
If I run just the query, I get 5090 reads and duration of 133ms.
If I run the SP with the same values, I get 11380 reads and duration of 1975ms.
The execution plan for the SP looks entirely different than the one for just the query.
I can't understand why. I have a demo in < 1 hour and this part runs so slow.
Any ideas? These are the indexes on ptLabResults:
(please dont' chastise me about these indexes, I didn't design this, I just have to live with it and try to improve it)
name type columns
-------------------------------- --------------- -----------------------
PK_ptLabResults_LabResultID CLUSTERED LabResultID (int)
IX_ptLabResults NONCLUSTERED EMRApproved (int)
IX_ptLabResults_ApprovedBy NONCLUSTERED Approved (varchar(255))
IX_ptLabResults_MRN NONCLUSTERED MRN (varchar(25))
IX_ptLabResults_SampleID NONCLUSTERED SampleID (varchar(255))
IX_ptLabResults_Test NONCLUSTERED Test (varchar(255))
IX_ptLabResults_LabResultID NONCLUSTERED LabResultID (int)
IX_ptLabResults_UniqueRecord NONCLUSTERED MRN (varchar(25)), SampleID (varchar(255)),
Test (varchar(255)), DateOfTest (datetime),
ObsID (varchar(255))
IX_ptLabResults_rrSampleID NONCLUSTERED rrSampleID (int)
IX_ptLabResults_ptLabTestsID NONCLUSTERED ptlabTestID (int)
IX_ptLabResults_UrgentFlg NONCLUSTERED UrgentFlg (int)
IX_ptLabResults_ObsResultStatus NONCLUSTERED ObsResultStatus (varchar(255))
IX_ptLabResults_PID NONCLUSTERED PID (varchar(25))
IX_ptLabResults_MRN_ObsID NONCLUSTERED MRN (varchar(25)), , ObsID (varchar(255))
Here's the SP:
CREATE PROCEDURE [dbo].[rr_Collect_ptLabResults_byObservation]
@MRN as nvarchar(25),
@Observation as nvarchar(255)
AS
BEGIN
SELECT lr.MRN,
lr.PID,
lr.PName,
lr.SampleID,
lr.Test,
lr.DateofTest,
u.UserName AS Approvedby,
lr.ApprovedDate,
lr.Performedat,
lro.Observation as ObsID,
lr.ObsValue,
lr.Units,
lr.AbnormalFlags,
lr.ObsResultStatus,
lr.ProducerID,
lr.OrderedBy,
lr.Comment,
lr.TestComment,
lr.EditedBy,
lr.EditedDate,
lr.ReTested,
lr.LabResultID,
d.DateOfBirth,
d.FName,
d.LName,
d.MI,
lr.ObsDateTime,
lr.ObsSubID,
lr.PSSN,
lr.ReferenceRange,
lr.Description,
lr.ValueType,
o.WarnHigh,
o.WarnLow,
o.AlertHigh,
o.AlertLow,
ro.SortOrder,
lr.UrgentFlg
FROM ptLabResults AS lr
INNER JOIN xrLabResultObservations AS lro ON lr.ObsID = lro.ObsID
LEFT OUTER JOIN ptDemographics AS d ON d.MRN = lr.MRN
LEFT OUTER JOIN coLabTests AS t ON t.Test = lr.Test
LEFT OUTER JOIN coLabTestObservations AS o ON t.ID = o.TestID AND lr.ObsID = o.ObservationName
LEFT OUTER JOIN coLabResultOrder AS ro ON ro.Test = t.Test AND ro.ObsID = lr.ObsID
LEFT OUTER JOIN ptUsers AS u ON u.UniqueID = lr.Approvedby
WHERE (lr.MRN = @MRN)
AND (lro.Observation = @Observation)
END
Here's the query:
SELECT lr.MRN,
lr.PID,
lr.PName,
lr.SampleID,
lr.Test,
lr.DateofTest,
u.UserName AS Approvedby,
lr.ApprovedDate,
lr.Performedat,
lro.Observation as ObsID,
lr.ObsValue,
lr.Units,
lr.AbnormalFlags,
lr.ObsResultStatus,
lr.ProducerID,
lr.OrderedBy,
lr.Comment,
lr.TestComment,
lr.EditedBy,
lr.EditedDate,
lr.ReTested,
lr.LabResultID,
d.DateOfBirth,
d.FName,
d.LName,
d.MI,
lr.ObsDateTime,
lr.ObsSubID,
lr.PSSN,
lr.ReferenceRange,
lr.Description,
lr.ValueType,
o.WarnHigh,
o.WarnLow,
o.AlertHigh,
o.AlertLow,
ro.SortOrder,
lr.UrgentFlg
FROM ptLabResults AS lr
INNER JOIN xrLabResultObservations AS lro ON lr.ObsID = lro.ObsID
LEFT OUTER JOIN ptDemographics AS d ON d.MRN = lr.MRN
LEFT OUTER JOIN coLabTests AS t ON t.Test = lr.Test
LEFT OUTER JOIN coLabTestObservations AS o ON t.ID = o.TestID AND lr.ObsID = o.ObservationName
LEFT OUTER JOIN coLabResultOrder AS ro ON ro.Test = t.Test AND ro.ObsID = lr.ObsID
LEFT OUTER JOIN ptUsers AS u ON u.UniqueID = lr.Approvedby
WHERE (lr.MRN = '10001')
AND (lro.Observation = 'HCT')
SELECT lr.MRN,
lr.PID,
lr.PName,
lr.SampleID,
lr.Test,
lr.DateofTest,
u.UserName AS Approvedby,
lr.ApprovedDate,
lr.Performedat,
lro.Observation as ObsID,
lr.ObsValue,
lr.Units,
lr.AbnormalFlags,
lr.ObsResultStatus,
lr.ProducerID,
lr.OrderedBy,
lr.Comment,
lr.TestComment,
lr.EditedBy,
lr.EditedDate,
lr.ReTested,
lr.LabResultID,
d.DateOfBirth,
d.FName,
d.LName,
d.MI,
lr.ObsDateTime,
lr.ObsSubID,
lr.PSSN,
lr.ReferenceRange,
lr.Description,
lr.ValueType,
o.WarnHigh,
o.WarnLow,
o.AlertHigh,
o.AlertLow,
ro.SortOrder,
lr.UrgentFlg
FROM ptLabResults AS lr
INNER JOIN xrLabResultObservations AS lro ON lr.ObsID = lro.ObsID
LEFT OUTER JOIN ptDemographics AS d ON d.MRN = lr.MRN
LEFT OUTER JOIN coLabTests AS t ON t.Test = lr.Test
LEFT OUTER JOIN coLabTestObservations AS o ON t.ID = o.TestID AND lr.ObsID = o.ObservationName
LEFT OUTER JOIN coLabResultOrder AS ro ON ro.Test = t.Test AND ro.ObsID = lr.ObsID
LEFT OUTER JOIN ptUsers AS u ON u.UniqueID = lr.Approvedby
WHERE (lr.MRN = '10001')
AND (lro.Observation = 'HGB')
SELECT lr.MRN,
lr.PID,
lr.PName,
lr.SampleID,
lr.Test,
lr.DateofTest,
u.UserName AS Approvedby,
lr.ApprovedDate,
lr.Performedat,
lro.Observation as ObsID,
lr.ObsValue,
lr.Units,
lr.AbnormalFlags,
lr.ObsResultStatus,
lr.ProducerID,
lr.OrderedBy,
lr.Comment,
lr.TestComment,
lr.EditedBy,
lr.EditedDate,
lr.ReTested,
lr.LabResultID,
d.DateOfBirth,
d.FName,
d.LName,
d.MI,
lr.ObsDateTime,
lr.ObsSubID,
lr.PSSN,
lr.ReferenceRange,
lr.Description,
lr.ValueType,
o.WarnHigh,
o.WarnLow,
o.AlertHigh,
o.AlertLow,
ro.SortOrder,
lr.UrgentFlg
FROM ptLabResults AS lr
INNER JOIN xrLabResultObservations AS lro ON lr.ObsID = lro.ObsID
LEFT OUTER JOIN ptDemographics AS d ON d.MRN = lr.MRN
LEFT OUTER JOIN coLabTests AS t ON t.Test = lr.Test
LEFT OUTER JOIN coLabTestObservations AS o ON t.ID = o.TestID AND lr.ObsID = o.ObservationName
LEFT OUTER JOIN coLabResultOrder AS ro ON ro.Test = t.Test AND ro.ObsID = lr.ObsID
LEFT OUTER JOIN ptUsers AS u ON u.UniqueID = lr.Approvedby
WHERE (lr.MRN = '5079')
AND (lro.Observation = 'HCT')
September 29, 2008 at 11:09 am
It sounds like parameter sniffing. Try adding WITH RECOMPILE to the procedure and then run it again. That won't help tune it, but it will prevent you getting a bad plan that hangs around.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 29, 2008 at 12:25 pm
A co-worker recently ran into a similar issue with parameter sniffing. The solution he came up with was to copy his input parameters into local variables inside the procedure and use those in the WHERE clause rather than to use the parameters that were passed in to the procedure.
This blog post from Glen Berry details a solution:
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!541.entry?wa=wsignin1.0
Jeremiah Peschka
Microsoft SQL Server MVP
Managing Director - Brent Ozar PLF, LLC
September 29, 2008 at 1:06 pm
That didn't help, in fact, it made things worse as it took 12 seconds to load .vs 6, before I
added the local variables, on a particular MRN/Observation set, i.e.
exec rr_Collect_ptLabResults_byObservation @MRN=N'10001',@Observation=N'HCT'
GO
exec rr_Collect_ptLabResults_byObservation @MRN=N'10001',@Observation=N'HGB'
GO
exec rr_Collect_ptLabResults_byObservation @MRN=N'10001',@Observation=N'Creatinine'
GO
September 29, 2008 at 1:09 pm
The proc doesn't look so complicated that a RECOMPILE would take six seconds... Hmmm.... You may have to go to old-fashioned tuning. What do the execution plans look like?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 29, 2008 at 1:14 pm
I just can't understand why the execution plan is so different and the difference between calling as a query .vs calling as a SP is so great.
September 29, 2008 at 2:29 pm
The execution plans are located here:
hold on, there seems to be a problem with my zip file.
Ok, it works now, I forgot to upload using binary mode.
September 30, 2008 at 1:02 am
I've noticed that column MRN is defined as varchar, while parameter @MRN is defined as nvarchar. In the procedure execution plan, you can see CONVERT IMPLICIT in the first step. Make sure that parameter is of precisely the same datatype as the column to which it refers. I'm not sure whether it will help in this particular case, but it might - sometimes avoiding conversion means a lot for performance. (Actually, I think it should help, because as it is now, you're converting the entire column to nvarchar before doing anything else.)
September 30, 2008 at 5:36 am
With these estimated plans, there's almost no difference. Some of the estimated costs are slightly different and the location of the table scan of 'RO' changes between the two, but that's about it. What do the actual execution plans look like? Do they differ at all, substantially, from these estimated plans? Also, do the row counts vary wildly. For example, the key lookup against pk_ptlabresults_labresultsid looks to be estimated as only one row. Is it more in the actual query? If there's a significant difference between the plans or between the rows, you might need to update the statistics on the indexes, possibly with a full scan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 30, 2008 at 6:59 am
Vladan (9/30/2008)
I've noticed that column MRN is defined as varchar, while parameter @MRN is defined as nvarchar. In the procedure execution plan, you can see CONVERT IMPLICIT in the first step. Make sure that parameter is of precisely the same datatype as the column to which it refers. I'm not sure whether it will help in this particular case, but it might - sometimes avoiding conversion means a lot for performance. (Actually, I think it should help, because as it is now, you're converting the entire column to nvarchar before doing anything else.)
This seems to have done the trick. It all stems from a design flaw from when this system
was first written. They had MRN defined serveral different ways, char(9), char(10),
varchar(25), nvarchar(25), varchar(50), nvarchar(50), and even varchar(100).
I wrote the script to modify the MRN column in all the tables to varchar(25) but not all
the SPs were changed, looks like I have a little more work to do. We have no need for
unicode so they will all be changed to varchar(25).
I was wondering if that was the problem when I saw the CONVERT IMPLICIT but wasn't
sure. Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply