topic Re: Regarding Table Buffering.. in Application Development and Automation Discussions

Regarding Table Buffering..

Former Member — Thu, 21 May 2009 13:08:37 GMT

Hi,

I have a requirement to get the data from SKAT table i.e - Text field (TXT20) for the corresponding GL Account.

My issue is I have to get the TXT20 for the corresponding GL accounts selected into an internal table.

But as SKAT table is buffered it has performance issue..

I have to use..select single...

Please let me know how to do that..

To get TXT20 from SKAT for the corresponding GL Accounts in the internal table.

Reagrds,

Priyanka.

Re: Regarding Table Buffering..

Former Member — Thu, 21 May 2009 13:18:47 GMT

Hi,

Use below code

PARAMETERS: 
COA LIKE T004-KTOPL DEFAULT 'CAEK',

SKA1 = SPACE.

SELECT * FROM SKA1 WHERE KTOPL = COA.

SELECT SINGLE * FROM SKAT WHERE SPRAS = 'E' AND
  KTOPL = COA AND
  SAKNR = SKA1-SAKNR.

IF SY-SUBRC = 0.
  ITAB-TXT20 = SKAT-TXT20.
ENDIF.

Re: Regarding Table Buffering..

Former Member — Thu, 21 May 2009 13:21:20 GMT


> But as SKAT table is buffered it has performance issue.

Buffering is done to improve performance.

Rob

Re: Regarding Table Buffering..

Former Member — Thu, 21 May 2009 13:26:02 GMT

Hi Rob,

But my Code Inspector is throwing warning saying..

Select single record ad it is buffered table.

Please let me know how to remove that warning.

Priyanka..

Re: Regarding Table Buffering..

Former Member — Thu, 21 May 2009 13:41:53 GMT

It gives no errors or even warnings when I try it. Are you using the full primary key in the SELECT?

Rob

Re: Regarding Table Buffering..

Former Member — Thu, 21 May 2009 13:48:44 GMT

Hi Rob,

I am taking the HKONT values from bseg into an internal table.

Select spras ktopl saknr txt20 into table it_skat

for all entries of it_bseg

where spras = 'E'

and ktopl = 'PCOA'

and saknr = it_bseg-hkont.

This is my query...

Priyanka...

Re: Regarding Table Buffering..

Former Member — Thu, 21 May 2009 14:08:01 GMT

I guess if you are not using SELECT SINGLE, it will give this warning. I'd ignore it.

Rob

Re: Regarding Table Buffering..

Former Member — Thu, 21 May 2009 14:12:37 GMT

Hi Rob,

How can I do that using select single?

Also can u please let me know how to reduce the Data Base Performance.

Regards,

Priyanka.

Re: Regarding Table Buffering..

Former Member — Thu, 21 May 2009 14:30:05 GMT

This gives no warnings:

LOOP AT it_bseg.
  SELECT SINGLE spras ktopl saknr txt20
    FROM skat
    INTO it_skat
    WHERE spras = 'E'
       AND ktopl = 'PCOA'
       AND  saknr = it_bseg-hkont.
  IF sy-subrc = 0.
    APPEND it_skat.
  ENDIF.
ENDLOOP.

But performance will be worse if you do it this way. That's why I suggested ignoring the warning.

Rob

This is more a performance question, so I'm moving it there.

Edited by: Rob Burbank on May 21, 2009 10:30 AM

Re: Regarding Table Buffering..

HermannGahm — Thu, 21 May 2009 15:34:46 GMT

Hi Rob,

Hi Priyanka,

in my system SKAT is single record buffered.

The ONLY way to read from single record buffer up to 7.0 EhP1 is

to use a SELECT SINGLE and the fully specified primary key (MANDT, SPRAS, KTOPL, SAKNR).

So actually this:

 
LOOP AT it_bseg.
  SELECT SINGLE spras ktopl saknr txt20
    FROM skat
    INTO it_skat
    WHERE spras = 'E'
       AND ktopl = 'PCOA'
       AND  saknr = it_bseg-hkont.
  IF sy-subrc = 0.
    APPEND it_skat.
  ENDIF.
ENDLOOP.

should be faster than this (10 times or even more):

 
Select spras ktopl saknr txt20 into table it_skat
for all entries of it_bseg
where spras = 'E'
and ktopl = 'PCOA'
and saknr = it_bseg-hkont.

because the FAE (FOR ALL ENTRIES) does bypass the table buffer.

Of course it will be only faster when the buffer is filled. There fore run

it several times if you want to compare it.

Try it and post the result.

You can use ST05 SQL Trace and ST05 Table Buffer Trace to see

which OPEN SQL statement uses the buffer and which statement

bypasses it.

Kind regards,

Hermann

Re: Regarding Table Buffering..

Former Member — Thu, 21 May 2009 15:56:25 GMT


> ...
> should be faster than this (10 times or even more):

Hmmmm..... I think that's right Hermann. Mea Culpa

But wouldn't bseg_int have to be sorted in hkont order for this to be effective?

And I don't think that is even right - sorting shouldn't make a difference.

Rob

Edited by: Rob Burbank on May 21, 2009 12:37 PM

Re: Regarding Table Buffering..

Former Member — Thu, 21 May 2009 21:17:42 GMT

OK - enough of this being wrong all the time. I did a quick test to find out.

The SELECT SINGLE/APPEND/ENDSELECT ran about 3 times faster in a development environment than FAE whether or not IT_BSEG was sorted.

However, in a test environment, the FAE was slightly faster!.

But I also tried another method - the one which I normally use in this sort of situation. Instead of doing the SELECT SINGLE/APPEND/ENDSELECT for each entry in IT_BSEG, I first used a binary search on IT_SKAT to see if it had already been retrieved. If it had, I didn't bother SELECTing again. This ran about 4 times faster than the other two methods (and would get rid of the warning from the code inspector).

Rob

Re: Regarding Table Buffering..

HermannGahm — Thu, 21 May 2009 23:26:39 GMT

Hi Rob,


> However, in a test environment, the FAE was slightly faster!.
> Rob

that is quite interesting.

Have you double checked with ST05 that the SELECT SINGLE uses the

table buffer? In ST05 you should have yellow lines for FAE and

blue lines for SELECT single, if you switch on ST05 SQL Trace and ST05

Table Buffer Trace at the same time. The SELECT SINGLE should be faster...

if it uses the SAP table buffer (blue line). Could it be that your SELECT SINGLE

couldn't use the SAP table buffer e.g. because the SKAT table was not in it?

If you could provide the details showing the the faster DB Access compared to the

SAP table buffer acces I would be keen to get more details from you.


> But I also tried another method - the one which I normally use in this sort of situation. Instead of doing the SELECT SINGLE/APPEND/ENDSELECT for each entry in IT_BSEG, I first used a binary search on IT_SKAT to see if it had already been retrieved. If it had, I didn't bother SELECTing again. This ran about 4 times faster than the other two methods (and would get rid of the warning from the code inspector).
> Rob

yes.

Talking about buffered Access (efficient key accesses, no linear serach):

- internal tables are faster than the SAP Table buffer

- SAP table buffer is faster than buffered DB (cache)

- buffered DB (cache) is faster then unbuffered DB

that is at least the theory and it matches my experience so far.

That's why I'm so interested in your faster FAE... .

Thank you already in advance.

Note:

Buffering in internal tables (in the internal mode) is the fastest. But it is as well

the buffer mode which needs the most memory. Since in the internal mode the

memory for table sktat is allocated per USER (executing the Program). For the SAP table buffer

the memory for the table skat is allocated once in apllication server level (not per user).

Kind regards,

Hermann

Re: Regarding Table Buffering..

HermannGahm — Thu, 21 May 2009 23:56:44 GMT

Hi Rob,

i just did it myself here.

For the attached trace you can see:

- REOPEN / FETCH pairs for DB Access (403 micro seconds, 207 micro seconds and 227 micro seconds...)

- the READ SI for the SAP Table Buffer acces. (30 micro seconds, 18 micro seconds, 21 micro seconds...)


Duration  Obj. name  Op.     Recs. RC     Statement

        8 SKAT       REOPEN             0 SELECT WHERE "MANDT" = '100' AND "SPRAS" = '1' AND
      395 SKAT       FETCH       5   1403
       13 SKAT       READ SI     1      0 P 36 1001C0010010010100
        5 SKAT       READ SI     1      0 P 36 1001C0010010010200
        4 SKAT       READ SI     1      0 P 36 1001C0010010110000
        4 SKAT       READ SI     1      0 P 36 1001C0010010110001
        4 SKAT       READ SI     1      0 P 36 1001C0010010110002
        7 SKAT       REOPEN             0 SELECT WHERE "MANDT" = '100' AND "SPRAS" = '1' AND
      200 SKAT       FETCH       5   1403
        4 SKAT       READ SI     1      0 P 36 1001C0010010010100
        3 SKAT       READ SI     1      0 P 36 1001C0010010010200
        4 SKAT       READ SI     1      0 P 36 1001C0010010110000
        4 SKAT       READ SI     1      0 P 36 1001C0010010110001
        3 SKAT       READ SI     1      0 P 36 1001C0010010110002
        6 SKAT       REOPEN             0 SELECT WHERE "MANDT" = '100' AND "SPRAS" = '1' AND
      222 SKAT       FETCH       5   1403
        5 SKAT       READ SI     1      0 P 36 1001C0010010010100
        4 SKAT       READ SI     1      0 P 36 1001C0010010010200
        4 SKAT       READ SI     1      0 P 36 1001C0010010110000
        4 SKAT       READ SI     1      0 P 36 1001C0010010110001
        4 SKAT       READ SI     1      0 P 36 1001C0010010110002
...

ST12 result:

Form DATABASE_ACCESS = 1517 microseconds. (for 5 times FAE)

Form DATABASE_ACCESS = 225 microseconds. (for 5 times SELECT SINGLE)


__ Call                                              No.   Gross =    Net Gross (%) Net (%)

   Form DATABASE_ACCESS                                5  1,517  = 1,517      13.0    13.0
   Select SKAT                                         5  1,453  = 1,453      12.5    12.5
   ...
   Form SAP_TABLE_BUFFER_ACCESS                        5    225  =   225       1.9     1.9
   Select Single SKAT                                 25    152  =   152       1.3     1.3

You should get similar results.

KInd regards,

Hermann

Re: Regarding Table Buffering..

Former Member — Fri, 22 May 2009 09:13:46 GMT

for single record buffered tables you MUST use


LOOP at itab
   SELECT SINGLE
                 FROM dbtab
                 WHERE ... fully specified key with equal conditions
ENDLOOP.

It will be much faster than any other access. But when yoz measure, you should not measure

how the buffer is filled!

that is also a reason, why AVOID SELECT SINGLE IN A LOOP is nonsense!

it always depends.

Siegfried

Re: Regarding Table Buffering..

Former Member — Fri, 22 May 2009 14:51:07 GMT


> Have you double checked with ST05 that the SELECT SINGLE uses the 
> table buffer? In ST05 you should have yellow lines for FAE and
> blue lines for SELECT single, if you switch on ST05 SQL Trace and ST05
> Table Buffer Trace at the same time. The SELECT SINGLE should be faster...
> if it uses the SAP table buffer (blue line). Could it be that your SELECT SINGLE
> couldn't use the SAP table buffer e.g. because the SKAT table was not in it?

Well, the FAE entries are in bright yellow, but the SELECT SINGLE entries are in dull yellow. SKAT is single record buffered and we are in a DB2 environment.

Rob

Re: Regarding Table Buffering..

Former Member — Fri, 22 May 2009 15:02:05 GMT

> the FAE entries are in bright yellow, but the SELECT SINGLE entries are in dull yellow

meaning that they appear in the SQL trace, which is actually the database trace.

The SELECT SINGLE should disappear from the db-trace and move to the buffer trace (must be switched on), then they are blue (dull and bright :).

I did not look up the table, but here SELECT SINGLE means the primary key must be specified,

not just any key with SELECT SINGLE, otherwise single-record buffer will not be accessed.

Siegfried

Re: Regarding Table Buffering..

Former Member — Fri, 22 May 2009 15:33:50 GMT

Well, I'd never used the bufrer trace, so I'm learning something today! But the FAE DB trace remained in bright yellow; nothing for the SELECT SINGLE DB trace. Both buffer traces were in bright blue.

Full primary key specified in both cases.

Rob

Re: Regarding Table Buffering..

HermannGahm — Fri, 22 May 2009 16:14:21 GMT

Hi Rob,

all yellow lines = statements are sent to the DB.

all blue lines = statements are not sent to the DB but executed in the SAP table buffer.

you should find FAE lines = yello because they can not use the table buffer and the select single lines in blue because they should use the table buffer (if full key is specified and SINGLE is in the statement and the record searched for is in the buffer.)

You can as well put each Statement in a form and run SE30. If nothing else but the statement itself is in the form you can use the gross time of the form for performance comparison like i did.

select single on buffered entries should be much faster.

Kind regards,

Hermann

Re: Regarding Table Buffering..

Former Member — Fri, 22 May 2009 16:22:34 GMT

I wonder if it has something to do with FAE eliminating duplicates. My it_bseg has 10,000 entries, but it_skat only 68 for FAE, but 10,000 for SELECT SINGLE.

Rob