Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2010
    Posts
    40

    Question Unanswered: subquery better than join but problem

    Hi,
    I have been working on a query which essentially joins a call record table (millions or records) to a phone table. Each call record has two phones (calling party and called party).

    Initially I was doing an inner join but the performance was abysmal and i was getting duplicate rows. For example

    select cr.*, pg.name from callrecords cr
    inner join phonegroup pg on (cr.callingphone_id = pg.phone_id OR cr.calledphone=pg.phone_id)
    where pg.id = 555

    To solve the duplicates, i had to add 'distinct' which made it worse yet.

    Using a subquery returned much faster and with no duplicates because the query would either return one callrecord or not if either calling or called phone was present. The challenge i have is i can't seem to find a good way to extract the name of this phone group when using a subquery.

    select cr,*, ?? from callrecords cr
    where ((cr.callingphone_id in (select pg.phone_id from phonegroup pg where pg.id=555)) OR (cr.calledphone_id in (select pg.phone_id from phonegroup pg where pg.id= 555)))

    Any help is greatly appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Please post some sample (input and output) data. I have a hard time understanding what you want.

    And please format your SQL to make it better readable.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Feb 2010
    Posts
    40

    subquery better than join but problem

    Quote Originally Posted by shammat View Post
    Please post some sample (input and output) data. I have a hard time understanding what you want.

    And please format your SQL to make it better readable.
    Sorry Shammat, let me see if this helps. These tables are very large so I don't want to include irrelevant details also.

    v_pgdevice
    group | phone_group_id | device_id

    "All International CSS";8580;"49ce3678-55dc-c56a-90c1-fa5b6e77804f"
    "Vmail Users";4841652;"49ce3678-55dc-c56a-90c1-fa5b6e77804f"
    "All International CSS";8580;"3df60189-769d-817a-3ec1-aa3070699145"
    "MyPhone";384811722;"7743157a-a590-b980-84f1-e4ae6c955c7e"
    "All International CSS";8580;"7743157a-a590-b980-84f1-e4ae6c955c7e"
    ...

    v_callsetup
    call_detail_record_id | origdevice_id | destdevice_id

    606815236;"d0837255-911c-9268-d49b-1320e196e23f";"34c97486-dac3-53b7-4ba0-628e6d59be9d"
    606815223;"297d0e21-5330-4487-80e5-220490f996cb";"34c97486-dac3-53b7-4ba0-628e6d59be9d"
    606838797;"784da0b9-9036-41e1-a520-78a84dfac4b2";"784da0b9-9036-41e1-a520-78a84dfac4b2"
    606838662;"";"784da0b9-9036-41e1-a520-78a84dfac4b2"
    606838619;"";"784da0b9-9036-41e1-a520-78a84dfac4b2"
    606838608;"d0837255-911c-9268-d49b-1320e196e23f";"784da0b9-9036-41e1-a520-78a84dfac4b2"
    606838603;"";"784da0b9-9036-41e1-a520-78a84dfac4b2"
    606838595;"";"784da0b9-9036-41e1-a520-78a84dfac4b2"
    606838567;"";"784da0b9-9036-41e1-a520-78a84dfac4b2"
    606838566;"";"784da0b9-9036-41e1-a520-78a84dfac4b2"

    ...

    --11477 rows, 1078 ms some duplicate CDRs
    select cs.call_detail_record_id,
    cs.origdevice_id,
    cs.destdevice_id,
    pg.group
    from v_callsetup cs
    inner join v_pgdevice pg on (cs.origdevice_id = pg.device_id or cs.destdevice_id = pg.device_id)
    order by cs.call_detail_record_id

    # the result has duplicates (same call_detail_record_id) which forces me to add distinct hurting performance.

    select distinct (call_detail_record_id), ...


    --8050 rows, 500ms # I believe this is the correct result, no duplicates. The problem is now I have no way to include pg.group (the name of the group) in the results.

    select cs.call_detail_record_id,
    cs.origdevice_id,
    cs.destdevice_id
    from v_callsetup cs
    where ((cs.origdevice_id in (select pg.device_id from v_pgdevice pg)) or
    (cs.destdevice_id in (select pg.device_id from v_pgdevice pg)))
    order by cs.call_detail_record_id

    so the question is really:
    1. If I've proven a subquery is faster and the right response, how can I then get a property from the table in the subquery returned in the results? It seems I can only really do this with a join.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    maybe something along the lines of

    Code:
    ...
    inner join phonegroup pg 
       on ((cr.callingphone_id = cr.calledphone AND cr.calledphone = pg.phone_id) OR
           (cr.callingphone_id <> cr.calledphone AND (cr.callingphone_id = pg.phone_id OR cr.calledphone=pg.phone_id)))
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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