Data Browser - Viewing Site  Sector 23 Code Bank Logged in as:  Guest  




           

SQL Server OR Clause Very Slow when Referencing Multiple Tables
I had a query that looked like this:
SELECT * FROM TABLE A JOIN TABLE B
ON A.Id = B.RefId
WHERE A.IntField = 1 OR B.DateField IS NOT NULL (runs in 150 seconds)

Note that the 'OR' clause references both tables A and B.

Table B was tiny and A was huge, but the reality is that the following queries run almost instantly:
SELECT * FROM TABLE A JOIN TABLE B
ON A.Id = B.RefId
WHERE A.IntField = 1 (runs in 3 seconds)

SELECT * FROM TABLE A JOIN TABLE B
ON A.Id = B.RefId
WHERE B.DateField IS NOT NULL (runs in 3 seconds)

So why would combining them with 'OR' take the query time from 3 seconds + 3 seconds to well over 150 seconds?

I think SQL Server was doing a row by row merge of the results meeting either condition A or B, and this was taking forever for whatever reason. Something to do with A and B being different tables.

I found a trick to make this query go down to 6 seconds. The trick was to alter the fast subqueries so that they return distinct results. Then, use UNION ALL to combine them.

It looks like this when done:
SELECT * FROM TABLE A JOIN TABLE B
ON A.Id = B.RefId
WHERE A.IntField = 1
AND B.DateField IS NULL -- (if it happens to be NOT NULL, the second query will catch it)
UNION ALL
SELECT * FROM TABLE A JOIN TABLE B
ON A.Id = B.RefId
WHERE B.DateField IS NOT NULL

(runs in 6 seconds)

Created By: amos 10/3/2013 1:57:29 PM
Updated: 10/3/2013 1:57:58 PM