Why is this simple query so slow?

SELECT * FROM Customer WHERE Customer_ID NOT IN(SELECT DISTINCT Customer_ID FROM Booking WHERE Booking_Flag_1)

I thought this would be a really simple query, but it's running painfully slow (over 2 minutes!) and I can't fathom out why. All I'm trying to do is get a list of customer's that don't have any related booking which are flagged. The tables are related one-to-many on Booking.Customer_ID = Customer.Customer_ID. Neither of them are especially large (<6000 records in each).

I would like to know: a) why does this query run so slowly and b) what can I do to make the speed acceptable?
LVL 11
Antagony1960Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
the DISTINCT is "bad", and actually not needed.

please try this alternative
SELECT * FROM Customer c WHERE NOT EXISTS ( SELECT NULL FROM Booking b WHERE b.Customer_ID = c.Customer_ID AND b.Booking_Flag_1 )

Open in new window

0
 
SharathData EngineerCommented:
Just delete DISTINCT from your query and check the query run time.
SELECT * FROM Customer WHERE Customer_ID NOT IN(SELECT Customer_ID FROM Booking WHERE Booking_Flag_1)

Open in new window

0
 
Antagony1960Author Commented:
@Sharath_123:
That took about 20 seconds off but it still took over 100 seconds!

@angelIII:
Well your query works perfectly, thank you. But I'd just like a little explanation for why my original query is bad, even if I drop the DISTINCT keyword. If I run the inner query on its own it is instantaneous, so I don't understand what is going wrong. I think it may have something to do with the NOT operator, because when I drop it--so that I am getting customers with flagged bookings--it is also pretty instantaneous.
 
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
carlsiyCommented:
Exists is faster than In... why?
IN:
Returns true if a specified value matches any value in a subquery or a list.
Exists:
Returns true if a subquery contains any rows.

also see link...
http://decipherinfosys.wordpress.com/2007/01/21/32/

cheers.
0
 
carlsiyConnect With a Mentor Commented:
one thing to note....
When using NOT IN, the query performs nested full table scans, whereas for NOT EXISTS, query can use an index within the sub-query.
0
 
Antagony1960Author Commented:
Thanks carlsiy. It's becoming clearer now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.