1

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?

  • How about forcing it to always use the clustered index by using an hint. e.g. WITH (INDEX (PK_ID)) – Daniel Björk Aug 12 '17 at 20:13
3

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 ;
0

The most important optimization here is to make sure the Actual Query Plan contains no CONVERT_IMPLICIT operators. This can happen if there are slightly different data types across the tables being UNION ALL'd. This can result in easily a 10x-100x performance degradation.

If you want to get only the last 25 rows, you can create a temp table for each where you only insert 25 rows for each. Then your FETCH will get the "best 25" of the 75 total (three tables being unioned on 25 rows each).

Alternatively, if you have Enterprise Edition, you can create an indexed partitioned view.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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