ABAP INNER JOIN Performance impact



Business background:

ERP system has a program ZPPR0002, each time the MES system release production orders in the ERP.

The production order data contains the BOM component, which is the 1500 item information that makes up the finished vehicle.

Symptoms:

Recently, business staff feedback production order transmission slow, an order to 2 minutes, 80 orders a day, to finish the transmission will be a few hours.

Reason:

With SE30 debugging, it is found that the program spends 90% of its time on a inner join statement.

The purpose of this statement is to find information for 3 fields and place them in each item information.

The program makes 1500 queries on 1500 items and populates information for 3 fields.

Workaround:

Originally a INNER JOIN statement, the final result is to get a stpo of data, which leaves a space for splitting the statement.

Since the final result is just to get a piece of data, the query that joins the 2 tables of the inner join should be divided into 2 select single.

            "Original code one statement SELECT single            a~potx1 a~potx2 a~sortf to (GS_STPO-POTX1,GS_STPO-POTX2,GS_STPO-SORTF) from StPO as  A            INNER JOIN  resb as B on a~stlnr = B~stlnr and a~stlkn = b~stlkn and a~stpoz = B~stpoz            WHERE b~rsnum = ' 00000 09884 ' and            b~rspos = ' 0001 '.                " Modified code, two statements            SELECT single STLNR stlkn stpoz to (gs_resb-stlnr,gs_resb-stlkn,gs_resb-stpoz) from            resb            WHERE rsnum = ' 0000009884 ' and            rspos = ' 0001 '.            SELECT single POTX1 POTX2 sortf to (gs_stpo-potx1,gs_stpo-potx2,gs_stpo-sortf) from            stpo            WHERE  STLNR = Gs_resb-stlnr and stlkn = gs_resb-stlkn and Stpoz = Gs_resb-stpoz.

Run both pre-and post-modified code, and the performance varies greatly, and the new code hardly takes the time. Medium Red is the original code time, green is the new code time.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

ABAP INNER JOIN Performance impact