Results 1 to 12 of 12
  1. #1
    Join Date
    May 2003
    Location
    Tampa, Florida
    Posts
    13

    Unanswered: Sub Select is very slow

    i'm doing a query to eliminate duplicate records from 2 identical tables, this is the query i wrote

    select info_usa_dealers_tb.*
    From info_usa_dealers_tb
    where info_usa_dealers_tb.dealerphone =
    (
    select distinct ch_dealer_info_tb.dealerphone from ch_dealer_info_tb
    where ch_dealer_info_tb.dealerphone=info_usa_dealers_tb. dealerphone

    )

    it takes so long to get the records or sometimes freezes it too, even for count it takes a long time.

    btw: both tables has 15000, and 22000 records. Is that why it is taking a long time? I have indexed both the tables too. I still don't know why

    Can someone help me in this, may be the query i'm doing is wrong i guess.

    Thanks
    Shriyan
    ******

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Sub Select is very slow

    Originally posted by sshriyan
    i'm doing a query to eliminate duplicate records from 2 identical tables, this is the query i wrote

    select info_usa_dealers_tb.*
    From info_usa_dealers_tb
    where info_usa_dealers_tb.dealerphone =
    (
    select distinct ch_dealer_info_tb.dealerphone from ch_dealer_info_tb
    where ch_dealer_info_tb.dealerphone=info_usa_dealers_tb. dealerphone

    )

    it takes so long to get the records or sometimes freezes it too, even for count it takes a long time.

    btw: both tables has 15000, and 22000 records. Is that why it is taking a long time? I have indexed both the tables too. I still don't know why

    Can someone help me in this, may be the query i'm doing is wrong i guess.

    Thanks
    Shriyan
    ******
    Possibly the DISTINCT clause makes the query very slow.

  3. #3
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    duplicates

    maybe you try to use a join instead of this subquery

    is faster I think

    also do vacuum full to the database
    maybe this helps to
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  4. #4
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    i agree with eperich...

    Also your query is a co-related sub query
    i.e for every row in the outer query , the inner query is executed once.
    So try to use joins instead...

  5. #5
    Join Date
    Sep 2003
    Location
    Wisconsin, USA
    Posts
    34
    Try this.



    SELECT info_usa_dealers_tb.*, ch_dealer_info_tb.dealerphone
    FROM info_usa_dealers_tb
    INNER JOIN ch_dealer_info_tb
    ON ch_dealer_info_tb.dealerphone=info_usa_dealers_tb. dealerphone;

  6. #6
    Join Date
    Jan 2004
    Posts
    4
    I've also got a very very slow subquery (about 2.5 days to run now!)

    SELECT a.epochsecs, ratiopriceapriceb, ratiopriceapricebdiff1, (SELECT ratiopriceapricebdiff1 FROM ratiopricedelta b WHERE b.epochsecs > a.epochsecs ORDER BY epochsecs LIMIT 1)
    AS nextratioprice INTO rr FROM working;

    working has about 300k rows
    ratiopricedelta has about 30k rows

    Any ideas for a quicker query?

    Thanks

  7. #7
    Join Date
    Jan 2004
    Posts
    24
    I don't use Postgres, but 2.5 days to run. That's insane! I do subselects on a competitors product with 10 tables with each over 20 million entries. And I've got it down to less than 3 seconds.

    I would say based on your query, it would do a full table scan since you don't have a where clause on working. So that would make it slow, but I bet the biggest problem is that you don't have your indexes setup correctly.

    Indexes will save your life many times over.

    Donny
    http://www.phpscripts.com/

  8. #8
    Join Date
    Jan 2004
    Posts
    4
    I have an index on epochsecs in table working...... Not sure what else would be needed. Maybe one on the epochsecs column in ratiopricedelta. I'll try that, thanks for the suggestion.

  9. #9
    Join Date
    Oct 2003
    Posts
    706
    The most common cause of problems involving sub-selects is that the sub-selecting query winds up being executed thousands of times.

    Problems can also be caused by using the WHERE clause in cases where JOIN should be used.

    Remember that SQL allows you to specify the same results in many different ways, yet the query-optimizer can only work with the actual query-string you have chosen to write. The best "query optimizer," therefore, is you.

    When you have written your initial version of the query, use a facility such as EXPLAIN to determine exactly what steps the DBMS would execute in response to your query. You may need to re-write it several times.

    By the way, the query-optimizer looks at the whole query, including all sub-selects, to develop its execution plan. It doesn't "examine one and then the other."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  10. #10
    Join Date
    Jan 2004
    Posts
    4
    Have you got a better query than the one above then? I couldn't think of one.....

  11. #11
    Join Date
    Oct 2003
    Posts
    706

    Unhappy

    C'mon... I've got queries of my own. You gotta work in your own environment, with your own data, and try things. EXPLAIN will tell you whether a particular query's gonna be doggrel before you try to execute it. There can be half-a-dozen ways to write the same query.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  12. #12
    Join Date
    Jan 2004
    Posts
    4
    Maybe one on the epochsecs column in ratiopricedelta. I'll try that, thanks for the suggestion.
    This worked a treat!

Posting Permissions

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