February 9, 2017 at 8:31 am
Hello,
We have one query which is running frequently through out the day. I have attached SP and execution plan for your consideration.
There are few indexes which are doing index scan, so i tried updating indexes but somehow it still doing the index scan. I will add one missing index on one of the table. Kindly, please guide me, how can i improve the overall performance of the query?
CREATE Procedure [PatientsForUser_test]
@UserId AS UNIQUEIDENTIFIER,
@UserName AS NVARCHAR(256),
@PatientName AS NVARCHAR(64) = NULL,
@DOB AS DATETIME = NULL,
@LabTestNumber AS VARCHAR(8) = NULL,
@RangeCode AS INT
AS
BEGIN
DECLARE @StartDate DATETIME
IF @RangeCode < 0 -- Select records from last @RangeCode months
BEGIN
SELECT @StartDate = DATEADD (MONTH, @RangeCode, GETDATE())
SELECT RQ.RequisitionID,
RQ.CollectionDateTime AS ServiceDate,
RQ.ReceivedDateTime,
RQ.PatientName,
RQ.DOB,
OT.TestID,
OT.LabTestNumber,
OT.[Description],
OT.Result,
OE.NormalRangeLow,
OE.NormalRangeHigh
FROM Orders.Requisitions AS RQ WITH(NOLOCK),
Orders.Tests AS OT WITH(NOLOCK),
Orders.ExplanatoryData AS OE WITH(NOLOCK),
Accounts.RequisitionAccountAccess AS RAA WITH(NOLOCK),
[Admin].AllowedAccountUserAccess AS AAUA WITH(NOLOCK),
dbo.aspnet_Users AS U WITH(NOLOCK)
WHERE RQ.RequisitionID = OT.RequisitionID
AND OE.TestID = OT.TestID
AND OE.SetID = 1
AND RQ.RequisitionID = RAA.RequisitionID
AND AAUA.AccountID = RAA.AccountID
AND (RAA.AddressID = AAUA.AddressID or AAUA.AddressID = 0)
AND AAUA.UserID = U.UserID
AND U.UserName = ISNULL(@UserName, U.UserName)
AND AAUA.UserID = ISNULL(@UserId, AAUA.UserID)
AND RQ.ReceivedDateTime >= @StartDate
AND RQ.PatientName = ISNULL(@PatientName, RQ.PatientName)
AND RQ.DOB = ISNULL(@DOB, RQ.DOB)
AND OT.LabTestNumber = ISNULL(@LabTestNumber,OT.LabTestNumber)
AND OT.LabSubTestNumber IS NULL
ORDER BY RQ.CollectionDateTime ASC
END
ELSE IF @RangeCode = 0
BEGIN
SELECT RQ.RequisitionID,
RQ.CollectionDateTime AS ServiceDate,
RQ.ReceivedDateTime,
RQ.PatientName,
RQ.DOB,
OT.TestID,
OT.LabTestNumber,
OT.[Description],
OT.Result,
OE.NormalRangeLow,
OE.NormalRangeHigh
FROM Orders.Requisitions AS RQ,
Orders.Tests AS OT,
Orders.ExplanatoryData AS OE,
Accounts.RequisitionAccountAccess AS RAA,
[Admin].AllowedAccountUserAccess AS AAUA,
dbo.aspnet_Users AS U
WHERE RQ.RequisitionID = OT.RequisitionID
AND OE.TestID = OT.TestID
AND OE.SetID = 1
AND RQ.RequisitionID = RAA.RequisitionID
AND AAUA.AccountID = RAA.AccountID
AND (RAA.AddressID = AAUA.AddressID or AAUA.AddressID = 0)
AND AAUA.UserID = U.UserID
AND U.UserName = ISNULL(@UserName, U.UserName)
AND AAUA.UserID = ISNULL(@UserId, AAUA.UserID)
AND RQ.PatientName = ISNULL(@PatientName, RQ.PatientName)
AND RQ.DOB = ISNULL(@DOB, RQ.DOB)
AND OT.LabTestNumber = ISNULL(@LabTestNumber,OT.LabTestNumber)
AND OT.LabSubTestNumber IS NULL
ORDER BY RQ.CollectionDateTime ASC
END
ELSE -- @RangeCode > 0
BEGIN
SELECT Top (@RangeCode)
RQ.RequisitionID,
RQ.CollectionDateTime AS ServiceDate,
RQ.ReceivedDateTime,
RQ.PatientName,
RQ.DOB,
OT.TestID,
OT.LabTestNumber,
OT.[Description],
OT.Result,
OE.NormalRangeLow,
OE.NormalRangeHigh
FROM Orders.Requisitions AS RQ,
Orders.Tests AS OT,
Orders.ExplanatoryData AS OE,
Accounts.RequisitionAccountAccess AS RAA,
[Admin].AllowedAccountUserAccess AS AAUA,
dbo.aspnet_Users AS U
WHERE RQ.RequisitionID = OT.RequisitionID
AND OE.TestID = OT.TestID
AND OE.SetID = 1
AND RQ.RequisitionID = RAA.RequisitionID
AND AAUA.AccountID = RAA.AccountID
AND (RAA.AddressID = AAUA.AddressID or AAUA.AddressID = 0)
AND AAUA.UserID = U.UserID
AND U.UserName = ISNULL(@UserName, U.UserName)
AND AAUA.UserID = ISNULL(@UserId, AAUA.UserID)
AND RQ.PatientName = ISNULL(@PatientName, RQ.PatientName)
AND RQ.DOB = ISNULL(@DOB, RQ.DOB)
AND OT.LabTestNumber = ISNULL(@LabTestNumber,OT.LabTestNumber)
AND OT.LabSubTestNumber IS NULL
ORDER BY RQ.CollectionDateTime DESC
END
END
February 9, 2017 at 8:38 am
The estimates are way out - when were statistics last updated?
Also
@UserName AS NVARCHAR(256),
@PatientName AS NVARCHAR(64) = NULL,
Both of these should be VARCHAR. Implicit conversions between the column values and the variables will cause table scans.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 9, 2017 at 8:43 am
ChrisM@Work - Thursday, February 9, 2017 8:38 AMThe estimates are way out - when were statistics last updated?
Yes, I noticed that. I would recommend updating them with a full scan. The other thing is this: is the ORDER BY clause really necessary? If you could have whatever is consuming the data do the sort, that could halve the resources used to execute the query.
John
February 9, 2017 at 8:49 am
John Mitchell-245523 - Thursday, February 9, 2017 8:43 AMChrisM@Work - Thursday, February 9, 2017 8:38 AMThe estimates are way out - when were statistics last updated?Yes, I noticed that. I would recommend updating them with a full scan. The other thing is this: is the ORDER BY clause really necessary? If you could have whatever is consuming the data do the sort, that could halve the resources used to execute the query.
John
It could be attributable to this also, John:
AND U.UserName = ISNULL(@UserName, U.UserName)
AND AAUA.UserID = ISNULL(@userid, AAUA.UserID)
AND RQ.PatientName = ISNULL(@PatientName, RQ.PatientName)
AND OT.LabTestNumber = ISNULL(@LabTestNumber,OT.LabTestNumber)
One username or all of them;
One userid or all of them
etc
It's highly unlikely that one plan will fit all of these scenarios.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 9, 2017 at 8:58 am
Ah yes, our old friend the catch-all query. Not easy to code your way around when you have so many optional parameters. I think the ORDER BY is the lowest-hanging fruit here.
John
February 9, 2017 at 9:05 am
Thanks all for your quick response.
Statistics are updating every day. I can work on changing data type from Nvarchar to Varchar. Is there any alternative for this code/
AND U.UserName = ISNULL(@UserName, U.UserName)
AND AAUA.UserID = ISNULL(@UserId, AAUA.UserID)
AND RQ.PatientName = ISNULL(@PatientName, RQ.PatientName)
AND OT.LabTestNumber = ISNULL(@LabTestNumber,OT.LabTestNumber)
February 9, 2017 at 9:10 am
EasyBoy - Thursday, February 9, 2017 9:05 AMThanks all for your quick response.
Statistics are updating every day. I can work on changing data type from Nvarchar to Varchar. Is there any alternative for this code/
AND U.UserName = ISNULL(@UserName, U.UserName)AND AAUA.UserID = ISNULL(@UserId, AAUA.UserID)
AND RQ.PatientName = ISNULL(@PatientName, RQ.PatientName)
AND OT.LabTestNumber = ISNULL(@LabTestNumber,OT.LabTestNumber)
Yes - the query hint OPTION(RECOMPILE)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 9, 2017 at 1:40 pm
In this case dynamic SQL would perform better as it complies on the fly. You could have a master procedure that calls different procedures based on the number of set parameters passed.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply