#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    Performance of a SQL query


    Hi all,

    I am trying to gather distinct values from a view in conjiuction with a join to another table of disntinct values by excluding some values.

    Query is

    select distinct id
    from Table_a
    where id NOT IN(select distinct id
    from Table_b
    where is_browse = TRUE)
    and layer != 'acca'
    and layer != 'base';

    The view has about 120 k records and the table has 1.6 million rows and the query never finishes and has been running for hours.

    Is there a better way to write this up. Any help is much appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    https://wiki.postgresql.org/wiki/Slow_Query_Questions
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    DO NOT USE with Large data


    It is always a bad idea to use IN statements with large data.

    Write a Sub Query.

    It is better write a SQL and them use the SELF JOIN.

    I wish this will help you!

IMN logo majestic logo threadwatch logo seochat tools logo