SQL Server Outer Apply Query Optimization

Question

I have two tables - Table A and Table B.

Table A has 121,903 rows. Table B has only 95 rows.

I need to join Table A with Table B such that I will get first row of Table B which have matching rows with Table A order by sort criteria.

I am using the following query to get the results. It is returning results correctly but has performance issues.

;WITH [TableAB] AS
(
    SELECT * FROM #TableA A
    OUTER APPLY
    (
        SELECT TOP 1 *  FROM #TableB
        WHERE 
            ([Col1] = A.[Col1]OR [Col1]IS NULL)
        AND ([Col2] = A.[Col2]OR [Col2]IS NULL)
        AND ([Col3] = A.[Col3]OR [Col3]IS NULL)
        AND ([Col4] = A.[Col4]OR [Col4]IS NULL)
        AND ([Col5] = A.[Col5] OR [Col5] IS NULL)
        AND ([Col6] = A.[Col6]OR [Col6]IS NULL)
        AND ([Col7] = A.[Col7]OR [Col7]IS NULL)
        AND ([Col8] = A.[Col8]OR [Col8]IS NULL)
        AND ([Col9] IS NULL)
        AND ([Col10] IS NULL)
        AND ([Col11] = A.[Col11] OR [Col11] IS NULL)
        AND ([Col12] = A.[Col12]OR [Col12] IS NULL)
        AND ([Col13] = A.[Col13]OR [Col13]IS NULL)
        AND ([Col14] = A.[Col14] OR [Col14] IS NULL)
        AND ([Col15]= A.[Col15]OR [Col15]IS NULL)
        AND ([Col16] = A.[Col16] OR [Col16] IS NULL)
        AND ([Col17]= A.[Col17]OR [Col17]IS NULL)
        AND ([Col18]= A.[Col18]OR [Col18]IS NULL)
        AND ([Col19]= A.[Col19]OR [Col19]IS NULL)
        AND ([Col20] = A.[Col20] OR [Col20]IS NULL)
        ORDER BY [SortCriteria]
    ) B
)
SELECT * FROM [TableAB]

It currently takes ~1 minute to execute this query. Is there any way I can rewrite the query to improve the performance?

Note that it is a data warehouse system, the above query is part of a large query which uses CTE table "TableAB".

Thanks.

Answer 1

Since the bulk of the execution is spent sorting TableB, the most likely candidate for improving performance would be to add an index that covers SortCriteria and INCLUDES all the columns in TableB that are selected in the query.