1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Slow sql using left join

Discussion in 'SQL PL/SQL' started by sdavis, Nov 22, 2009.

  1. sdavis

    sdavis Guest

    Hill all,

    When I use the code below, it takes longer than 1 minute to return 6000 records. How can I optimize this? The key fields are already indexed.
    Thanks.

    Select
    s.code,
    s.id,
    s.name,
    c.totalregs
    from sales s
    left join
    ( select code, id, count(*) as totalregs from sales
    group by code, id ) as countall c
    on (s.code=c.code and s.id=c.id)
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260


    Hello there,


    I don't really understand why you use a join to achive this, why not an analytic function? did you try the following?

    Code (Text):

    SELECT code,
           id,
           name,
           COUNT(*) OVER (PARTITION BY code,id) AS totalregs
    FROM sales;
     
    Because as I see you use a LEFT JOIN so it seems to me that at the end you retrieve the whole lines of the sales table. If you want to have the COUNT(*) for each group of (code, id) an analytic function can also do the job (often faster)

    Regards,
    Dariyoosh