topic Re: JOIN VBAK-VBAP - performance poor in Application Development and Automation Discussions

JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 09:56:21 GMT

Hi, i have some problems with this select. Perfomance are very poor

SELECT p_{vbeln p}posnr t_{vdatu p}werks p~matnr

p~kwmeng

INTO CORRESPONDING FIELDS OF TABLE i_vbap

FROM vbak AS t INNER JOIN vbap AS p

ON t_{vbeln = p}vbeln

WHERE t~vkorg = c_vkorg

AND t~vtweg = c_vtweg

AND t~spart = c_spart

AND ( t~auart = 'tip' OR

t~auart = 'tap')

AND t~vdatu IN r_vdatu

AND p~matnr IN r_matnrnf1

AND p~werks IN r_dfl

AND p~spart NE 'F1'

AND t~vkgrp IN r_vkgrp

AND t~bname = space.

In r_vdatu Orders are about 60000 and positions are 80 per order.

Is it possibile to improve perfomance?. Do you know an another way to do this select?

Thank you for attention.

Bye

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 10:00:42 GMT

Declare the i_vbap with the order of fields same as the order in which they are selected and you can remove the into correspoding field statement which is very costly.

SELECT p~vbeln

p~posnr

p~werks

p~matnr

p~kwmeng

t~vdatu

INTO TABLE i_vbap

FROM vbak AS t INNER JOIN vbap AS p

ON t_{vbeln = p}vbeln

WHERE t~vkorg = c_vkorg

AND t~vtweg = c_vtweg

AND t~spart = c_spart

AND ( t~auart = 'tip' OR

t~auart = 'tap')

AND t~vdatu IN r_vdatu

AND p~matnr IN r_matnrnf1

AND p~werks IN r_dfl

AND p~spart NE 'F1'

AND t~vkgrp IN r_vkgrp

AND t~bname = space.

Regards,

Ravi

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 10:04:43 GMT

Hello Andrea,

Yuo can probably split up the two select statements like this -

SELECT vbeln 
  FROM vbak 
  INTO table lt_vbak
 WHERE vkorg = c_vkorg
   AND vtweg = c_vtweg
   AND spart = c_spart
   AND ( auart = 'tip' OR auart = 'tap')
   AND vdatu IN r_vdatu 
   AND bname = space.

SELECT vbeln posnr werks matnr kwmeng
  FROM vbap
  INTO table i_vbap
  FOR ALL ENTRIES IN lt_vbak
WHERE vbeln = lt_vba-vbeln
  AND matnr IN r_matnrnf1
  AND werks IN r_dfl
  AND spart NE 'F1'.

Since both the tables VBAK and VBAP are quite huge, a Join on these tables may not be desirable from the performance point of view.

Regards,

Anand Mandalika.

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 10:05:58 GMT

Hi,

along with the above suggestion..

Remove some unnecessary where conditions..,

after select use delete itab where <condition>.

some thing like this..

delete itab where vbeln not in s_vbeln.

regards

vijay

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 10:07:12 GMT

Hi,

You would use a view which has the fields of both the tables which you require and then query it for specific records

e.g views WB2_V_VBAK_VBAP2

Sameena

Message was edited by: sameena attarwala

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 10:08:15 GMT

Hi,

if you are using for all entries then don't forget to check the initial condition.

SELECT vbeln 
  FROM vbak 
  INTO table lt_vbak
 WHERE vkorg = c_vkorg
   AND vtweg = c_vtweg
   AND spart = c_spart
   AND ( auart = 'tip' OR auart = 'tap')
   AND vdatu IN r_vdatu 
   AND bname = space.
<b>if not lt_vbak[] is initial.</b>
SELECT vbeln posnr werks matnr kwmeng
  FROM vbap
  INTO table i_vbap
  FOR ALL ENTRIES IN lt_vbak
WHERE vbeln = lt_vba-vbeln
  AND matnr IN r_matnrnf1
  AND werks IN r_dfl
  AND spart NE 'F1'.
<b>endif.</b>

Regards

vijay

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 10:14:06 GMT

one more suggestion for performance improvement.

in the WHERE Condition of SELECT the fields you are giving should be in the same order in the tables. then only you will get results faster.

in your case,

VBAK TABLE,

VBELN,

AUART,

VKORG,

ETC.. IS There in the table.but in your SELECT you given VKORG,VTWEG before AUART. which you have to change.

regards

srikanth

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 10:20:29 GMT

hi,

to improve performance, you should keep in mind following points.

1. the order of the fields of internal table should be the same as in select statement.

2. avoid using joins.instead use two select statements.

3. check the condition if the first internal table has some values then only select data into the other internal tables(that data depends on the data selected in first internal table)

4. in the where condition, the fields should be in the same order in the tables

Hope this helps you.

Regards,

Richa

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 11:01:46 GMT

Hi,

You can use indexes on the tables in the given sequences.

If you dont have the key values, pass the empty ranges for keys.

Try not to give negative conditions in where clause.

Insted, delete the table after select statement.

Alternatively,

Try using following views,

WB2_V_VBAK_VBAP

WB2_V_VBAK_VBAP2

Regards,

Shashank

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 11:08:29 GMT

Hi,

see the sample code:

may be it will be useful.

REPORT ZDEMO.

TYPES: BEGIN OF TY_VBAK,

VBELN LIKE VBAK-VBELN,

VDATU LIKE VBAK-VDATU,

END OF TY_VBAK.

TYPES: BEGIN OF TY_VBAP,

VBELN LIKE VBAP-VBELN,

POSNR LIKE VBAP-POSNR,

MATNR LIKE VBAP-MATNR,

WERKS LIKE VBAK-WERKS,

KWMENG LIKE VBAP-KWMENG,

END OF TY_VBAP.

DATA: IT_VBAK TYPE STANDARD TABLE OF TY_VBAK,

WA_VBAK LIKE LINE OF IT_VBAK,

IT_VBAP TYPE STANDARD TABLE OF TY_VBAP,

WA-VBAP LIKE LINE OF IT_VBAP.

SELECT VBELN

VDATU

INTO TABLE IT_VBAK

FROM VBAK

WHERE VKORG = C_VKORG

AND VTWEG = C_VTWEG

AND SPART = C_SPART

AND ( AUART = 'TIP' OR AUART = 'TAP')

AND VDATU IN R_VDATU

AND BNAME = SPACE.

IF SY-SUBRC = 0.

SELECT VBELN

POSNR

MATNR

WERKS

KWMENG

FROM VBAP INTO TABLE IT_VBAP

FOR ALL ENTRIES IN IT_VBAK

AND MATNR IN R_MATNRNF1

AND WERKS IN R_DFL

AND SPART NE 'F1'.

IF SY-SUBRC <> 0.

ENDIF.

- Selvapandian Arunachalam

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 13:26:24 GMT

I don't think there's much you can do here without re-working the logic. The program selects from two rather large tables without using either primary or secondary keys.

Rob

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 13:56:42 GMT

Hi Andrea

A lot of the fields in your Where clause are part of the primary key in VAPMA, which is a sales index table. If you have a look at it in the ABAP dictionary I would be inclined to try using three select statements. The first would select just VBELN and POSNR from VAPMA based on as many of the conditions that apply to that table

SELECT vbeln posnr

FROM vapma

INTO CORRESPONDING FIELDS OF TABLE i_vapma

WHERE matnr IN r_matnrnf1

AND vkorg = c_vkorg

etc etc

Make sure that the order the fields appear in your WHERE clause are the same as in VAPMA. Also avoid using criteria that do not appear in the primary key.

Then write two more select statements collectin data from VBAK and VBAP:

CHECK NOT i_vapma IS INITIAL.

SELECT vbeln vdatu bname

FROM vbak

INTO CORRESPONDING FIELDS OF TABLE i_vbak

FOR ALL ENTRIES IN i_vapma

WHERE vbeln = i_vapma-vbeln.

SELECT vbeln posnr werks matnr kwmeng

FROM vbap

INTO CORRESPONDING FIELDS OF TABLE i_vbap

FOR ALL ENTRIES IN i_vapma

WHERE vbeln = i_vapma-vbeln

AND posnr = i_vapma-vbap.

You can then loop through i_vbak and read records from vbap into a combined table:

LOOP AT i_vbak INTO wa_vbak.

IF wa_vbak-vdatu IN r_vdatu AND bname = space.

READ TABLE i_vbap WITH KEY vbeln = i_vbak-vbeln

posnr - i_vbak-posnr

INTO wa_vbap.

IF i_vbap-werks IN r_dfl.

...

ENDIF.

ENDLOOP.

WHere ... = code to copy all of the various data from wa_vbak and wa_vbap to an itab containing the relevant combined fields.

I've not tested this so be a bit careful but I think the logic is sound in that you are using an index to find the primary key fields for VBAK and VBAP (i.e. VBELN and POSNR). Then you can select from VBAK and VBAP using their primary keys which will be much quicker than what you were trying.

Generally, you should avoid Joins as they can be very slow, it is normally better to do multiple select...for all entries and then combine the data.

I hope this is of some help.

Kind regards

Andrew

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 14:54:40 GMT

Good point Andrew. I'd forgotten about that table. (I don't work with SD very much.)

However, I'm not sure about a join being inefficient. For example, look at SAP view COVP which is essentially a join on two very large tables. I re-wrote the original code using VAPMA in the join. If the original poster (or any one else) wants to try both methods, it would be interesting to compare the results. My code follows (I'm not sure about the WHERE on SPART):

SELECT p~vbeln p~posnr t~vdatu p~werks p~matnr
       p~kwmeng
  INTO CORRESPONDING FIELDS OF TABLE i_vbap
  FROM vbak AS t
    INNER JOIN vbap AS p
      ON t~vbeln = p~vbeln
    INNER JOIN vapma AS m
      ON t~vbeln = m~vbeln
  WHERE m~matnr IN r_matnr
  AND   m~vkorg = c_vkorg
  AND   m~vtweg = c_vtweg
  AND   m~spart = c_spart
  AND   m~spart NE 'F1'
  AND ( m~auart = 'tip' OR
        m~auart = 'tap')
  AND   t~vkgrp IN r_vkgrp
  AND   p~werks IN r_dfl
  AND   t~vdatu IN r_vdatu
  AND   t~bname = space.

Rob

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 15:10:29 GMT

Thanks Rob - you mean there's a world outside SD??? I must get out more!!

You're right it would be interesting to see how this works - maybe one day I'll get round to trying it. I'm not convinced it will be more efficient because I understood the Joins to work by finding all of the matches between the joined tables and then applying the Where conditions. I suspect this means the relative efficiency is down to the number of records involved in the corresponding tables. However, when compared with my solution yours looks much more elegant and maintainable so if the efficiency gains are marginal it's probably a better option.

Kind regards

Andrew

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 15:32:38 GMT

Hey,

SD has index tables from which data retrieval is much faster as compared to VBAK and VBAP.

These tables are

VAKPA- Sales Index: Orders by Partner Function

VAPMA- Sales Index: Order Items by Material

Try using these tables in your application.

-Kiran

Re: JOIN VBAK-VBAP - performance poor

Former Member — Fri, 23 Jun 2006 15:39:42 GMT

Hi Andrew - yes, there's a world ouside of SD - FI. (I've heard rumours of things called beaches and baseball games as well).

Any way, a similar question came up about a month ago and I wrote a program that compared a join on a number of tables against both for all entries and nested selects. The nested select lost, but the join beat both. Here's the original Your results may vary.

Rob

Re: JOIN VBAK-VBAP - performance poor

Former Member — Sat, 24 Jun 2006 06:40:01 GMT

Hi AS ,

try to use any Indexes which are created on these Tables ? if not create new S.Index.

2.try to minimize Where Claue.

3.run st05 and find out DB cost and which index it is using .

4.maintain Proper order based on the Position of the Field in the Relavent Table.

5.Try use Date Range in the where Clause.


SELECT p~vbeln p~posnr t~vdatu p~werks p~matnr
p~kwmeng
INTO CORRESPONDING FIELDS OF TABLE i_vbap
FROM vbak AS t INNER JOIN vbap AS p
ON t~vbeln = p~vbeln
WHERE t~auart in(  'tip','tap')
AND t~vkorg = c_vkorg
AND t~vtweg = c_vtweg
AND t~spart = c_spart
AND t~vdatu IN r_vdatu 
AND p~matnr IN r_matnrnf1
AND p~werks IN r_dfl
AND p~spart NE 'F1'
AND t~vkgrp IN r_vkgrp
AND t~bname = space.

Regards

Prabhu

Re: JOIN VBAK-VBAP - performance poor

Clemenss — Sat, 24 Jun 2006 18:44:43 GMT

the 'into corresponding fields' is not costly much; you will not be able to determine the costs as more than 0.5 % of the overall performance.

Depending on your database system, it may be useful first to select the header data from vbak and then get the item data using for all entries clause with header table and specifying the item restrictions.

Although people might tell you that for all entries is costly, they will never be able to prove that because just the opposite is fact.

Regards,

Clemens

Re: JOIN VBAK-VBAP - performance poor

Former Member — Mon, 26 Jun 2006 08:19:07 GMT

Hi Rob - what is this 'beach' of which you speak?!

Thanks for this. I've had a look at the thread and was very interested to see that the join comes out on top. I'll certainly being giving your program a go on our system when I get a chance and see what happens. Call me a geek but I find all this performance stuff fascinating!!!

Kind regards

Andrew