Why is this simple query so slow?

Posted on 2008-11-18
Last Modified: 2012-05-05
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?
Question by:Antagony1960
    LVL 142

    Accepted Solution

    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

    LVL 40

    Expert Comment

    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

    LVL 11

    Author Comment

    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.
    LVL 6

    Expert Comment

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

    LVL 6

    Assisted Solution

    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.
    LVL 11

    Author Comment

    Thanks carlsiy. It's becoming clearer now.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now