I did an update of the statistics on all of the related tables. That didn't change the execution time.
The view ([font="Courier New"]EmailAudit[/font]) duplicates many of the tables in the original query. Just running the view takes about 25 seconds.
The view definition is:
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
--CREATE VIEW [dbo].[EmailAudit]
--AS
SELECT COALESCE(f.FranchiseNumber, di.FranchiseNumber) AS FranchiseNumber,
a.[CustomerID],
a.[EmailAddress] AS [EmailBefore],
c.EmailAddress AS [EmailAfter],
a.[ChangeDate]
FROM [dbo].[tblEmailAudit] a
INNER JOIN dbo.tblCustomer c ON C.DatabaseInfoID = a.DatabaseInfoID
AND c.CustomerID = a.CustomerID
AND ISNULL(c.FranUniqueID, c.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
LEFT JOIN dbo.tblFranchise f ON a.DatabaseInfoID = f.DatabaseInfoID
AND ISNULL(f.FranUniqueID, f.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
LEFT JOIN dbo.tblDatabaseInfo di ON a.DatabaseInfoID = di.DatabaseInfoID
WHERE ISNULL(a.EmailAddress,'') <> ISNULL(c.EmailAddress,'');
SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO
So I rewrote the original query removing the view and I'm still getting about the same time (40-45 seconds).
-- original query rewritten to remove EmailAudit view --
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT DISTINCT COALESCE(f.FranchiseNumber, di.FranchiseNumber) AS FranchiseNumber,
a.CustomerID,
a.EmailAddress AS EmailBefore,
c.EmailAddress AS EmailAfter,
a.ChangeDate,
j.ScheduledEndDate AS MoveDate,
CASE
WHEN j.ScheduledEndDate < a.ChangeDate THEN 'After Move Change'
ELSE 'Before move change'
END AS Change
FROM dbo.tblEmailAudit a
INNER JOIN dbo.tblCustomer c ON c.CustomerID = a.CustomerID
AND c.DatabaseInfoID = a.DatabaseInfoID
INNER JOIN dbo.tblWorkOrder wo ON c.CustomerId = wo.CustomerId
AND wo.DatabaseInfoId = a.DatabaseInfoId
INNER JOIN dbo.tblJob j ON j.WorkOrderID = wo.WorkOrderID
AND j.DatabaseInfoID = a.DatabaseInfoID
INNER JOIN dbo.tblDatabaseInfo di ON di.DatabaseInfoID = a.DatabaseInfoID
LEFT JOIN dbo.tblFranchise f ON a.DatabaseInfoID = f.DatabaseInfoID
AND ISNULL(f.FranUniqueID, f.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
WHERE (COALESCE(f.FranchiseNumber, di.FranchiseNumber) = 253)
AND j.ScheduledEndDate < a.ChangeDate
AND ISNULL(a.EmailAddress,'') <> ISNULL(c.EmailAddress,'')
ORDER BY a.ChangeDate DESC;
SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO
The two biggest costs in the query plan are both index seeks (one clustered and the other non-clustered). The third largest cost is an index scan on dbo.tblJob.