Help in Select without full key for buffer table

[Former Member]

HI All,

i had table with 3 keys mandt userid and address and... , for this table i need to use select

with key userid in order to get all user data such address (can be more then one per user )etc.

i create the table as single row buffer (since the table are read frequently and i can have 100000 entries on the table )and in the code inspector i get warning for performance

since it is single row buffer,when i change it to genric buffer table with to keys i get also warning ,when i choose on key on the genric buffer 1 the warning disappear.

if i choose one key on the generic buffer i think it take the first key such mandt

and i don't think that this is good from performance aspect.

there an idea how to design it well from performance aspects.

Best Regards

Nina

[Rui_Dantas]

Hello Nina,

Single-record buffering: you don't want that because you won't select with the full key (from what I understand the address is also a key in your table)

Generic area buffering (with two fields): you don't want that because it would create too many small areas; that is not good.

Full buffering: you probably don't want that for 100,000 entries.

So the question is: why do you think you need to use table buffering?

If you switch buffering off and select by customer (pretty selective, I would guess) I don't think you'll have performance problems.

Regards,

Rui Dantas

[Former Member]

HI Rui ,

Thanks,

I want to use buffer tables since i have lot of record (KPI can be until 300000 entries on the table ) ,and the record of the tables

such user data with all his data read frequently by several of program( since the table can have user address such home address email address and so on so specific user can have 5 - 6 entries on the table ....) . i.e. there is lot of access to the method that read user data

and can read every time different user from the table .

so for that i think of using the buffer table.

do you think that i should avoid it ?

Regards

Nina

[Former Member]

the type of buffering is clear, single record can only be used if you specify to full key, which you don't. And fully buffered is not a good idea her.

So try the generic 2 buffering and check the size of the table in transaction ST10 (AL12) afte a while.

I has a lot of records, but tjis does not mean thatall records will be buffered. Only what is used will come into the buffer.

Siegfried

[Former Member]

HI Siegfried ,

Thanks,

i try it (generic with 2 keys )but when i check with code inspector i get warning since in the select i need to provide all the user

data ,so i cant select for user and address since i need to get all the user address.

and when i choose just one key on generic buffer i don't have perfomace problem on the code inspctor but i think it take the first key

which is mandt and i don't know what is the benfit on this case ?

best regards

Nina

[Rui_Dantas]

>

> So try the generic 2 buffering and ...

Hi Siegfried. From what I've always read, too many different areas is not recommended because it will not be efficient (in this case there would be one area per customer, which would mean hundreds of thousands of different areas). Generic area buffering would only be recommended in cases where few areas would be created (for example, language or company, or sth like that).

Do you have a recommendation different that this one?

Rui Dantas

[HermannGahm]

Hi Rui Dantas

> Generic area buffering would only be recommended in cases where few areas would be created (for example, language or company, or sth like that).

>

> Do you have a recommendation different that this one?

no anwer but a few thoughts on that:

how much is few?

Does few depent on nr. of entries or space available in the buffer?

And if so, could few change over time when available hardware changes?

I guess the answer would be that it depends... on configuration(space and entries available).

Additionally there might be limits in the kernel.

update:

i just checked a system with 4GB allocated space (1GB free) in the generic key buffer.

Additionally 6.7 Million Entries (generic areas) are possible and some 700000 are free.

With such a setup i think one could go for the buffering i would say However i would

monitor the impact in any case ...

Kind regards,

Hermann

Edited by: Hermann Gahm on Jul 17, 2009 1:51 PM

[Former Member]

could you copy the SELECT which gives you the warning

[Former Member]

HI,

Select *
from ZEMP_GEN_DATA
into table it_emp_date_out
where employee in it_employee  .

Nina

[HermannGahm]

Hi Nina,

generic buffering with key field one (which is MANDT only) equals FULLY BUFFERED.

your IN condition CAN NOT be processed on single record or generic buffered tables in the buffer.

It will be processed on fully buffered tables in the table buffer however the buffer will be scanned

(full table scan in main memory) since the in will not be optimized internally.

For making efficient use of the table buffer you need a where condition that uses EQ on the key fields.

Kind regards,

Hermann

[Former Member]

HI Hermann,

Thanks,

So maybe i can use fully buffer table, but for that issue i need to calculate the table size ,(since fully buffer table have some limit

size i think ) do u have an idea how i can I calculate the table?

P.S. if the table size dont fits the fully buffer size ,do u have other suggestion how to increase the performance of the read process from the table since the table size is large can be until 300000 records and it read frequently during the day .

Regards

Nina

Edited by: Nina C on Jul 17, 2009 12:26 PM

[HermannGahm]

Hi Nina,

>

> So maybe i can use  fully buffer  table, but for that issue i need to calculate the  table size ,(since fully buffer table have some limit
> size i think ) do u have an idea how i can I calculate the table?

Check if you can rewrite your select statement to EQ conditions on buffer key fields. Otherwise, don't buffer it, since you can not make efficient use of the buffer it will probably be not faster (rather slower) and a wast or ressources and a burden for the system.

However DB05 is used for space calculation in table buffers. Run it for your primary key and you will get this output:

 Date/time of analysis:                  17.07.2009  12:29:07
 Analyzed table:                         T000
 Total number of rows:                              4 rows
 Requested bytes per row:                         107 bytes per row
 Requested bytes to be buffered 100%:             428 bytes
 Current buffering mode:                         100% buffering

With the above SELECT using an in for employee you can only make sure that you get a good database select (index employee).

Kind regards,

Hermann

[Former Member]

Hi Hermann,

To make sure that i got u write ,

IF i can use the select for example with for all entries EQ emt_tab , i can use generic buffer with two fields that will be

probably mandt and employee num?

Best Regards

Nina

[HermannGahm]

Hi Nina,

yes, this select:

Select *

from ZEMP_GEN_DATA

for all entries in emp_tab

into table it_emp_date_out

where employee = emp_tab-employee .

would use a generic buffered table with buffered keys mandt, employee.

Buffering the table may create a lot generic areas in the table buffer:

Carefully monitor with ST02 regarding free space, free entries, and swaps.

The buffering of such a table must not affect the other buffered tables negatively.

Check with ST05 (buffer trace) if the buffering works like it should work.

Kind regards,

Hermann

[Former Member]

> Generic area buffering would only be recommended in cases where few areas would be created (for example,

> language or company, or sth like that).

no, I just she should try what is actually used and not what potentially could be used.

Coming bach to the original question:

@Nina,

you should read the code inspector checks more carefully! I know the code inspector checks quite well.

It gives you not just a warning but tells you more. It should be possible to figure out, that the statement is wrong and not the buffer setting.

Siegfried