在SQL Server中比较索引视图和存储过程的性能表现

Question

最近我才意识到现在可以在SQL Server中索引视图（请参见http://technet.microsoft.com/en-us/library/cc917715.aspx）。现在，我正在尝试弄清楚针对已经缓存执行路径的存储过程内部的相同查询与针对已索引视图的查询哪个性能更好?

以以下为例:

SELECT colA, colB, sum(colC), sum(colD), colE
FROM myTable
WHERE colFDate < '9/30/2011'
GROUP BY colA, colB, colE

每次运行日期都会不同，因此，如果这是一个视图，我不会将WHERE包含在视图中，而是作为我的选择部分来对视图进行选择。如果它是存储过程，则日期将是参数。请注意，该表中有约30万行。其中20万个满足日期的where子句。在group by之后将返回1万个。

如果这是一个索引视图，我应该期望获得比已经有机会缓存执行路径的存储过程更好的性能吗？还是过程会更快？还是差异可以忽略不计？我知道我们可以说“只是尝试两者”，但有太多的因素可能会错误地偏向结果，导致我得出错误的结论，所以我想听听更多的理论背景和预期结果。

谢谢！

- Nullqwerty

---

每个日期的colA，colB，colE组平均有多少行？ - Martin Smith

你现在可以在 SQL Server 中创建索引视图。这项功能从 SQL Server 2000 开始提供。 - Bogdan Sahlean

@MartinSmith 大约20万。 - Nullqwerty

@Nullqwerty - 所以平均每个colA，colB，colE每个日期只有1-2行？在这种情况下，与第一种情况相比，“GROUP BY colA，colB，colE，Date”的好处大大降低了！ - Martin Smith

@SonicTheLichen - 是的。和任何索引一样，维护它们需要额外的开销。 - Martin Smith

Answer 1

一个索引视图可以被看作是一个常规表——它是一组已物化的行。所以问题实际上就是是否一个"常规"查询比存储过程更快。

如果你看一下SQL Server执行任何查询(存储过程调用或即席SQL语句)经历的步骤，你会发现以下步骤（大致）：

1. 语法检查查询
2. 如果正常——它检查计划缓存是否已经有该查询的执行计划
3. 如果存在执行计划——则重复使用该计划并执行查询
4. 如果没有计划，则确定执行计划
5. 将该计划存入计划缓存以供后续使用
6. 执行查询

关键是：即席SQL和存储过程没有任何不同对待。

如果一个即席SQL查询正常地使用了参数——它应该这样做，以预防SQL注入攻击——它的性能特征与执行存储过程没有区别，而且绝对不会更差。

存储过程具有其他优点(例如无需直接为用户授予表访问权限)，但在性能方面，使用正确参数化的即席SQL查询与使用存储过程同样高效。

使用存储过程而不是非参数化查询有两个主要原因：

• 由于每个非参数化查询对于SQL Server来说都是一个新的、不同的查询，它必须经历所有确定执行计划的步骤，对于每个查询(从而浪费时间——还浪费计划缓存空间，因为将执行计划存储到计划缓存中实际上没有帮助，因为该特定查询可能不会再次执行)

• 非参数化查询有遭受SQL注入攻击的风险，应尽量避免

现在当然，如果你的索引视图可以通过使用GROUP BY子句显著减少行数(例如几十或几百行)，那么这个索引视图肯定比针对整个数据集运行存储过程要快得多。但这并不是因为采取了不同的方法——这只是一个规模问题——查询几十或几百行会比查询20万行或更多行更快——无论你用什么方式查询。

- marc_s

---

感谢您的回复。关于最后一段，Proc和View都将使用group by。但奇怪的是，我可以找到很多文章说，由于缓存，使用procs会有性能优势。您说即使不使用proc也会发生这种情况。这是否因数据库而异？ - Nullqwerty

@Nullqwerty：是的，存储过程的性能优势是关于SQL Server的一个难以消除的神话 - 相比于适当参数化的查询，真的没有任何好处。这样的查询执行计划被缓存的方式也是一样的，会像存储过程执行计划一样长时间保留在缓存中 - 在这个领域真的没有任何好处可以从存储过程中获得。 - marc_s

参见：预编译存储过程：事实还是神话，为什么存储过程比查询更快？，“SQL专家”第14篇：存储过程行为背后的神话。 - marc_s