topic buffered table in a join in Application Development and Automation Discussions

buffered table in a join

geetha_k — Thu, 26 Apr 2012 10:03:17 GMT

Hi,

We had developed a Customised program to get mail those who are separated or transfered one payroll area to another payroll area. Its working fine. But when performance checking in code inspector its showing some warning messages like "Buffered Table T001P in a JOIN" . I have wrote code like this,

   SELECT pernr endda begda aedtm a~werks a~btrtl name1 c~btext INTO TABLE it_prsarea FROM pa0001 AS a INNER JOIN t500p AS b
          ON a~werks = b~persa
          INNER JOIN t001p AS c
          ON a~werks = c~werks AND
             a~btrtl = c~btrtl

          WHERE a~pernr = wa_0001-pernr.

How i will remove these warning message. Give me suggessions.

Re: buffered table in a join

Former Member — Thu, 26 Apr 2012 10:07:26 GMT

If you are using a join, then the Buffering will be of no use as it will hit the database layer,

Please try using BYPASSING BUFFER in the select statement and check if you are still getting a warning.

Re: buffered table in a join

former_member209818 — Thu, 26 Apr 2012 10:11:10 GMT

why do you need a Text table in a Join Query. It will deteriorate your query performance. Instead, you do you selection directly from PA0001 and then create a temp internal table of those contents. Delete duplicate from that temp table and find the text from T001P separately.. which can be further updated in your original internal table.

Re: buffered table in a join

geetha_k — Thu, 26 Apr 2012 10:11:17 GMT

Hi,

I had tried using BYPASSING BUFFER . But no use of that. Stiil warning message is coming.

Re: buffered table in a join

ThomasZloch — Thu, 26 Apr 2012 10:17:30 GMT

The main options I see are:

remove T001P from the join and later do a select single on it inside a loop (OK for buffered tables) or with some FOR ALL ENTRIES construct
deactivate the warning with a comment "#EC CI_BUFFJOIN
just live with the warning

Somehow I don't really like option 1, since this makes the code more complex, as I assume you want all data in that one final internal table.

Depending on the number of rows in T001P, this might not present a problem at all, so 2 could be ok, if 3 is not an option.

Let's see what the experts say.

Thomas

Re: buffered table in a join

Former Member — Thu, 26 Apr 2012 10:27:45 GMT

If you check the Code inspector message you can see that you can hide thee error message using #EC .

Please check the log completely.

Re: buffered table in a join

yuri_ziryukin — Thu, 26 Apr 2012 11:58:43 GMT

Hello Geetha,

the options that Thomas proposed above should be evaluated based on the fact, how the table T001P is accessed in your query and how large it is.

If the table is very small and fits into 1-2 blocks and if "full table scan" is selected in access path, then it should not be an issue from the performance point of view. This table block will be in memory and only one access will be necessary. I don't see a reason to split the JOIN and use option 1. Option 2 will be ok.

But, if the table T001P has many hundred or thousands of entries, and index access + table block access are required, then option 1 may be better from the performance point of view. And it will automatically resolve the message.

I'd actually compare the performance of both variants directly in your system.

Yuri

Re: buffered table in a join

Former Member — Thu, 26 Apr 2012 12:27:15 GMT

First off all you should understand that if a buffered table appears in a join then the SAP table buffer is bypassed because the buffer does not support join.

The keyword BYPASSING BUFFER explicitly bypasses the buffer, how should that help?

Note, nearly all tables with Tnnn... with n numbers are buffered tables, i.e T500p and T001p are buffered.

The recommended solution would be a SELECT/ENDSELECT on PA001 and inside two select single on the buffered tables. But add a comment otherwise somebody else will try to fix the nested selects.

Performance impact is probably small, as these 2 tables are quite small.

Siegfried