I have a query that uses a conditional join similar to the code snippet below. The join is different depending on whether or not parameter _search_string is null.
Even when _search_string is null, this query runs for about 3 seconds as opposed to 0.05 seconds without the conditional join. I did try it using an 'or' in the join instead of a 'case' but no change. The real query is quite long with a number of joins. I hate to just use an 'if' statement and duplicate the code. I could build a string and execute that, but this query is called quite a lot and I'd rather avoid the performance hit.
How do I do this?
select *
from tableA
left join tableB
on case
when _search_string is not null
then tableA.customer_name = tableB.customer_name
else
tableA.customer_id = tableB.customer_id
end
Your query is using a CASE inside the ON condition prevents the query optimizer from efficiently using indexes, which impacts performance. When you use CASE inside a JOIN the database cannot determine which index to use, leading to a more expensive table scan. A better approach is to use two LEFT JOINs and then filter in the WHERE clause to select the appropriate one:
SELECT *
FROM tableA
LEFT JOIN tableB AS b1
ON tableA.customer_name = b1.customer_name
LEFT JOIN tableB AS b2
ON tableA.customer_id = b2.customer_id
WHERE (_search_string IS NOT NULL AND b1.customer_name IS NOT NULL)
OR (_search_string IS NULL AND b2.customer_id IS NOT NULL);