sql - 比较 SQL Server 中索引 View 和存储过程的性能

Question

我最近才意识到您现在可以在 SQL Server 中为 View 建立索引(请参阅 http://technet.microsoft.com/en-us/library/cc917715.aspx )。我现在试图弄清楚，与缓存执行路径的存储过程中的相同查询相比，对索引 View 的查询何时可以获得更好的性能？

以下例:

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

每次运行时日期都会不同，因此如果这是一个 View ，我不会在 View 中包含 WHERE ，而是将其作为针对 View 的选择的一部分。如果它是一个存储过程，则日期将是一个参数。请注意，表中约有 300,000 行。其中 200,000 个将满足带有日期的 where 子句。 group by后返回10,000。

如果这是一个索引 View ，我是否应该期望从中获得比有机会缓存​​执行路径的存储过程更好的性能？或者过程会更快吗？或者差异可以忽略不计？我知道我们可以说“两者都尝试一下”，但是有太多的因素可能会错误地影响结果，导致我得出错误的结论，所以我想听听更多其背后的理论以及预期的结果是什么.

谢谢!

Answer 1

索引 View 可以被视为普通表 - 它是具体化行集合。

所以问题实际上归结为“正常”查询是否比存储过程更快。

如果您查看 SQL Server 执行任何查询(存储过程调用或即席 SQL 语句)所经历的步骤，您将发现(大致)以下步骤:

1. 语法检查查询
2. 如果没问题 - 它会检查计划缓存以查看是否已有该查询的执行计划
3. 如果有执行计划 - 该计划将被(重新)使用并执行查询
4. 如果还没有计划，则确定执行计划
5. 该计划存储在计划缓存中以供以后重用
6. 执行查询

要点是:即席 SQL 和存储过程在处理上没有什么不同。

如果临时 SQL 查询正确使用参数 - 无论如何，为了防止 SQL 注入(inject)攻击 - 它的性能特征没有不同并且绝对<强>不比执行存储过程差。

存储过程还有其他好处(例如，无需授予用户直接表访问权限)，但就性能而言，使用正确参数化的即席 SQL 查询与使用存储过程一样高效程序。

使用存储过程优于非参数化查询，这主要有两个原因:

• 由于每个非参数化查询都是 SQL Server 的新的、不同的查询，因此它必须为每个查询执行确定执行计划的所有步骤(从而浪费时间- 并且还浪费计划缓存空间，因为将执行计划存储到计划缓存中最终并没有真​​正的帮助，因为该特定查询可能不会再次执行)

• 非参数化查询面临 SQL 注入(inject)攻击的风险，应不惜一切代价避免

现在，当然，如果您索引 View 可以显着减少行数(通过使用GROUP BY子句) - 那么当然 该索引 View 将比针对整个数据集运行存储过程时快得多。但这并不是因为采用了不同的方法 - 这只是规模问题 - 查询几十或几百行将比查询 200'000 或更多行更快 - 无论您采用哪种查询方式。