ABAP 不要以为会JOIN就显得SQL语句很高明
最近用户用一个报表查看采购订单的修改记录，结果运行了30分钟还没出来，选择条件如下：
而查看其源代码，是这样写的SQL语句：用EKKO的EBELN关联CDHDR的objectid，还join EKPO，join MARA可想而知数据量有多大。此语句系统应该会先找CDHDR 中所有 objectid 与 EKKO的所有采购订单EBELN 匹配，找出的结果再排除 在修改日期在20190901 到 20190930之间的，当然还要匹配EKPO，MARA，系统是怎么个运行机制不得而知，反而这样看似高明的SQL语句，30分钟内无结果，后来强行中止，也就是不知要运行到什么时候。
我认为在开发 SAP 修改记录时，一定要根据屏幕选择条件分别写SQL语句。以上查询条件，输入了修改日期，则先要从CDHDR中找这个修改日期之内的 objectid 等，再根据此 objectid 找 EKKO 和 EKPO。
然后根据EKKO,EKPO数据过滤下CDHDR，因为当按修改日期查CDHDR时，会找到很多 objectid ，而查找EKKO,EKPO时还有另一个条件 即WERKS 工厂，所以这些 objectid 即对应EKKO中的EBELN字段，不一定是选择工厂下的。
IF NOT s_udate[] IS INITIAL OR NOT s_uname[] IS INITIAL. "当选择了修改日期，则先找CDHDR，再找EKKO,EKPO
PERFORM select_cdhdr TABLES lt_cdhdr USING 'X'.
PERFORM select_po TABLES lt_cdhdr USING 'X'.
ELSE. "否则先找EKKO,EKPO,再找CDHDR
PERFORM select_po TABLES lt_cdhdr USING ''.
PERFORM select_cdhdr TABLES lt_cdhdr USING ''.
ENDIF.
PERFORM filter_cdhdr TABLES lt_cdhdr. "过滤CDHDR
*&---------------------------------------------------------------------*
*& Form SELECT_CDHDR
*&---------------------------------------------------------------------*
FORM select_cdhdr TABLES pt_cdhdr STRUCTURE cdhdr USING p_evn.
IF p_evn = 'X'.
SELECT objectclas
objectid
changenr
username
udate
utime
tcode
planchngnr
act_chngno
was_plannd
change_ind
langu
FROM cdhdr
INTO CORRESPONDING FIELDS OF TABLE pt_cdhdr
WHERE objectclas EQ 'EINKBELEG'
AND objectid IN s_ebeln
AND udate IN s_udate
AND username IN s_uname.
ELSE.
DATA:BEGIN OF ls_ebn,
objectid TYPE cdhdr-objectid,
END OF ls_ebn.
DATA lt_ebn LIKE STANDARD TABLE OF ls_ebn.
IF gt_po[] IS INITIAL.
EXIT.
ENDIF.
lt_ebn[] = CORRESPONDING #( gt_po[] MAPPING objectid = ebeln ).
SORT lt_ebn.
DELETE ADJACENT DUPLICATES FROM lt_ebn.
SELECT objectclas
objectid
changenr
username
udate
utime
tcode
planchngnr
act_chngno
was_plannd
change_ind
langu
INTO CORRESPONDING FIELDS OF TABLE pt_cdhdr
FROM cdhdr
FOR ALL ENTRIES IN lt_ebn
WHERE objectclas EQ 'EINKBELEG'
AND objectid = lt_ebn-objectid
AND udate IN s_udate
AND username IN s_uname.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SELECT_PO
*&---------------------------------------------------------------------*
FORM select_po TABLES pt_cdhdr STRUCTURE cdhdr USING P_EVN.
DATA:BEGIN OF ls_ebn,
ebeln TYPE ekko-ebeln,
END OF ls_ebn.
DATA lt_ebn LIKE STANDARD TABLE OF ls_ebn.
IF P_EVN IS INITIAL.
SELECT
ekpo~ebeln
ekpo~matnr
ekpo~txz01
ekpo~ebelp
ekpo~pstyp
ekpo~elikz
ekpo~loekz
ekko~lifnr
FROM ekko
JOIN ekpo ON ekko~ebeln = ekpo~ebeln
JOIN marc ON marc~matnr = ekpo~matnr
INTO CORRESPONDING FIELDS OF TABLE gt_po
WHERE ekko~ebeln IN s_ebeln
AND ekko~bsart IN s_bsart
AND ekpo~matnr IN s_matnr
AND ekpo~werks = s_werks
AND marc~dispo IN s_dispo.
ELSE.
lt_ebn[] = CORRESPONDING #( pt_cdhdr[] MAPPING ebeln = objectid ).
SORT lt_ebn.
DELETE ADJACENT DUPLICATES FROM lt_ebn.
SELECT
ekpo~ebeln
ekpo~matnr
ekpo~txz01
ekpo~ebelp
ekpo~pstyp
ekpo~elikz
ekpo~loekz
ekko~lifnr
FROM ekko
JOIN ekpo ON ekko~ebeln = ekpo~ebeln
JOIN marc ON marc~matnr = ekpo~matnr
INTO CORRESPONDING FIELDS OF TABLE gt_po
FOR ALL ENTRIES IN lt_ebn
WHERE ekko~ebeln = lt_ebn-ebeln
AND ekko~bsart IN s_bsart
AND ekpo~matnr IN s_matnr
AND ekpo~werks = s_werks
AND marc~dispo IN s_dispo.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FILTER_CDHDR
*&---------------------------------------------------------------------*
FORM filter_cdhdr TABLES pt_cdhdr STRUCTURE cdhdr.
DATA ls_cdhdr LIKE LINE OF gt_cdhdr.
SORT gt_po BY ebeln.
LOOP AT pt_cdhdr.
READ TABLE gt_po WITH KEY ebeln = pt_cdhdr-objectid BINARY SEARCH TRANSPORTING NO FIELDS.
IF sy-subrc = 0.
ELSE.
DELETE pt_cdhdr.
CONTINUE.
ENDIF.
ENDLOOP.
ENDFORM.
过滤CDHDR后，接下来要查找CDPOS，即修改明细，此时就很快了，因为都 是根据表关键字段查找。经过以上简单优化后，同样的条件 只需2秒出结果。