20

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?

bpeikes
  • 3,495
  • 9
  • 42
  • 80
  • 2
    Well what do the query plans look like? Are you missing an index? Is the view returning too many rows? If the where clause being applied in the wrong place? – Jonathan Allen Jul 11 '11 at 17:48
  • 1
    Does your view happen to be calling a view? – HLGEM Jul 11 '11 at 17:59
  • What happens if your view does not contain a windowing function? What is the expected semantic result of a VIEW that computes rank over the entire set, but a query to the view adds a new condition--should the RANK insde be applied before or after the condition outside? – ErikE Mar 22 '12 at 01:18

5 Answers5

17

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Nice reference. That's just really scary. It confirms my suspicion that SQL Server has never felt comfortable with the concept of a TOP keyword in SQL. One really ought not to need to know this stuff to write optimized queries. – dkretz Jul 11 '11 at 19:20
  • I was completely surprised to find out how badly SQL Server 2005 handles RANK functions. There really ought to be a hotfix for that and not require an upgrade to SQL 2008. – bpeikes Jul 19 '11 at 15:42
  • @bpeikes: yep, see http://stackoverflow.com/q/4230838/27535 and comment here too: http://stackoverflow.com/q/2798094/27535 – gbn Jul 19 '11 at 16:35
  • 2
    Not sure about this one. I'm having issues with a near-identical scenario with SQL Server 2008 R2. – marknuzz Dec 05 '13 at 21:11
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.

ckramer
  • 9,419
  • 1
  • 24
  • 38
  • +1 I think you're absolutely right. Perhaps stats are off, and you'd have to verify by comparing estimated/actual plans for each query, but it's quite likely that the query from the view materializes the whole join before applying the filter, whereas the outer query can use the filter to choose a more efficient join type if it can eliminate rows from one side of the join earlier. – Aaron Bertrand Jul 11 '11 at 17:48
  • Views are just shorthand for the full expression. They wouldn't be much use if they weren't equally optimizable. – dkretz Jul 11 '11 at 17:52
  • awesome....we don't use views much here, so I've not had a reason to dig too deeply into them. Nice to know that there is some explanation behind it....nicer to know it's better in 2008 – ckramer Jul 11 '11 at 18:36
2

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.

dkretz
  • 37,399
  • 13
  • 80
  • 138
  • FYI, my link demonstrates this has been resolved. See this too (comments too) http://stackoverflow.com/q/2798094/27535 – gbn Jul 11 '11 at 18:22
  • Perhaps, but perhaps not with his site/version. It's not great when the implementation is so dodgy that you need to know everything you or I know to work reliably. – dkretz Jul 11 '11 at 19:07
  • Indeed. And the folk like you or I tend not to use views so wouldn't see it day to day... – gbn Jul 11 '11 at 19:10
1

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?

bobs
  • 21,844
  • 12
  • 67
  • 78
  • Almost valid... the view is slower, this is the point of the question. Even if the in-line query is "handicapped" by SELECT * – gbn Jul 11 '11 at 18:19
0

As a work-around I would suggest using a function instead of a view so that you can pass in data parameter.

Jonathan Allen
  • 68,373
  • 70
  • 259
  • 447