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.
However, when the view is called in a parametrized way, things get nasty:
Index scanon all tables ignoring indices. Returns 5m rows.
- Huge join.
- Calculating windows over all
partitions (about 500k windows).
Filterto take 10 rows out of 5m.
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?