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.
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.