Search code examples
sqlsql-serversql-server-2008-r2indexed-view

WHERE Clause Performance While Querying an Indexed View

I created an indexed view (to save time on a very time-consuming aggregation) using a code like the following (simplified):

CREATE VIEW vCosts WITH SCHEMABINDING AS
SELECT   ProjectID
         ,YEAR(Date) AS Year
         ,SUM(Cost) AS YearlyCost
FROM     dbo.DailyAssignments
GROUP BY ProjectID
         ,YEAR(Date)

CREATE UNIQUE CLUSTERED INDEX IX_vCosts ON vCosts (Year, ProjectID)

Doing a SELECT * on this view takes a second. But the following query takes 30 seconds (and exponentially worse if more years are included):

SELECT *
FROM   vCosts
WHERE  Year = 2001

The execution plan indicates that it actually uses the underlying table rather than the view (to be more precise, it seems to be using the clustered primary key of the DailyAssignments table rather than the index of the view). SELECT * on the view uses the index as expected.

And I don't have the same problem with the other field. The following also uses the index of the view and finishes in less than a second:

SELECT *
FROM   vCosts
WHERE  ProjectID = 1

Could anyone help me understand what's happening?

Solution

  • Try to add WITH (NOEXPAND) after view. I had this problem too.

    SELECT *
FROM   vCosts WITH (NOEXPAND)
WHERE  ProjectID = 1

    When NOEXPAND is specified for a view, the query optimizer considers using any indexes defined on the view. NOEXPAND specified with the optional INDEX() clause forces the query optimizer to use the specified indexes. NOEXPAND can be specified only for an indexed view and cannot be specified for a view not indexed.

    Source here http://technet.microsoft.com/en-us/library/ms181151(v=sql.105).aspx