Why does putting a WHERE clause outside view have terrible performance

Question

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?

Answer 1

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

Answer 2

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.