Subquery in a join is slowing down performance, looking for a better alternative

Question

Below is a snippet of code similar to what I am using.

DECLARE
    @UserParam = NULL    --optional paramater

SELECT 
    rtrim(item)         [aKey]
INTO
    #aKeyTable
FROM 
    myDB.dbo.fnSplit(@UserParam,',') 

SELECT
/* Lots of columns, not important to the question */
FROM
   myDB.dbo.tableB b
   JOIN myDB.dbo.tableC c ON c.cKey = b.bKEY
       AND (c.columnA IN
           (
           SELECT
               aKey
           FROM
               #aKeyTable
           )
               OR @UserParam IS NULL)

My question is this: How do I remove the subquery to improve performance.

Requirements:

@UserParam is optional
@UserParam can have multiple comma separated parameters
@UserParam has to either match columnA in tableC OR be NULL
Using a WHERE clause isn't an option either, it impacts performance too much as well

I am using SQL Server 2014

UPDATE: My entire query is very long and it takes about 15-20 secs on average to run depending on parameters but according to the Execution Plan this subquery is using 89% of the performance. I had it in a WHERE clause previous to this and the performance was comparable and sometimes slower.

Thanks

`AND (c.columnA IN` should be `WHERE . . .` – Gurwinder Singh Feb 14 '17 at 17:55 — Gurwinder Singh, Feb 14 '17 at 17:55

score 1 · Accepted Answer · answered Feb 14 '17 at 17:56

1

Hard to know for sure without a query plan to see; that said, perhaps create an index on column aKey?

answered Feb 14 '17 at 17:56

user1327961

452
2
8

I marked this as the answer - I don't have permissions to make indexes but the Execution Plan noted a missing index. I changed my query to use tables/columns that were indexed. I also reordered a few JOINS, in the end I went from 15-20 seconds depending on parameters, to less than a second. – DRT Feb 15 '17 at 23:27

score 0 · Answer 2 · edited May 23 '17 at 12:17

Did You consider using TVP? They are best for this purpose. Read also Erland Sommarskog for more details.

It is problematic to combine different cases into into one execution plan. When @UserParam is empty the situation is entirely different than when it is not. You should have execution plan for each case. You can induce an IF and make two queries. For more parameters You would end up with dynamic sql as exponential growth of combinations is not manageable otherwise.

Number of rows for table variable should be estimated by optimizer as 1, leading to index seeks. If the selectivity of parameters is good, this should work.

Subquery in a join is slowing down performance, looking for a better alternative

2 Answers2