April 21, 2011 at 4:37 pm
I am basically a .NET developer and trying to troubleshoot the performance of a query
I am noticing some database timeouts in my .NET application and on further analysis(of Actual execution plan) it seems the update statement in the stored procedure has Table Scans on a table with 33 million records.......So i created a non clustered index on the table which changed the table Scans to index seek
But now how do i know whether the performance changed significantly....what are the factors i need to consider???
I checked the total Execution time in Client statistics, it looked almost similar
April 21, 2011 at 7:27 pm
Here is the query i was working on
UPDATE p
SET p.CPEReleaseID = @CPEReleaseID,
p.DateUpdated = getdate()
FROM Cln_ServiceInstPayer p WITH (NOLOCK)
INNER JOIN Cln_ServiceInstance i WITH (NOLOCK) ON p.ServiceInstKey = i.ServiceInstKey
INNER JOIN is_CPERelease cpe WITH (NOLOCK) ON cpe.CPEThresholdID = p.CPEThresholdID
WHERE p.CPEThresholdID = @CPEThresholdID
AND p.PayerID = 201
AND p.DeleteStatus = 0
AND cpe.CPEReleaseID = @CPEReleaseID
AND i.ServiceDateBegin BETWEEN @ReleaseStartDate AND @ReleaseEndDate
AND i.ServiceDateEnd BETWEEN @ReleaseStartDate AND @ReleaseEndDate
AND i.Status = @StatusID
AND i.VoidStatus IS NULL
AND i.ServiceInstType IN (40261,40262)
AND i.SubmitDate <= CASE WHEN cpe.ReleaseAtExecuteFlag = 0 THEN cpe.AppliedDate
WHEN cpe.ReleaseAtExecuteFlag = 1 THEN GETDATE() END
Also please find the attachment for the execution plan
I created the non clustered index on InstPayer table with columns DeleteStatus , PayerID, CPEThresholdID, ServiceInstKey
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply