Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: how to refine NOT IN to improve performance

    Hi ALL,

    I have a table like the following which will have more than 50,000 entries.

    table TAB

    ID | Name | ParentID
    --------------------
    1680 | textP1 | 1699
    1686 | text1 | 1680
    1687 | text2 | 1680
    1699 | textP2 | NULL
    1685 | try | NULL
    1200 | abc | 680

    And i would like to run the following query on it
    select ID from TAB where job_name like 'text%' and (ParentID is NULL or ParentID not in(select ID from TAB where name like 'text%')

    The result as expect with above set of data is 1699. But on usual the results will be in around 15000+ items.

    But as i was using NOT IN it is causing a performance issue. Can anyone let me know how to refine this.

    Thanks in Advance
    Maddy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT tab.id 
      FROM tab 
    LEFT OUTER
      JOIN tab AS par
        ON par.id = tab.parentid
       AND par.job_name LIKE 'text%' 
     WHERE tab.job_name LIKE 'text%' 
       AND par.id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Seems to me all you need is
    select ID from TAB where job_name like 'text%' and ParentID is NULL

  4. #4
    Join Date
    Mar 2009
    Posts
    2
    Hi

    Rudy .. Thanks for the help.. Thanks a lot...

Posting Permissions

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