为什么在视图之外放置 WHERE 子句的性能很差 [英] Why does putting a WHERE clause outside view have terrible performance

问题描述

假设您有一个视图:

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'

我很惊讶这两个语句的查询计划不一样.

I'm very suprised that the query plan for these two statements are not the same.

我也尝试过使用内联表值函数，但查询仍然比我复制和粘贴视图逻辑的代码长 100-1000 倍.

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.

有什么想法吗?

推荐答案

它叫做谓词推送" 又名延迟过滤.

It's called "Predicate pushing" aka deferred filtering.

SQL Server 并不总是意识到 WHERE 可以更早"地在视图内部有效地应用.

SQL Server doesn't always realise the WHERE can be applied "earlier", inside the view effectively.

它已在 SQL Server 2008 中得到缓解，可以按预期工作

It has been mitigated in SQL Server 2008 to work more as expected

这篇关于为什么在视图之外放置 WHERE 子句的性能很差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！