Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Toronto
    Posts
    3

    Question Unanswered: performance issue

    Should I worry about the performance of a query like:
    SELECT CustomerID FROM invoices WHERE InvoiceID NOT IN (SELECT TOP 50% InvoiceID FROM invoices)
    What if the "invoices" is very big?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would not worry about performance until you can define what "very big" means

    also, in the example you gave, you really should have an ORDER BY in the subquery, otherwise it doesn't make sense to pull 50% of all the invoices randomly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto
    Posts
    3

    absolutely right

    The query I used for exemplification doesn't make much sense. My real query it's totaly different but I'm affraid that the inner SELECT will return too many records (hundreds of thousands maybe).

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since I can only see six invoices in your imaginary table, I really doubt that performance will be an issue for that syntax.

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maybe the inner subquery will be optimized to a join, who knows without at least setting it up and running an EXPLAIN

    but a few hundred thousand is typically a piffle
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    been wantin' to use that word for ages

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •