Performance of 'OR' in a where clause

Question

Post reply

Performance of 'OR' in a where clause

homebrew01

SSC Guru

Points: 55548
More actions
October 17, 2014 at 1:08 pm

#300246

I don't have the code in front of me, so just wondering if there's a general principal at work here.
I had a pretty simple query like
select * from table_A
where email in (select email from table_B)
or
email not in (select email from table_c)
It ran for a very long time.
But if I ran the select with either of the conditions by itself it took just a second. Combining both conditions really slowed it down.
Seemed odd to me.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick suggestion, try two separate queries, one for each email table and combine the results using union all.

😎

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

OR can lead to scans on the index because multiple levels of testing are needed and you can't simply seek for values because of that. Depending on the use of the OR, the indexes in question, and all the rest of the fun stuff that affects performance and the decisions of the optimizer.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

I prefer EXISTS over NOT IN especially, since NULL values will prevent NOT IN from working. You might see if that also corrects the optimizer issue.

select *

from table_A a

where

exists(select 1 from table_B b where b.email = a.email)

or not exists(select 1 from table_C c where c.email = a.email)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

Eirikur Eiriksson (10/17/2014)
Quick suggestion, try two separate queries, one for each email table and combine the results using union all.
😎

I did something like that at the time, putting the result into a temp table, then using the temp table in the main query.

Thanks for all the replies.