Performance Issues for SELECT in batch job

[Missschaaa]

Hello guys,

I know there are many opinions about the topic but I'm giving it a try 🙂

I'm using a custom report which selects data from tables BSIS and BSAS with non-key field PROJK. This report runs every hour in a batch job.

For optimizing the performance I replaced 'FOR ALL ENTRIES' statement in the SELECT with a self-defined range of wbs elements in the WHERE condition.

Now I'm wondering about what happened. Before the optimization the job runs about 20-30 minutes every hour. The first time with my new logic the jobs ran 40 minutes, but then, after the first run of new logic, the jobs constantely runs only 2 minutes.

How is this possible? The only explanation can be the buffering option? But why does the buffering option only happens with the range in the WHERE condition and not in the FOR ALL ENTRIES statement?

And if its really because of the buffer, what is the trigger for this? On what time the buffer gets resetted?

Any opinions? 🙂

Regards

Michael

[pokrakam]

I can't give you a 100% answer without looking at your scenario, but I can speculate on a few possible explanations...

Alternative 1: For All Entries will read your criteria table and generate one or more SQL statements with an OR clause for each entry. So the DB will likely treat this as a semi-random selection of rows and might decide not to buffer a whole bunch of individual records. With a range it may be treating it as a group, and something worth buffering.

Alternative 2: Even if it is a non-key field, it may be included in an index. If you look at explains in the SQL trace, you may see that the DB will often use an index even if the primary keys are not part of the query. This is especially the case if the result set can entirely be provided by the index because an index is always smaller than the table.

Alternative 3: Who knows? 🙂

Run both queries with an SQL trace (ST05) and have a look at the EXPLAINs, I'm sure it will be informative, post the results back if not clear.

[SuhaSaha]

I will be really surprised if buffering is activated for BSIS/BSAS, because buffering shouldn't be activated for tables containing application data!

[horst_keller]

It is documented ...

"But why does the buffering option only happens with the range in the WHERE condition and not in the FOR ALL ENTRIES statement?"

https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abenbuffer_restric...

"And if its really because of the buffer, what is the trigger for this? On what time the buffer gets resetted?"

https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abenbuffer_synchro...

[Missschaaa]

But is it really because of the buffer? As Suas already said, BSIS and BSAS are not activated for buffering in the ddic table definition. So is there an additional buffer on the server itself?

Regards

Michael