Sql-server – Window functions cause awful execution plan when called from a view with external parametrized ‘where’ clause

sql-server-2008viewwindow functions

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?

Best Answer

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)

Related Solutions

Sql-server – IN Clause causes Execution plan to change from Nested Loops to Hash Match

Well, you can force nested loops by using OPTION(LOOP JOIN) however, I would advise using OPTION(LOOP JOIN, MERGE JOIN) this is working with the optimiser rather than against it and is saying do what you want as long as its not a hash match.

But I would also dig a little deeper. With the IN clause it is possible that SQL is performing multiple index scans as opposed to a single table scan, so you may need to use an WITH(INDEX(0)) instead.

If you add SET STATISTICS IO at the top of the batch and run it with and then without the IN clause and check the number of physical and logical reads for each, as well as the read-ahead reads, this will prove or disprove my theory.

Oracle View Ignoring External WHERE Clause

With a bit of trial and error, I was able to spot the culprit. Turns out, it has nothing to do with the view itself. Attempting to manually push the where clause directly into the query also gave the same bad results.

As it turns out, the problem was cause by the CONNECT BY clause in the inner queries. My suspicion (which may be completely wrong) is that it has to do with the fact that Oracle 10g cannot handle recursive CTE's. If anyone has any more information about why this might have happened, I am all ears.

I was able to work around the issue by moving the

SELECT level AS UNPIVOT_ROW FROM DUAL CONNECT BY level <= 3

subquery to its own CTE block and selecting from that as normal. Below I have included the (condensed) working version of the above broken query.

CREATE OR REPLACE VIEW FSA.FSA_V_DB_TOTALS_2
(...)
AS
WITH
--Queries to generate intermediate result sets

--'NUMBERS' CTE block to work around Oracle 10g limitation wherein WHERE
--  clause is ignored if CONNECY BY is directly written into inner queries.
--  NOTE: the size limitation (currently 10) only needs to be larger than
--        or equal to the largest value needed.
NUMBERS AS (
SELECT level AS UNPIVOT_ROW
  FROM DUAL CONNECT BY level <=10
),

--Distribution TRA
DIST_TRA AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA002_DIST_TRA T2 ON T1.SERIAL_NO = T2.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 3      -- <==  THIS
        )
),

--Distribution Fran
DIST_FRAN AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA003_DIST_FRAN T3 ON T1.SERIAL_NO = T3.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 2      -- <==  THIS AS WELL
        )
)

--Queries to generate final result set based on CTE intermediate queries
--Subtotals / System totals for Dist-TRA, Dist-Fran, Trans-TRA, Trans-Fran
SELECT ...
  FROM (SELECT * FROM DIST_TRA
        UNION ALL
        SELECT * FROM DIST_FRAN)

Best Answer

Related Solutions

Sql-server – IN Clause causes Execution plan to change from Nested Loops to Hash Match

Oracle View Ignoring External WHERE Clause

Related Question