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
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

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

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

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.
The 14th Annual Expert Award Winners

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

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

also see link...

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.
Antagony1960Author Commented:
Thanks carlsiy. It's becoming clearer now.
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.