Faster Record Retrieval in Application in Second try

[jdetechie07]

Hello,
I am calling a custom item search application from the subledger field on PO Entry form.When the user clicks on the torch light it opens up the item search form and brings up a list of items.The issue is when this is done for the very first time in the day it takes a loooong time to load the initial screen.Subsequent clicks on the subledger button result in very quick item search.Page at a time processing is enabled.Question is why does JDE takes such a long time to fetch anything for the first time and then pretty quickly later on.Does it build a temporary index or something for subsequent fetches ? How to find that out ?
My database is DB2.JDE version 8.10, tool release 9.1
Item branch table on which the app is based has some 20 million records.The only filter on the application is on MCU.
Thanks in advance.

 

[Larry_Jones]

Well, you say its a custom search app. And its only slow the first time its used in a day. So I'm guessing the app has a complex query or hits very large tables or both. Since its only slow the first time that would indicate to me that caching (database?) is improving the response time on subsequent queries. Not familiar with DB2 but by chance is the database server brought down each night for backups ...?

PS: I don't think you can be running JDE 8.10 with Tools release 9.1. Check again.

 

[jdetechie07]

Thanks for the reply Larry.My tools release is 8.96.2.3 and not 9.1.Thanks for catching that.
The DB is not bought down every night but we do have citrix deployment every day at noon.Once at noon this application slows down for the first time, then it does some internal caching and the performance becomes fast.
Any Db2 experts care to chime in ?

 

[daveschultz]

Not a DB2 expert, but we had a similar issue. We ended up resolving it by adding an index. The other suggestion was to find out what SQL query executes and schedule it to run at the beginning of the day so that it caches. This was on an Oracle Database.

Dave

 

[jdetechie07]

Thanks Dave I looked at the index advisor in DB2 where it suggests what indexes that the table needs.
Tried to add those indexes in Dev but doesn't seem to help.
Once user does a find for the first time in the app the response is very good.There is some kind of internal caching that it does need to figure that.

 

[Remo Williams]

Offtopic: Damn Larry, that's one sick avatar!

 

[craig_welton]

We had a similar issue after moving to 9.0 and web clients. The runtime engine was issuing SQL statements like:
SELECT * FROM PY900DTA.F0911 WHERE (1 = 0)
when first running an app after a web server restart. This "query" took forever.

Adding the setting IGNORE_DERIVED_INDEX *YES to the QAQQINI in QUSRSYS fixed the problem.

There should be some IBM details on the setting along with what O/S release you are on.

Craig