查询索引视图时的WHERE子句性能

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 *需要一秒钟的时间。但是下面的查询需要30秒(如果包含更多年份，情况会更糟):

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.

执行计划表明它实际上使用的是底层表而不是视图(更准确地说，它似乎使用的是DailyAssignments表的主键，而不是视图的索引)。视图上的SELECT *按预期使用索引。

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?

谁能帮我弄明白发生了什么事吗?

1 个解决方案

#1

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

尝试添加后视图(NOEXPAND)。我也有这个问题。

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.

当为视图指定NOEXPAND时，查询优化器将考虑使用视图上定义的任何索引。使用可选的INDEX()子句指定的NOEXPAND命令查询优化器使用指定的索引。NOEXPAND只能为已索引的视图指定，而不能为未被索引的视图指定。

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

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

#1