Skip to Content
author's profile photo Former Member
Former Member

Performance on select join vbap/vbak/vbep

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,

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on May 25, 2005 at 04:10 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 26, 2005 at 06:10 AM

    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 .

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 26, 2005 at 07:05 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 26, 2005 at 07:18 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 26, 2005 at 07:22 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.