I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending):
SELECT ColA FROM MyView a WHERE a.[Date] >= DATEADD(YEAR, -1,CAST(GETDATE() as date)
SELECT ColA FROM MyView a WHERE a.[Date] >= DATEADD(YEAR, -1, CAST(GETDATE() as date)) AND a.[Date] <= CAST(GETDATE() as date)
The first query runs in two seconds, while the second query takes 2 minutes to execute, yet the execution plan states the first query's cost (55%) is higher than the second query's (45%) relative to the batch, which I imagine must be due to the size of the resultset as opposed to how long it took to calculate.
As the execution plans were extremely convoluted selecting from the view, I rewrote the queries to select just from the table with the date column (I figure since all I'm changing is the criteria on that column, the other work happening in the view is irrelevant). Looking at the resulting execution plans, the first performs a clustered index scan against the Primary Key (Id column, Identity 1, 1) and doesn't even look at the index. The second query performs a non-clustered index seek against the index and a key-lookup against the primary key.
I thought I understood at least the fundamentals of indexing, but the disproportionately long runtime of the second query vs the first challenges my understanding. Could anyone possibly explain the differing execution plans & why they are so different?
EDIT: It's become apparent the other work in the view is not irrelevant. I feel it's far too large a view to ask anyone to look through it, but as Stack requests so Stack shall receive!
It's a view that queries a view, so I'll post the code that creates the view and then the code the creates the underlying view. For the sake of maintaining some brevity, I'm not going to post the details of all of the tables involved, but please ask for any specific details you feel are relevant.
MyView (the view mentioned above):
WITH cteOppWon as ( SELECT opi.[Opp Won Date], opi.Level, DATEDIFF(DAY,opi.[Opp Create Date], opi.[Opp Won Date]) as [OppCreateToWon] FROM v_OppProjectInvoice opi ), cteOppWonProjectCreated as ( SELECT opi1.[Project Create Date], opi1.Level, DATEDIFF(DAY, opi1.[Opp Won Date], opi1.[Opp Create Date]) as [OppWonToProject] FROM v_OppProjectInvoice opi1 ), cteProjCreateToStart as ( SELECT opi2.[Project Start Date], opi2.Level, DATEDIFF(DAY,opi2.[Project Create Date], opi2.[Project Start Date]) as [ProjCreateToStart] FROM v_OppProjectInvoice opi2 ), cteProjStartToFin as ( SELECT opi3.[Project End Date], opi3.Level, DATEDIFF(DAY,opi3.[Project Create Date], opi3.[Project End Date]) as [ProjStartToFin] FROM v_OppProjectInvoice opi3 ), cteProjFinToInv as ( SELECT opi4.[First Invoice Date], opi4.Level, DATEDIFF(DAY,opi4.[Project End Date], opi4.[First Invoice Date]) as [ProjectEndToFirstInvoice] FROM v_OppProjectInvoice opi4 ), cteFirstInvToLastInv as ( SELECT opi5.[Final Date Paid], opi5.Level, DATEDIFF(DAY,opi5.[First Invoice Date], opi5.[Final Date Paid]) as [FirstInvoiceToLastInvoice] FROM v_OppProjectInvoice opi5 ) SELECT cc.CircleYear, -- Included in index cc.CircleDate, -- Indexed cc.CircleWeek, -- Included in index cc.CircleQuarter, -- Included in index cteOppWon.Level, ISNULL(SUM(OppCreateToWon),0) as [Total Days From Opportunity Creation to Opportunity Win], ISNULL(SUM(OppWonToProject),0) as [Total Days From Opportunity Win to Project Creation], ISNULL(SUM(ProjCreateToStart),0) as [Total Days From Project Creation to Project Start], ISNULL(SUM(ProjStartToFin),0) as [Total Days From Project Start to Project End], ISNULL(SUM(ProjectEndToFirstInvoice),0) as [Total Days From Project End to First Invoice], ISNULL(SUM(FirstInvoiceToLastInvoice),0) as [Total Days From First Invoice to Most Recent Invoice], COUNT(*) as NProjects FROM CircleCalendar cc LEFT JOIN cteOppWon ON cc.CircleDate = cteOppWon.[Opp Won Date] LEFT JOIN cteOppWonProjectCreated ON cc.CircleDate = cteOppWonProjectCreated.[Project Create Date] AND cteOppWon.Level = cteOppWonProjectCreated.Level LEFT JOIN cteProjCreateToStart ON cc.CircleDate = cteProjCreateToStart.[Project Start Date] AND cteOppWon.Level = cteProjCreateToStart.Level LEFT JOIN cteProjStartToFin ON cc.CircleDate = cteProjStartToFin.[Project End Date] AND cteOppWon.Level = cteProjStartToFin.Level LEFT JOIN cteProjFinToInv ON cc.CircleDate = cteProjFinToInv.[First Invoice Date] AND cteOppWon.Level = cteProjFinToInv.Level LEFT JOIN cteFirstInvToLastInv ON cc.CircleDate = cteFirstInvToLastInv.[Final Date Paid] AND cteOppWon.Level = cteFirstInvToLastInv.Level GROUP BY cteOppWon.Level, cc.CircleYear, cc.CircleQuarter, cc.CircleDate, cc.CircleWeek
WITH cteOpp as ( SELECT o.Opportunity_RecID -- Int, Indexed FROM cwwebapp_circleit.dbo.v_rpt_Opportunity o WHERE o.Closed_Status = 'Won' ), cteProjOpp as ( SELECT pp.PM_Project_RecID, plvl.Level, CONVERT(date, pp.Date_Entered_UTC) [Project Create Date], CONVERT(date, pp.Date_Start) as [Project Start Date], CONVERT(date,pp.Date_Req) as [Project End Date], o.Opportunity_RecID, CONVERT(date,oppwon.[Opportunity Won Date]) as [Opp Won Date], CONVERT(date, oppcreate.[Opp Created Date]) as [Opp Create Date], CONVERT(date, inv.[First Invoice Date]) as [First Invoice Date], CONVERT(date, inv.[Final Date Paid]) as [Final Date Paid], inv.[Total Amount Paid] FROM cwwebapp_circleit.dbo.PM_Project pp INNER JOIN cteOpp o ON o.Opportunity_RecID = pp.Opportunity_RecID CROSS APPLY (SELECT MIN(oa.last_Updated_utc) as [Opportunity Won Date] FROM cwwebapp_circleit.dbo.SO_Opportunity_Audit oa INNER JOIN cwwebapp_circleit.dbo.Locale_Key lk ON oa.Locale_Key_RecId = lk.Locale_Key_RecId INNER JOIN cwwebapp_circleit.dbo.SO_Opportunity_Audit_Value oav ON oa.SO_Opportunity_Audit_RecId = oav.SO_Opportunity_audit_recid WHERE oa.Opportunity_RecId = o.Opportunity_RecID AND oav.audit_token = 'new_value' AND oav.audit_value IN ('Won', 'Won - Engineer Required', 'Won - Project Level 2', 'Won - Project Level 3', 'Won - Telecoms', 'Opp Won', 'Opp closed as won', ' Won -Deposit Required', 'Won Test') ) as oppwon CROSS APPLY ( SELECT MIN(oa.last_Updated_utc) as [Opp Created Date] FROM cwwebapp_circleit.dbo.SO_Opportunity_Audit oa WHERE oa.Opportunity_RecId = o.Opportunity_RecID ) as oppcreate LEFT JOIN ( SELECT i.PM_Project_RecID, MIN(i.Date_Invoice) As [First Invoice Date], MAX(i.Date_Paid) as [Final Date Paid], SUM(i.Invoice_Amount) as [Total Amount Paid] FROM cwwebapp_circleit.dbo.v_rpt_Invoices i GROUP BY PM_Project_RecID) inv ON pp.PM_Project_RecID = inv.PM_Project_RecID CROSS APPLY ( SELECT gpbl.Type_Description as [Level] FROM cwwebapp_circleit.dbo.udf_GetProjectBoardList() gpbl WHERE gpbl.PM_Project_RecID = pp.PM_Project_RecID) plvl ) SELECT * FROM cteProjOpp