How to make use of Batch Mode with an UNPIVOT (a loop join)?

12 votes

1 answer

837 views

sql-server sql-server-2014 columnstore unpivot batch-mode

                          I have a query of the following form:

    SELECT ...
    FROM ColumnstoreTable cs
    CROSS APPLY (
        SELECT *
        FROM (VALUES
            ('A', cs.DataA)
            , ('B', cs.DataB)
            , ('C', cs.DataC)
        ) x(Col0, Col1)
    ) someValues

This takes every row from a Columnstore-backed subquery (ColumnstoreTable) and multiplies those rows. This is essentially an UNPIVOT. The real query is larger than this. This part of the query feeds into other processing.

The problem here is that this CROSS APPLY is implemented as a loop join which is a reasonable choice. Unfortunately, loop joins do not support batch mode.

This part of the query is very performance critical and I suspect that running it in batch mode could be very beneficial to performance.

How can I rewrite this query so that I do not transition out of batch mode?

I did try using a temporary table instead of VALUES, but that did not change the fact that there is no equality join condition to hash join on.
                        

Asked by boot4life (1289 rep)

Jan 12, 2016, 03:40 PM
Last activity: Jan 12, 2016, 10:17 PM

How to make use of Batch Mode with an UNPIVOT (a loop join)?

Related Questions