This seems to be taking over 5 hours

RichardBrianSmith 2015-07-30 10:50:34

This seems to be taking over 5 hours – can it be optimized?

I've added the missing index:

CREATE NONCLUSTERED INDEX [] 
ON [dbo].[tblSystemAuditTrail] 
    ([AuditTrailID],[AuditTrailTypeID]) 
INCLUDE 
    ([UserID],[ApplicationID],[EntityID],[EntityRefID],[ActionDate],[CommentID])

Query:

SET NOCOUNT ON
 
DECLARE @MaxID int
 
SELECT @MaxID = MaxID
FROM tblTableRefreshMaxID
WHERE TableName = 'tblSystemAuditTrail'
 
--INSERT tblAuditUpdates (EntityID, EntityRefID, ActionDate, UserID, ApplicationID, FieldID, OldValue, NewValue, Comment)
SELECT a.EntityID, a.EntityRefID, a.ActionDate, a.UserID, a.ApplicationID, d.FieldID, d.OldValue, d.NewValue, c.Comment
FROM ESS.dbo.tblSystemAuditTrail a (nolock)
    INNER JOIN ESS.dbo.tblSystemAuditTrailDetail d  (nolock)
        ON a.AuditTrailID = d.AuditTrailID
    LEFT OUTER JOIN ESS.dbo.tblDataComment c    
        ON a.CommentID = c.CommentID
WHERE a.AuditTrailID > @MaxID
AND a.AuditTrailTypeID IN (3, 6)
 
--UPDATE tblTableRefreshMaxID
--SET MaxID = (SELECT MAX(AuditTrailID) FROM ESS.dbo.tblSystemAuditTrail)
--WHERE TableName = 'tblSystemAuditTrail'
/storage/attachments/1317-tmpCF68_Anonymized.queryanalysis
SQLkiwi 2015-08-01 08:40:46
The main problem is the sort (required for a merge join):

Sort and Merge Join

A very likely better plan would use nested loops. The index needed to support that is very close to the suggested missing index:

CREATE UNIQUE INDEX i 
ON dbo.tblSystemAuditTrail
    (AuditTrailTypeID, AuditTrailID)
INCLUDE
    (EntityID, EntityRefID, ActionDate, UserID, ApplicationID, CommentID);

That reverses the key order, so the equality comparison comes before the inequality.

On my simulation of your tables, that produces the following plan:

Estimated plan

You may need to use an OPTION (LOOP JOIN) hint to get this plan, depending on statistics, and your settings for server max memory & max degree of parallelism.

You should also consider updating your SQL Server to at least Service Pack 3 from the current SP1.

July 30, 2015
No Comments