使用带有索引的实际非规范化表而不是索引视图对 SQL 查询性能更好吗? [英] Better for SQL Query performance to use an actual denormalized table with indexes rather than an indexed view?

问题描述

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

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.

我应该注意到，当我创建视图时，SELECT 中有很多 ISNULL.我知道如果将这些列加入到常规视图中，这些会影响性能，但我的印象是，如果将视图编入索引就可以了.ISNULL 可能是问题吗?

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?

推荐答案

您是否为实际选择的列编制了索引?如果您在查询的索引视图上没有覆盖索引，那么您肯定会发现表更快.但是，如果您这样做，则应该没有真正的区别.示例:

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

此视图唯一保留的数据是 ID 列上的索引 - 其余数据必须即时进行锻炼.因此，对每一行再次计算 ISNULL.但是如果我们添加这个索引:

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:

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.

这篇关于使用带有索引的实际非规范化表而不是索引视图对 SQL 查询性能更好吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！