I have a report that takes about 3 hours to run for a 9 month period. I have read through the report optimization info and didn't find any help. When I have tried to use indices for the various tables, I have too many unknowns to make them work. I have attached my code. Any suggestions?

Hello Sharon,

Ok, first of all, I will have to assume that your user-input (selection range on screen) is for one or a few ledger accounts, because your main query on tfgld106 is on index3, which is ledger account first, followed by transaction type, doc.nr etc. If this is not the case, then you should select on a different index. I.e. if normally the selection is for instance for all ledger accounts but for one year, then you should take index2. If it is for certain transaction types/document ranges only, you should take index1, etc. This is as for the main query on tfgld106.

As for all the other queries in your script: two very important optimizers are missing and are possibly causing a serious delay (also depending on your database and its driver level..):

1) you are not specifying a specific index. Result is in the worst case that no index is considered, and in the best case that the database driver will determine for itself which index to use based on the fields in your where-clause. So you should always check which index you want to work with, and then make sure this index will indeed be used. The best way o ensure that, is to force the query with 'order by ttpppnnn._indexX' (after the where-clause). Search the Baanboard on this topic if you require more information.

2) In all your sub-queries you seem to need/require only one record. If so, you should limit your query by adding "as set with 1 rows" just before the selectdo-clause. This stops execution of the query after 1 row has been found, and therefor will reduce execution time when waiting for the database to search for more records meeting the query conditions.

Good luck, and let us know the results please...

Regards,
Eli Nager

Thank you for the advice. I added indices to all but the 410 table. Whenever I tried to add it there I got no results. My new code is below. Now it takes an hour and 45 minutes to report on 9 periods, and 45 minutes to report on 3. This just seems way to long. All this work only to put the check number on an existing report!

Ok, so we reduced already 40% execution time... I looked at your new source, I see that
a) you still didn't add the 'as set with 1 rows' except for to the last function.
b) I don't know what you mean with 'you got no result when you added order by clause for tfgld410
c) you didn't yet answer my question about the main query/selection: is your selection on form normally for one or a limited amount of ledger accounts, or is it for all ledger accounts of 1 year or so? If the latter, you should use a different index on your main query.

To conclude, I just want to remind you of the fact that you are querying very large tables, so if the range selection is also very large, it is normal that the execution requires some time.

Please apply all my suggestions, and run the session again. Then let us know the result.

Kind regards,
Eli Nager

Thank you for your reply. I realize I am querying very large tables and that it will not be a 30 second run.

It will be run for one ledger account only.

"As set with 1 rows" will only work on tables tdpur046 and tfcmg110. The other tables all require multiple pulls.

Table tfgld410 is a huge table with only two indicies, and when I use either of them the session no longer fills the variables (according to the debugger). It is also obvious that this table and tfacp200 are the bottlenecks. Are there better tables to get the same information? I don't know of a shorter route to get to the check number!

This report is to satisfy auditors at our Mexico facility, and may have to be run after hours.

Sharon,


I didn't look at the purpose of your script, just at the content.. Right now I have no time to look at your purpose and possible ways to optimize the "path", will try to look at that later if I have a few minutes.

About the as set with 1 rows: this doesn't make sense to me. Per record fetched from tfgld106, you query all the other tables, and all you do is assigning the temp values in their selectdo statements, thus my conclusion is that you need only one record per sub-query. Please look at it again, and let me know about that.

Kind regards,
Eli Nager

As shown from my spreadsheet, from tfgld106 I get 3 records, resulting in 3 records from tfgld410. These then each pull one record from tdpur046, which then pulls 5 records from tfacp200, resulting in one record from 110.