Sql-server – Optimizing key lookup in subquery with UNION ALL

bookmark-lookupperformancequery-performancesql serversql-server-2012

I am trying to optimize following query (little bit more complex actually, but this is important part):

SELECT Id, StatusDate, [...Lot Of Columns...]
FROM ( (
        SELECT Id, StatusDate, [...Lot Of Columns...]
        FROM Results_201505
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, [...Lot Of Columns...]
        FROM Results_201504
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, [...Lot Of Columns...]
        FROM Results_201503
        WHERE A = 0, B = 1, C = 3
    )
) as result
ORDER BY StatusDate DESC
OFFSET xxx ROWS
FETCH NEXT 25 ROWS ONLY

On every table I've created index covering 'where' part:

CREATE NONCLUSTERED INDEX ix_asdf ON Results_asdf (StatusDate DESC)
INCLUDE (A, B, C)

I can't use index A, B, C, StatusDate, because the WHERE conditions are changing depending on filters that are user selected. It can be WHERE A=, B=, C=, but also WHERE B=, D= or WHERE C= or without WHERE at all).

My idea is that SQL Server will perform fast index scan with my index, and delay key lookup until last possible moment (fetching top 25 rows) – this seems to me like most reasonable thing to do.

And indeed, if I comment out [... Lot of Columns...] everything goes as planned – index is used, no key lookup is necessary and everything is very fast.

But in full form, SQL Server (according to execution plan, as shown by SSMS) tries to do key lookup for each row, before OFFSET ... FETCH 25.

The reason is obvious (I believe) – Sql Server realizes that UNION ALL concatenates multiple tables, so after UNION it is too late to do key lookup (because after UNION SQL Server knows only ID, but doesn't know which results table it came from). ID is globally unique, but query optimizer probably doesn't know that.

Of course I'm able to work this around with something like:

SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ]
FROM (
    (
        SELECT Id, StatusDate, 'Results_201505' as tbl
        FROM Results_201505
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, 'Results_201504' as tbl
        FROM Results_201504
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, 'Results_201503' as tbl
        FROM Results_201503
        WHERE A = 0, B = 1, C = 3
    )
) as result
ORDER BY StatusDate DESC
OFFSET xxx ROWS
FETCH NEXT 25 ROWS ONLY

But my question is – is there a better way? Some kind of hint to optimizer would be perfect, but if it's not possible – what's most elegant/fastest/best way to handle this query?

Best Answer

Have you tried this?

SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ]
FROM (
        SELECT Id, StatusDate, 'Results_201505' as tbl
        FROM Results_201505
        WHERE A = 0, B = 1, C = 3
    UNION ALL 
        SELECT Id, StatusDate, 'Results_201504' as tbl
        FROM Results_201504
        WHERE A = 0, B = 1, C = 3
    UNION ALL 
        SELECT Id, StatusDate, 'Results_201503' as tbl
        FROM Results_201503
        WHERE A = 0, B = 1, C = 3
    ORDER BY StatusDate DESC
    OFFSET xxx ROWS
    FETCH NEXT 25 ROWS ONLY
) AS result ;
Related Question