Question Need report to run faster

[jdfitzus]

I have a report that I am creating in Progress (OpenEdge Release 10.1C03) and it is running slowly, actually it has not finished running yet. It is for a whole company with multiple orders and lines but it is only viewing specific products from an input list, a cando list of taken bys and a cando list of warehouses.

I have deleted alot of the miscellaneous out of the following to make it shorter (like all the variables):
output to /temp/SKJ1312013.csv.

export delimiter "~t"
"InvoiceDT"
"Invoice#"
"SaleDT"
"ProductSKU"
"Quantity".

INPUT FROM /home/joef/cramerrew/finallist.


All the variables are defined here*


v-whse = "dsp,fhp,fws,gar,jan,lac,lft,lit,mob,nat,oxf,tup,vks".


v-takenby = "abs,amj,ddw,dfv,dtb,eak,jbbn,jrt,lacf,ldl,lrj,lsc,mgr,mlhd,mtps,pgl,rl h,sh, thdc,tv".

REPEAT:
IMPORT DELIMITER ","
v1.

FOR EACH oeeh use-index k-invproc WHERE oeeh.cono = 1 AND
oeeh.stagecd >= 4 and oeeh.stagecd <= 5
and oeeh.invoicedt >= 01/01/13
/* beg-date */
AND oeeh.invoicedt <= 01/31/13
/* end-date */
and oeeh.langcd = 'sr'

and can-do(v-whse,oeeh.whse)

and can-do(v-takenby,oeeh.takenby)
NO-LOCK,
each icsd where icsd.cono = oeeh.cono and icsd.whse = oeeh.whse no-lock,
EACH oeel OF oeeh use-index k-oeel WHERE oeel.cono =
1 and (oeel.vendno = 50110 or oeel.vendno = 81150)
and oeel.qtyship > 0 and oeel.specnstype <> "L" and oeel.statustype ne 'c' NO-LOCK,
each icsc where icsc.catalog = oeel.shipprod no-lock
break by oeeh.orderno by oeeh.ordersuf by oeel.shipprod:


kohlerprod = if icsc.vendprod = v1 then v1 else "".

if oeel.returnfl then v-qtyship = oeel.qtyship * -1.
if not oeel.returnfl then v-qtyship = oeel.qtyship.

accumulate v-qtyship (sub-total by oeel.shipprod).
v-qtyship# = (ACCUM SUB-TOTAL BY oeel.shipprod v-qtyship).

assign V-Invoice# = string(oeeh.orderno) + "-" + string(oeeh.ordersuf).

if oeeh.takenby = "amj" then do: V-BPN = "3247570". V-BPNfname = "Annie" . V-BPNlname = "Jones". end.
if oeeh.takenby = "dtb" then do: V-BPN = "3190293". V-BPNfname = "David" . V-BPNlname = "Bowie". end.


IF NOT LAST-OF(oeel.shipprod) THEN NEXT.

if cramerprod = "" then next.

export delimiter "~t"
InvoiceDT
Invoice#
SaleDT
ProductSKU
Quantity.
end.
end.
output close.

 

[Cringer]

Please put your code inside code tags so it's easier to see.
CAN-DO is bad practise. It doesn't do what you think. More importantly it will kill your query meaning that you're probably getting whole index reads. Build a temp table with your valid values and add those to the joins and you should see a massive gain. Also, if you compile the code with XREF it'll show you which indexes it's using.

 

[Rob Fitzpatrick]

Doing too much logical I/O to get the desired results could certainly be a bottleneck. You can get an idea of how many logical reads you are doing in your queries by:

• reading the contents of your session's records in _UserTableStat and _UserIndexStat before and after your queries
  • this requires you to have started the database with -tablerangesize and -indexrangesize parameters, with values high enough to accommodate your highest-numbered application table and index
• using the Progress LOG-MANAGER with the QRYINFO log entry type, either programmatically (with the LOG-MANAGER system handle) or as client startup parameters, and reviewing the (rather verbose) output.

Either way, it would be helpful to have a recent proutil dbanalys or tabanalys report to compare against so you know how many records are in the tables in question.

Also, it's somewhat obvious but compare your index components against the elements of and operators in your WHERE clauses. For each query, write down how you think your query is bracketed by the AVM. Then compare those predictions against your actual run-time results, e.g. by using the -zqil client startup parameter and looking at the run-time bracketing information written to the database log. Read this KB article for more info. And, as Cringer indicated, review the COMPILE XREF output of your code and look for any WHOLE-INDEX queries on large tables.

How your client connects to the database could also be a factor in performance. Is it a shared-memory client or TCP? If the latter, other factors come into play like database broker client/server startup parameter values, the throughput of the network path between client and server, etc.

Also take a look at your client temp file sizes (.dbi, .srt, etc.) and see if anything unusual is happening there. High I/O on those files could also be a performance constraint, or indicate an opportunity for application optimization. The statistics client startup parameters (-y, -yd, -yx) can provide useful information about what the client is doing.

 

[TomBascom]

CODE tags would make your code readable.

From the table and index names I'm guessing that the application is SXE.

USE-INDEX plus CAN-DO()? It will be a miracle if it finishes this year.