Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I had this issue long time ago, I found a workaround which suited me and forgot about it. But now there's that question on SO so I'm willing to bring this problem up.


There's a view that joins few tables in a very straightforward way (orders + order lines).

When queried without a where clause, the view returns several million lines.
However, noone ever calls it like that. The usual query is

select * from that_nasty_view where order_number = 123456;

This returns about 10 records out of 5m.

An important thing: the view contains a window function, rank(), which is partitioned exactly by the field using which the view is always queried:

rank() over (partition by order_number order by detail_line_number)

Now, if this view is queried with literal parameters in the query string, exactly as shown above, it returns the rows instantly. The execution plan is fine:

  • Index seek on both tables using the indices on order_number (returns 10 rows).
  • Calculating windows over the returned tiny result.
  • Selecting.

However, when the view is called in a parametrized way, things get nasty:

  • Index scan on all tables ignoring indices. Returns 5m rows.
  • Huge join.
  • Calculating windows over all partitions (about 500k windows).
  • Filter to take 10 rows out of 5m.
  • Select

This happens in all cases when parameters are involved. It can be SSMS:

declare @order_number int = 123456;
select * from that_nasty_view where order_number = @order_number;

It can be an ODBC client, such as Excel:

select * from that_nasty_view where order_number = ?

Or it can be any other client that uses parameters and not sql concatenation.

If the window function is removed from the view, it runs perfectly quickly, regardless of whether or not it's quieried with parameters.

My workaround was to remove the offending function and reapply it at a later stage.

But, what gives? Is it genuinely a bug in how SQL Server 2008 handles window functions?

share|improve this question
    
order_number is primary key? Datatypes of column and parameter match? –  gbn Feb 8 '12 at 19:08
    
order_number is not a primary key. It is int not null with nonclustered index on it in both tables. –  GSerg Feb 8 '12 at 19:10
4  
SQL Server 2005 had issues with predicate pushing in this area. I thought they were fixed now. BTW your TSQL example uses a variable not a parameter. Does adding OPTION (RECOMPILE) help? –  Martin Smith Feb 8 '12 at 19:20
1  
@GSerg - So on the bad plan with the filter last it has an estimated 5 million rows into the filter and an estimated 10 rows out matching the actual? If so maybe it is that predicate pushing issue still not fixed completely then. –  Martin Smith Feb 8 '12 at 19:49
2  

2 Answers 2

I'd try replacing the view by table-valued udf. That way it will filter records first, and then apply window function. This function may accept table parameter so you can pass multiple order_number into it

share|improve this answer
    
Yet another workaround, yes. I couldn't do that though because not all clients were able to consume a table-valued function. –  GSerg Feb 8 '12 at 19:39
    
Why? I'm not 100% sure, but I think all you need is to change query a little to something like SELECT * FROM my_funct(12345) –  a1ex07 Feb 8 '12 at 19:40
    
One of the requirement was the query is consumable by end users using Excel (that is, by MS Query), and MS Query will not let you do that, at least in versions up to 2003. –  GSerg Feb 8 '12 at 19:47
    
it will filter records first, and then apply window function is incorrect. There is no deterministic order to execution –  Remus Rusanu Mar 9 '13 at 12:48

This appears to be a long standing issue that keeps resurfacing in one form or another and is still present in SQL Server 2012.

Some posts discussing it are

All current versions of SQL Server up to and including 2012 are not able to push the filter on a partitioning group past the sequence project for a parameterised predicate except if option(recompile) is used (if 2008+).

An alternative to the recompile hint would be to rewrite the query to use a parameterised inline TVF as suggested by @a1ex07)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.