Poor performance for the 1st select everyday for AFRU table

[Former Member]

Hello everyone, I have performance problems with AFRU table. Every day, the first time I run a "Z" transaction, it takes around 100-120 seconds, but the second time and following it only takes four seconds. What could I do, in order to reduce the first execution time?

This is the select:

SELECT * FROM AFRU WHERE MANDT = :A0 AND CATSBELNR = :A1 AND BUDAT = :A2 AND PERNR = :A3 AND STOKZ <> :A4 AND STZHL = :A5

The execution plan for this select takes index AFRU~ZCA with an acceptable cost of 6.319. Index AFRU~ZCA is a nonunique index with these colums: MANDT + CATSBELNR + BUDAT + PERNR

I'll appreciate any ideas.

Thanks in advance,

Santi.

[former_member188958]

What database system are you using (ASE, Oracle, etc?).

If ASE, for the general issue of the first exection of a query taking longer, the two most likely reasons would be

a)  the table's data has aged out of cache so the query has to do a lot of physical i/o to read the data back into cache

or

b) the query plan for the query has aged out of statement cache and needs to be recompiled.

This query looks pretty simple, so the data cache seems much more likely.

To get a better feel, some morning run the query with

set statistics io on

set statistics time on

then run it again and look for differences in the physical vs logical i/o numbers and compile vs execution times.

You could use a scheduled event (Job Scheduler, cron job) to run the query or some query very like it a little earlier in the day to prime the data cache with the table data.

[Former Member]

Our systems are under Oracle DB.

[former_member188883]

Hi Ramos,

Is this problem observed for only Z-transaction or SAP transactions as well ?

If it is only for Z-transaction program may require further tuning.

Regards,

Deepak Kori