◆◆0

[Code Inspector]消除警告信息SELECT Statements That Bypass the Table Buffer

石头 7月 26, 2021 其他 9,553 views 1

写完程序用code inspector工具进行代码检查时发现一个警告信息，

简单的代码如下：

DATA:l_sales_group TYPE tvgrt-bezei.
SELECT SINGLE b~bezei
  FROM vbak AS a
 INNER JOIN tvgrt AS b ON a~vkgrp = b~vkgrp
  INTO l_sales_group
 WHERE a~vbeln = '123456789'
   AND b~spras = 'E'.

DATA:l_sales_group TYPE tvgrt-bezei.

SELECT SINGLE b~bezei

FROM vbak AS a

INNER JOIN tvgrt AS b ON a~vkgrp = b~vkgrp

INTO l_sales_group

WHERE a~vbeln = '123456789'

AND b~spras = 'E'.

警告消息如下：

SELECT Statements That Bypass the Table Buffer

1	SELECT Statements That Bypass the Table Buffer

具体提示为表TVGRT是个buffered表，不应该使用再join SQL中，

Buffered Table TVGRT in a JOIN

1	Buffered Table TVGRT in a JOIN

去SE11查看了表TVGRT的技术设置，果然是个buffered表，

又搜了一些资料如下：

SAP Buffering 
When accessing database tables using Open SQL, SAP buffering is effective as standard if it is activated for
the corresponding database table. It can be explicitly deactivated using the addition BYPASSING BUFFER of the statement SELECT. 

In addition to the explicit specification of BYPASSING BUFFER, SAP buffering can also be bypassed implicitly 
by some variants of the Open SQL statements, as the corresponding operation can only be executed in the database and not 
in the SAP buffer. 

The following Open SQL statements implicitly bypass the SAP buffer and access the database tables directly: 

SELECT with the addition FOR UPDATE, 
SELECT with the addition DISTINCT, 
SELECT with aggregate expressions, 
Access to a table with single record buffering without linked equality conditions specified for all key fields of
the primary key in the WHERE condition with AND, Open SQL statement with the addition CLIENT SPECIFIED, without 
specification of the client ID in a WHERE condition, 
SELECT with JOIN expressions 
Access to a generically buffered area without full specification in a WHERE condition 
Open SQL statements with IS [NOT] NULL in the additions WHERE and HAVING, 
Use of a subquery in a WHERE condition, 
SELECT with the addition GROUP BY, 
SELECT with the addition ORDER BY, whereby the sort key is not the primary key.

SAP Buffering

When accessing database tables using Open SQL, SAP buffering is effective as standard if it is activated for

the corresponding database table. It can be explicitly deactivated using the addition BYPASSING BUFFER of the statement SELECT.

In addition to the explicit specification of BYPASSING BUFFER, SAP buffering can also be bypassed implicitly

by some variants of the Open SQL statements, as the corresponding operation can only be executed in the database and not

in the SAP buffer.

The following Open SQL statements implicitly bypass the SAP buffer and access the database tables directly:

SELECT with the addition FOR UPDATE,

SELECT with the addition DISTINCT,

SELECT with aggregate expressions,

Access to a table with single record buffering without linked equality conditions specified for all key fields of

the primary key in the WHERE condition with AND, Open SQL statement with the addition CLIENT SPECIFIED, without

specification of the client ID in a WHERE condition,

SELECT with JOIN expressions

Access to a generically buffered area without full specification in a WHERE condition

Open SQL statements with IS [NOT] NULL in the additions WHERE and HAVING,

Use of a subquery in a WHERE condition,

SELECT with the addition GROUP BY,

SELECT with the addition ORDER BY, whereby the sort key is not the primary key.

大体意思就是如果在SQL中用到buffered表时，无论是隐式还是显式跳过了buffer都会导致性能上的问题，所以要尽量保证buffered 表都使用到缓存，inner join就是一种隐式bypass buffer的SQL语句。

做下测试，简单的将代码改成2个单独的SQL，用来替代inner join，

DATA:l_sales_group TYPE tvgrt-bezei,
     l_vkgrp       TYPE vbak-vkgrp.
SELECT SINGLE vkgrp
  FROM vbak
  INTO l_vkgrp
 WHERE vbeln = '123456789'
   .
SELECT SINGLE bezei
  FROM tvgrt
  INTO l_sales_group
 WHERE spras = 'E'
   AND vkgrp = l_vkgrp.