1

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) 

And:

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

v_OppProjectInvoice:

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
  • I'm guessing locking caused the problem, not the query itself. Maybe try running both queries with SET STATISTICS IO ON and SET STATISTICS TIME ON. – BuahahaXD Apr 15 '15 at 12:14
  • 1
    A Key Lookup is performed because the index you have added does not cover all the columns returned in the query. SQL Server seeks the matching records against the non-clustered index, then has to 'lookup' the other columns it needs to return from the Clustered Index. If you're on SQL Server 2005 or later, you should INCLUDE the extra columns in your index msdn.microsoft.com/en-gb/library/ms190806.aspx – Mark Sinkinson Apr 15 '15 at 12:25
  • 1
    In the first query, the optimizer probably estimated enough rows to be returned to perform a scan against the clustered index. Since the clustered index is essentially the table itself, it had all the data it needed, no additional operator was needed. The second query is more restrictive so the optimizer probably found an index that allows it to perform an index seek. Like Mark said, the index lacked the data at the leaf level so it had to perform a key lookup on your clustered index to retrieve the columns in your SELECT clause. – Queue Mann Apr 15 '15 at 12:30
  • Make sure stats are up-to-date. It may be that the number of rows is underestimated for the second query so SQL Server chose a seek instead of a scan, which is less efficient for a large number of rows. – Dan Guzman Apr 15 '15 at 12:35
  • Thanks all for the swift responses. I've now included all of the columns in the date table that are pulled back via the view into the index, however I'm seeing the same behaviour. I've also updated statistics on the date table. Using the first suggestion fro BuahahaXD, I can see that many of the tables in the view are being accessed hundreds more times in the second query than the first query. I'm still at a loss as to how to fix this, as due to business needs I cannot modify the other tables nor their indexes, but thanks for sharing your knowledge on this one as I've still learned alot! – TehJake Apr 15 '15 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.