Fully Buffered Table vs Internal Table

[Former Member]

I had a question regarding performance of a fully buffered table vs an internal table.

In a BW characteristic routine, the current code selects data off a fully buffered table. This process repeats itself for every record which passed through the routine, which could be millions of records each day.

A possible alternative I was thinking would be to load the table into an internal table, which would only happen once, and the selects would occur off the internal table.

Can anyone recommend how much a performance improvement if any, that change would provide? Any help would be great.

Thanks.

[former_member97254]

Hi John,

I think this depends on the table size, system status, application usage..etc

If I have a table with huge data (millions of records & NOT changed frequently) I would prefer to keep the data in buffer instead of internal table for following reasons:

(1) work processor will have limited memory (ex: 5MB) and if the data is more than the work processor memory limit, then the work processor has to page-out and page-in this data. This might have a big performance bottle neck.

(2) If the data is kept in the buffer then multiple processes can access this data (instead of keeping one copy per one process). This actually depends on your application and its usage. I am not sure about your particular scenario.

If you are looking for performance improvement, you may also want to look at

(1) parallelization of process

(2) statements which bypass buffers and access data base (Ex: aggregate functions like COUNT, MIN..etc)

Best Regards

Madhu

[christian_wohlfahrt]

Hi John,

in addition: why do you think, selects to a fully buffered table might cost a lot of performance. Normally you don't see this selects at all in a SQL trace.

So I guess, there are relatively often changes to this table (BW - can't happen!) or table size is to big for buffer size of your application server.

Like before: there is no general answer. But have a look at FM T001W_SINGLE_READ - there is one answer how to handle fully buffered table selects vs. internal table (SAP switched to select).

Regards,

Christian