Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

Question

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 queried 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?

Answer 1

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

• Performance Tuning 101 - 2005
• Plan Regression with Sequence Projections - 2008 R2 @Paul White
• Common Table Expressions (CTEs), Window Functions, and Views - 2012 @Jonathan Kehayias
• The Problem with Window Functions and Views

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)

Answer 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