How I increase speed of reports that fetch data from database

[dipanshutalwar]

Hi everyone ,
I am new to this and this is my first question in that progress talk..so plz help me.
i made one report to collect data from 3 tables of qaddb database but it takes a lot of time near about 30 min to display output..even though i use proper locking(means NO-LOCK) as well as Indexes also.
so if there is any other way plz tell me that can help me out...

thanks in advance..

 

[joey.jeremiah]

you haven't given us much to go on.

but flat out that's too long, we have an average sized database and i
can go thru the entire database in much less.

of course there are exceptions, but imo as a rule of thumb even
heavy reports should not take more then two, three minutes at most.

both self-service instead of remote client, and logical index scattering
each can easily effect performance by factors of 10's.


post the following -

of course version, and out of interest what app you're using, network
or self-serving connection.

base query, please don't post the entire prog. estimated number of
rows.

table indices, which ones are the primaries, recent dump and load,
and anything else you think might be relevant.

 

[dipanshutalwar]

Hi,
Thanks to reply me... now i share the code that takes time bcoz other for each loops takes 10 seconds to give output but when i plase this code to calculate Po amount including tax amount then report takes 30 min to give output...


FOR EACH tx2d_det WHERE tx2d_nbr = pon AND tx2d_line_site_ent = site USE-INDEX tx2d_ref_nbr NO-LOCK BREAK BY tx2d_nbr BY tx2d_line:
ACCUMULATE tx2d_tax_amt (TOTAL BY tx2d_nbr).
IF FIRST-OF(tx2d_line) THEN
ACCUMULATE tx2d_totamt (TOTAL BY tx2d_nbr).
IF LAST-OF(tx2d_nbr) THEN DO:
txamt = ACCUM TOTAL BY tx2d_nbr (tx2d_tax_amt).
toamt = ACCUM TOTAL BY tx2d_nbr (tx2d_totamt).
finamt = txamt + toamt.
PUT finamt AT 90.
END.
END.

Thanks in advance....

 

[gcampbell]

What are the indices for tx2_det?

Later,
Gordon

 

[dipanshutalwar]

Indices for tx2d_det is..

[FONT=Arial,Bold]

This is the index for tx2d_det. I used tx2d_ref_nbr but it still takes a lot of time..and i am working on progress v9.1D. so what is the other way to increase the speed to fetch data.


Index-Name Uni Field-Name Asc Abbr​

tx2d_carrier no tx2d_carrier yes no​

[/FONT]

​

​

tx2d_ref_nbr yes tx2d_ref yes no
tx2d_nbr yes no
tx2d_line yes no
tx2d_trl yes no
tx2d_tr_type yes no
tx2d_tax_code yes no
tx2d_tax_code no tx2d_tax_code yes no


thanks in advance...​

 

[bulklodd]

FOR EACH tx2d_det WHERE tx2d_nbr = pon AND tx2d_line_site_ent = site USE-INDEX tx2d_ref_nbr NO-LOCK BREAK BY tx2d_nbr BY

I can say you've got a WHOLE-INDEX problem. You use tx2d_ref_nbr index but tx2d_ref field isn't included in your query. IOW to speed up you query you should either include it in WHERE clause or build a new index which includes tx2d_nbr and tx2d_line_site_ent fields.

HTH

 

[RonaldS]

Do not use USE-INDEX let Progress figure it out. It is pretty good at it. Use XREF option in the compiler to check in which order the fields are used.

 

[wysinotwyg]

fields () logic in "for each"

Try using the "fields()" option in your "for each" query. Depending on the size of the database, and the number of fields the improvement can be quite impressive. On a table with 6 fields, and around 2.6 million records, the "for each" search went from about 4 1/2 hours to about 20-30 minutes.

 

[joey.jeremiah]

Try using the "fields()" option in your "for each" query. Depending on the size of the database, and the number of fields the improvement can be quite impressive. On a table with 6 fields, and around 2.6 million records, the "for each" search went from about 4 1/2 hours to about 20-30 minutes.

anytime you're using a remote instead of a self-serving connection you're already in trouble.

due to how progress executes multi-table queries and sort-access on remote connections.

hopefully distributed will phase out client/server architecture sooner then later.

then we can efficiently and easily divide the work on many diff machines.