Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Performance on select join vbap/vbak/vbep

Former Member
0 Kudos

Hello,

I would like to know how improve the performance (time-reponse) of my select :

SELECT

apmatkl approdh apmatnr aparktx apvbeln apposnr apnetwr apkzwi3 apkzwi1 apmwsbp

epbmeng epwmeng epedatu apvrkme ap~brgew

INTO CORRESPONDING FIELDS OF TABLE p_tvbapcache

FROM vbap AS ap

INNER JOIN vbep AS ep ON apvbeln = epvbeln AND apposnr = epposnr

INNER JOIN vbak AS ak ON akvbeln = epvbeln AND apvbeln = akvbeln

WHERE ak~vbeln NOT LIKE '0005%'

AND ep~vbeln NOT LIKE '0005%'

AND ap~vbeln NOT LIKE '0005%'

AND ep~edatu IN date

AND ap~prodh IN nomencla

AND ak~auart IN canal

AND ap~matnr IN numero

AND apnetwr <> 0 AND apnetwr <> '0'

AND epwmeng <> 0 AND epwmeng <> '0'

AND epbmeng <> 0 AND epbmeng <> '0'

AND apkzwi1 <> 0 AND apkzwi1 <> '0'

AND ak~faksk <> 'Z3'.

The performance are very very very poor...

For information, there are 18.329.040 entries in VBAP table; VBAK : 485.437 entries ; VBEP : 18.304.173 entries

Many thanks for yours ideas,

5 REPLIES 5

Former Member
0 Kudos

Dear Coppalle:

Couple of things:

1. You can split the SQL into 2 different ones. The first one can be executed against view VIVEDA in which the WHERE conditions can cover VBAP and VBAK related checked. From the selected records retrieved, run the records against the remaining table i.e. VBEP to get the rows that you want.

2. Try replacing <> 0 and <> '0' conditions with > 0 option.

Statements like 'NOT LIKE' and 'IN' are costly in terms of execution time - and especially when you are joining 3 tables in one shot, it is not a surprise that the execution time is very slow. As I mentioned in point 1, try to retrieve details first using the view so that you can perform the filterning of records from internal table etc fast rather than at the time of retrieving records.

Hope it helps.

Regards,

Chetan Singh

jayanthi_jayaraman
Active Contributor
0 Kudos

Hi,

I suggest to select records from VBAK first and then use for all entries(vbeln) in VBAP for that first internal table and then use for all entries in VBEP(vbeln and posnr) for the second internal table .

Former Member
0 Kudos

Hi Coppalle,

Sometimes you need to have a bit more confidence.

Your WHERE-clause checks VBELN for all tables, but in an inner join they are already checked if VBELN is used in the relation.

To check number fields <> 0 <u>AND</u> also <> '0' is not required. Use either one (what you find easy to use), allthough <> 0 is slightly faster.

Since many records are in your tables, consider the following program detail.

DATA: p_tvbapcache_buf LIKE p_tvbapcache OCCURS 0,
      w_tvbapcache_buf LIKE p_tvbapcache.
SELECT ap~vbeln ap~posnr ap~matnr ap~matkl ap~prodh ap~arktx
       ap~netwr ap~kzwi3 ap~kzwi1 ap~mwsbp ap~vrkme ap~brgew
       ep~bmeng ep~wmeng ep~edatu
  INTO CORRESPONDING FIELDS OF TABLE p_tvbapcache_buf
  FROM vbap AS ap PACKAGE SIZE 10000
 INNER JOIN vbep AS ep
    ON ap~vbeln = ep~vbeln
   AND ap~posnr = ep~posnr
 INNER JOIN vbak AS ak
    ON ak~vbeln = ep~vbeln
   AND ap~vbeln = ak~vbeln
 WHERE ak~auart IN canal
   AND ak~faksk <> 'Z3'.
  LOOP AT p_tvbapcache_buf INTO w_tvbapcache_buf.
    CHECK: vbeln NOT LIKE '0005%',
           prodh IN nomencla,
           matnr IN numero,
           netwr <> 0,
           kzwi1 <> 0,
           edatu IN date,
           wmeng <> 0, 
           bmeng <> 0.
    APPEND w_tvbapcache_buf TO p_tvbapcache.
  ENDLOOP.
ENDSELECT.

And as last suggestion: I see that you use a lot of 'NEGATIVE' selections (this is selection by exclusion). In the database world this is a very bad way of selecting data, because it will not follow any key in a table. In my opinion you try and find a way of using at least a selection that can use a key.

Hope this gives you some directions,

Regards,

Rob.

johan_geraedts
Explorer
0 Kudos

Hi,

Instead of using the number range of the document, (WHERE ak~vbeln NOT LIKE '0005%'...)

- try the use the VBAK-AUART to include or exclude a kind of documents.

- or try to use VBAK-TRVOG to include/exclude a kind of documents

- or build a range - see help on range statement.

Good luck,

JG

johan_geraedts
Explorer
0 Kudos

Me again:

- if the material number is mandatory in your selection screen... use table VAPMA first to retrieve documents and you as well can use the field TRVOG to include/exclude a kind of documents...

JG