DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT DISTINCT vwea.FranchiseNumber,
    vwea.CustomerID,
    vwea.EmailBefore,
    vwea.EmailAfter,
    vwea.ChangeDate, 
    j.ScheduledEndDate AS MoveDate, 
    CASE
       WHEN j.ScheduledEndDate < vwea.ChangeDate THEN 'After Move Change'
       ELSE 'Before move change'
    END AS Change
FROM dbo.tblJob j
   INNER JOIN dbo.tblWorkOrder wo ON j.DatabaseInfoID = wo.DatabaseInfoID
      AND j.WorkOrderID = wo.WorkOrderID
   INNER JOIN dbo.EmailAudit vwea ON wo.CustomerID = vwea.CustomerID
   INNER JOIN dbo.tblDatabaseInfo ON j.DatabaseInfoID = dbo.tblDatabaseInfo.DatabaseInfoID
   INNER JOIN dbo.tblFranchise ON j.DatabaseInfoID = dbo.tblFranchise.DatabaseInfoID
      AND vwea.FranchiseNumber = dbo.tblFranchise.FranchiseNumber
WHERE (vwea.FranchiseNumber = 253)
   AND j.ScheduledEndDate < vwea.ChangeDate
ORDER BY vwea.ChangeDate DESC;

SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO

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

-- 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

-- Two indexes created --
CREATE NONCLUSTERED INDEX [IX_tblCustomer_CustomerId_EmailAddress] ON [dbo].[tblCustomer]
(
	[CustomerID] ASC,
	[EmailAddress] ASC
);


CREATE NONCLUSTERED INDEX [IX_tboWorkOrder_CustomerID_DatabaseInfoId] ON [dbo].[tblWorkOrder]
(
	[CustomerID] ASC,
	[DatabaseInfoId] ASC
);

• http://www.mssqltips.com/sqlservertip/1856/sql-server-query-execution-plans-in-sql-server-management-studio/


• http://www.mssqltips.com/sqlservertip/1873/how-to-read-sql-server-graphical-query-execution-plans/

• http://www.sqlservercentral.com/articles/books/65831/

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
	and (
		c.EmailAddress <> a.EmailAddress 
		OR (c.EmailAddress IS NULL AND a.EmailAddress IS NOT NULL)
		OR (c.EmailAddress IS NOT NULL AND a.EmailAddress IS NULL)
		)   
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
	
LEFT JOIN dbo.tblDatabaseInfo di 
	ON di.DatabaseInfoID = a.DatabaseInfoID 
	AND di.FranchiseNumber = 253
	
LEFT JOIN dbo.tblFranchise f 
	ON (f.FranUniqueID = a.FranUniqueID OR f.DatabaseInfoID = a.DatabaseInfoID)
	--a.DatabaseInfoID = f.DatabaseInfoID
	AND f.FranchiseNumber = 253	
	--AND ISNULL(f.FranUniqueID, f.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
WHERE 1 = 1
	--(COALESCE(f.FranchiseNumber, di.FranchiseNumber) = 253)
   AND j.ScheduledEndDate < a.ChangeDate
   --AND ISNULL(a.EmailAddress,'') <> ISNULL(c.EmailAddress,'')
ORDER BY a.ChangeDate DESC;