F0911 add index for performance question

I have searched and read a lot of related posts on F0911 and performance issues. Most of them are AS/400 and Oracle databases, and I am not gaining any insight.

We have a custom report, for inventory component pricing. It selects on our 13 million F0911 records at first. After it prints the detail or summary section, it goes out and does more 'select' and 'fetch next' to get cost variance numbers, again from the F0911.

Now that the report is in production, it takes hours to run.

If I add an index specifically for this report, will I see a performance gain? The other options being bantered around involve changing the business view, which means report re-write.

Input is greatly appreciated.

We were having similiar issues with certain reports running against our F9011 table with 15 million records.
We added indexes for fields specific to these reports with very good results.
It reduced our report the time it took to run reports significantly and reduced MS SQL locking also.

We have added Indexes over our 40Mil record F0911 & got great results with performance.

we have 51mil F0902 records and before the index the financial report took 22 hours to complete with the index it is now 13 hours. I am assuming you guys are finding the best index by looking in the sql statement in the JDEDebug log. Is this correct and are your indecies on the 400? are your reporst financial reports, meaning your using the standard financial bsfn's...??

What was the performance gain? I added an index to the F0902 where we have 51 mil records and my financial Row reoprt went from 22 hours to 13 hours. Thanks curious to know?

Thanks for the input - sorry I was slow to respond to your responses. JKiel - thanks for the pointer to jdedebug.log, I want to be sure - really sure - I am getting the new index definition right as it will take a long time to generate the index on our 13 million records.

I will let you know the outcome(s) when we try it.

Keep in mind that you can also use iSeries Navigator to review the SQL queries being executed in full detail. Also, there is a JDE Performance Workbench tool available for download from Oracle. I don't have the URL at hand. You should be able to find it via Customer Connection or your client representative should be able to provide a link.

Good luck.

-KV

It is really hard to give you an answer on whether adding an index will help or not; without seeing what type of report you are using, the ER code etc. Can you post your ER and what you are trying to achieve in your report. Is there a reason you are not using the Cardex? Move info will help - Thanks Angelis

Actually - it would be better for you to post the Select Statement from the reports JDEDebug.Log.

From the statement we can determine what indexes might help (based on the syntax of the select and the order-by of the statement).

The ER may tell us what the ER is going to 'data-select', but the actual statement from the debug-log will tell us how the ER (and Data-Selection) put the syntax of the statement together.

HEY ERIK! Have you considered creating a forum just for DataBase Issues? Seems to be a lot of interest lately!

(db)

Good idea on new Forum. There may be a couple of others that we see are common. May be big help to all - Thanks

We've added several indexes to the F0902/F0911 to speed up reports with pretty good success. Our DBs are 18mil and 20mil records.

Our biggest speed increase came by added 1 simple field to the query on every financial report we run. GLFY = '6' (or whatever is needed). In ERP8.0 FY = isn't passed as part of the default query setup for FASTRs. Our DB has records since 1995, so when we'd run a report asking for records = X, we'd get 11 years worth of results. By adding FY = to the query we'd get back just the years in question, dropping the return records from 10 mil to 1.2 million for the first report I took a look at. Reports dropped from 12 hours to under 1 hour. Our consultant tried to blame our system hardware for the issue because we refused to buy their over priced goods where in reality it was the report writings writing terrible queries in the reports.

Anyway, make sure you’re using FY = is you have many years worth of data.

Thanks to everyone for all the helpful posts. Sorry for the slow response, year end issues, and some reason, email notifications not coming anymore when I get replies posted.

FIRST = by just adding the Company to the select (CO) it did dramatic improvements. I had the document company KCO, not CO in the selects at first. We actually proved this was accelerating at the DB2 level. Then we incorporated it into the JDE code and it really helped.

SECOND = our admin people are also doing another side project. Our F0911 is in a table space that is nearing the DB2 maximum. They are going to move F0911 to it's own table space, then regenerate the indexes, and see the results (or lack thereof) on performance. We have a dedicated test system that we are using for this.

Lastly - if anyone is interested in pursuing this thread further, I would be glad to assist all of our collective knowledge by providing some of things asked for in your posts.

cheers to all, and thanks for the brain food
Sheen