Results 1 to 2 of 2

Thread: Slow query

  1. #1
    Join Date
    Feb 2010
    Posts
    38

    Unanswered: Slow query

    Hi!

    I've created this SQL query but it runs really slow. I'm wondering if this is the optimal way of executing the query or if there is possibly some way to speed it up? It's a Progress OpenEdge database.

    Code:
    select ig.itemgrp, sum(sq.qty*se.price) as 'value' 
    from pub.itemgrp ig
    inner join pub.item i on ig.itemgrp=i.itemgrp
    inner join pub.itemvers iv on i.itemcode=iv.itemcode
    left outer join pub.stockeve sq on i.itemcode=sq.itemcode and iv.version=sq.version and sq.stock in ('stock1','stock2') and sq.evedate <= to_date('9/27/2011')
    left outer join (select itemcode, version, max(evedate) as 'mdate' from pub.stockeve where stock in ('stock1','stock2') and evedate <= to_date('9/27/2011') group by itemcode, version) as m on i.itemcode=m.itemcode
    left outer join (select itemcode, version, evedate, max(evetime) as 'mtime' from pub.stockeve where stock in ('stock1','stock2') and evedate <= to_date('9/27/2011') group by itemcode, version, evedate) as n on i.itemcode=n.itemcode and m.mdate=n.evedate
    left outer join (select itemcode, version, evetime, max(evenum) as 'mnum' from pub.stockeve where stock in ('stock1','stock2') and evedate <= to_date('9/27/2011') group by itemcode, version, evetime) as u on i.itemcode=u.itemcode and iv.version=u.version and n.mtime=u.evetime
    left outer join pub.stockeve se on u.mnum=se.evenum and m.mdate=se.evedate and n.mtime=se.evetime and i.itemcode=se.itemcode and iv.version=se.version
    group by ig.itemgrp
    If I add a "where ig.itemgrp = 'group'" it's reasonably fast, but I'd need it to select all item groups.

    Thank you for any help!

  2. #2
    Join Date
    Mar 2012
    Posts
    2
    I think the issue is that the system is having a hard time creating the right plan to access the data, especially with the select statements you are joining with.

    If it were me, I'd break down the query myself so the system doesn't have to determine what is the best plan, and access the data quickly through a view or creating a temp table.

    It's quite hard to figure out what you are trying to do, but can you do something like:

    select subset of what you need or a work table INTO #TEMPTABLE

    go

    Then your simplified query accessing the data as needed for the temp table.

    Also, since * and / are the slowest forms the CPU can do, I'd consider that if the majority of the qty's are 1, then don't do the calculation.

    case when qty = 1 then price else price * qty end

    For million records, this will execute faster since it doesn't have to do the multiplication and setup the decimal field.

Posting Permissions

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