对于SQL查询性能来说，使用带有索引的实际非规范化表比使用索引视图更好?

In order to improve the performance of a query I have created a denormalized indexed view that contains some of the information I need to report on. When I didn't get the performance gains that I had hoped for I created a table version of my view with indexes and got significantly better performance.

为了提高查询的性能，我创建了一个非规范化的索引视图，其中包含我需要报告的一些信息。当我没有获得预期的性能提升时，我用索引创建了视图的表版本，并获得了明显更好的性能。

I should note that when I create my view there are a lot of ISNULLs in the SELECT. I know that these can hurt performance if these columns were joined on a regular view but I was under the impression that it would be OK if the view was indexed. Could the ISNULLs be the problem?

我应该注意，在创建视图时，SELECT中有很多ISNULLs。我知道，如果这些列在常规视图上连接起来，会影响性能，但我的印象是，如果视图被索引，那就没问题。ISNULLs会是问题吗?

2 个解决方案

#1

Did you index the columns you were actually selecting on? If you don't have a covering index on the indexed view for your query, then you will definitely find a table is quicker. If you do, though, there should be no real difference. Example:

你是否索引了你实际选择的列?如果查询的索引视图上没有覆盖索引，那么您肯定会发现表更快。但如果你这样做了，就不会有真正的不同。例子:

CREATE VIEW dbo.denormalized
WITH SCHEMABINDING
AS
    SELECT  A.id,
            A.col1,
            A.col2,
            ISNULL(B.col3, '') col3
    FROM    dbo.A LEFT JOIN dbo.B ON A.Bid = B.id
GO

CREATE UNIQUE CLUSTERED INDEX UIX_denormlaized
ON dbo.denormalized (id)

So far so good. Now, we try to select from this view as follows:

目前为止一切都很顺利。现在，我们尝试从这个视图中选择如下:

SELECT id, col3 FROM denormalized

The only persisted data for this view is the index on the ID column - the remainder has to be workout out on the fly. So the ISNULL is calculated again for each row. However if we add this index:

此视图的惟一持久性数据是ID列上的索引——其余数据必须动态计算。所以ISNULL是对每一行重新计算的。但是如果我们加上这个指数:

CREATE INDEX IX_denormalized
ON dbo.denormalized (id, col3)

then the same query is served entirely from the persisted index - much quicker, in fact equivalent performance to selecting from a table.

然后，相同的查询完全由持久化索引提供，这在实际上相当于从表中进行选择时的等效性能。

#2

What SQL Server SKU? Only Enterprise Edition considers indexed views in the query plan. Standard Edition will not consider the indexed view, unless the select is from the view and uses a NOEXPAND hint.

SQL Server SKU什么?只有企业版在查询计划中考虑索引视图。标准版不会考虑索引视图，除非select来自视图并使用no展开提示。

Update

更新

Since I already got two comments indicating this is usefull to know, I'm linking the relevant MSDN page Resolving Indexes on Views:

由于我已经收到了两条评论，表明这是有用的，我正在链接相关的MSDN页面解析索引的视图:

Indexed views can be created in any edition of SQL Server. In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used.

可以在任何版本的SQL Server中创建索引视图。在SQL Server Enterprise中，查询优化器自动地考虑索引视图。要在所有其他版本中使用索引视图，必须使用NOEXPAND表提示。

#1