topic Re: Performance on select join vbap/vbak/vbep in Application Development and Automation Discussions

Performance on select join vbap/vbak/vbep

Former Member — Wed, 25 May 2005 15:39:02 GMT

Hello,

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

SELECT

ap_{matkl ap}prodh ap_{matnr ap}arktx ap_{vbeln ap}posnr ap_{netwr ap}kzwi3 ap_{kzwi1 ap}mwsbp

ep_{bmeng ep}wmeng ep_{edatu ap}vrkme ap~brgew

INTO CORRESPONDING FIELDS OF TABLE p_tvbapcache

FROM vbap AS ap

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~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 ap_{netwr <> 0 AND ap}netwr <> '0'

AND ep_{wmeng <> 0 AND ep}wmeng <> '0'

AND ep_{bmeng <> 0 AND ep}bmeng <> '0'

AND ap_{kzwi1 <> 0 AND ap}kzwi1 <> '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,

Re: Performance on select join vbap/vbak/vbep

Former Member — Wed, 25 May 2005 16:10:47 GMT

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

Re: Performance on select join vbap/vbak/vbep

jayanthi_jayaraman — Thu, 26 May 2005 06:10:53 GMT

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 .

Re: Performance on select join vbap/vbak/vbep

Former Member — Thu, 26 May 2005 07:05:00 GMT

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.

Re: Performance on select join vbap/vbak/vbep

Former Member — Thu, 26 May 2005 07:18:41 GMT

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,

Re: Performance on select join vbap/vbak/vbep

Former Member — Thu, 26 May 2005 07:22:03 GMT

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...