为什么将 WHERE 子句放在视图之外会产生糟糕的性能
假设你有一个观点：
CREATE VIEW dbo.v_SomeJoinedTables AS
SELECT
a.date,
a.Col1,
b.Col2,
DENSE_RANK()
OVER(PARTITION BY a.date, a.Col2 ORDER BY a.Col3) as Something
FROM a JOIN b on a.date = b.date
我发现： 的性能
SELECT *
FROM v_SomeJoinedTables
WHERE date > '2011-01-01'
要差得多
SELECT *,
DENSE_RANK()
OVER(PARTITION BY a.date, a.Col2 ORDER BY a.Col3) as Something
FROM a JOIN b ON a.date = b.date
WHERE a.date > '2011-01-01'
比我非常惊讶这两个语句的查询计划不一样
。我还尝试过使用内联表值函数，但查询所需的时间仍然比复制和粘贴视图逻辑的代码长 100-1000 倍。
有什么想法吗？
Let's say you have a view:
CREATE VIEW dbo.v_SomeJoinedTables AS
SELECT
a.date,
a.Col1,
b.Col2,
DENSE_RANK()
OVER(PARTITION BY a.date, a.Col2 ORDER BY a.Col3) as Something
FROM a JOIN b on a.date = b.date
I've found that the performance of:
SELECT *
FROM v_SomeJoinedTables
WHERE date > '2011-01-01'
is much worse than
SELECT *,
DENSE_RANK()
OVER(PARTITION BY a.date, a.Col2 ORDER BY a.Col3) as Something
FROM a JOIN b ON a.date = b.date
WHERE a.date > '2011-01-01'
I'm very suprised that the query plan for these two statements are not the same.
I've also tried using an inline table valued function, but the query still takes 100-1000 times longer than the code where I copy and paste the view logic.
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
它被称为“谓词推送”又名延迟过滤。
SQL Server 并不总是意识到 WHERE 可以“更早”地在视图内部有效地应用。
它已在 SQL Server 2008 中得到缓解，可以更好地按预期工作
It's called "Predicate pushing" aka deferred filtering.
SQL Server doesn't always realise the WHERE can be applied "earlier", inside the view effectively.
It has been mitigated in SQL Server 2008 to work more as expected
我不是 SQL 专家，所以我可能会因为我的愚蠢而被否决，但我的猜测是，在第一种情况下，SQL 在应用 WHERE 中的谓词之前会获取整个视图的结果条款。因此，当您查询视图时，它会选择所有记录，将它们放入内存中，然后在完成后应用日期过滤器。
这看起来类似于在 WHERE 中应用过滤器之前获取联接中指定的整个数据集的方式（这里的教训是，如果可能，您应该在 ON 子句中应用谓词）。
除非以某种方式以不同的方式对待观点。
I'm not a SQL expert, so I may be voted down for my foolishness, but my guess is that in the first case SQL is fetching the results of the entire view before applying the predicate in the WHERE clause. So when you query the view, it selects all of the records, puts them in memory, and then applies the Date filter after it is done.
This seems similar to the way the entire data set specified in your joins is fetched prior to applying the filter in the WHERE (lesson here is that you should apply predicates in your ON clause when possible).
Unless views are treated differently somehow.
OVER() 语法是 SS2005 中的全新语法，显然没有很好地集成到优化器中。我建议你尝试一下更传统的表达方式？如果您关心可优化性，可能不是一个表达式。
http://www.sqlteam.com/article /sql-sever-2005-using-over-with-aggregate-functions
或者，更好的是，更熟悉一下探查器 - 视图应该是可以修复的。
the OVER() syntax was brand-new in SS2005 and apparently not well integrated into the optimizer. I suggest you try a more traditional expression? Probably NOT an expression if you care about optimizability.
http://www.sqlteam.com/article/sql-sever-2005-using-over-with-aggregate-functions
Or, better, get a bit more familiar with the profiler - the view should be fixable.
从技术上讲，您并不是在相同的 SQL 语句之间进行比较。您的视图表明它返回
a.date、a.Col1、b.Col2以及您的 DENSE_RANK() 函数。在没有视图的查询中，您将返回所有列。起初，您可能认为返回所有列会更糟糕。但是，如果不知道表结构（包括索引）是什么样子，就很难确定哪个更好。
您是否比较过每个语句的查询计划？
Technically, you're not comparing between the same SQL statements. Your view indicates that it returns
a.date, a.Col1, b.Col2,plus your DENSE_RANK() function. In your query without the view, you return all columns.At first, you may think that returning all the columns would be worse. But it's difficult to determine which would be better without knowing what the table structure, including indexes, looks like.
Have you compared the query plans for each statement?
作为解决方法，我建议使用函数而不是视图，以便您可以传入数据参数。
As a work-around I would suggest using a function instead of a view so that you can pass in data parameter.