Slow query

tosa · #1 (**permalink**) 01-03-12, 02:24

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!

RobertInCA · #2 (**permalink**) 03-23-12, 23:15

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech