topic Re: Joining VBAK and VBAP in Application Development and Automation Discussions

Joining VBAK and VBAP

Former Member — Tue, 24 Mar 2009 07:14:02 GMT

Hi Guys,

Can you pls check the query and suggest best approch to improve the performance as its going for dump.

VBAP as 2,22,34,404

VBAK as 25,54,345

and

r_matnr as 96,000 records.

SELECT vbap_{matnr SUM( vbap}kwmeng )

FROM vbak INNER JOIN vbap ON vbak_{vbeln = vbap}vbeln

INTO TABLE t_xx

WHERE audat BETWEEN w_pstdat AND w_inidat

AND vkorg IN r_vkorg

AND auart IN r_auart

AND vbap~matnr IN r_matnr

AND vbap~werks EQ p_werks

AND vbap~pstyv IN s_pstyv

GROUP BY vbap~matnr.

Regards,

Srinivas M

Re: Joining VBAK and VBAP

Former Member — Tue, 24 Mar 2009 07:19:40 GMT

HI,

Build the range for r_audat using w_pstdat AND w_inidat

SELECT vbap_{matnr SUM( vbap}kwmeng )

FROM vbak INNER JOIN vbap ON vbak_{vbeln = vbap}vbeln

INTO TABLE t_xx

WHERE audat in r_audat

AND vkorg IN r_vkorg

AND auart IN r_auart

AND vbap~matnr IN r_matnr

AND vbap~werks EQ p_werks

AND vbap~pstyv IN s_pstyv.

Instead GROUP BY vbap~matnr use SORT t_xx By MATNR.

Or Check for the View on VBAP AND VBAK that will help to increase the preformence instead of inner join. write the select on this view MASSVBAP and fetch the data

Re: Joining VBAK and VBAP

Former Member — Tue, 24 Mar 2009 07:30:34 GMT

SELECT vbap_{matnr SUM( vbap}kwmeng )

FROM vbak INNER JOIN vbap ON vbak_{vbeln = vbap}vbeln

INTO TABLE t_xx

WHERE audat BETWEEN w_pstdat AND w_inidat

AND vkorg IN r_vkorg

AND auart IN r_auart

AND vbap~matnr IN r_matnr

AND vbap~werks EQ p_werks

AND vbap~pstyv IN s_pstyv

GROUP BY vbap~matnr.

This can be changed to two set of queries.

select vbeln from vbak

intocorresponding fields of table it_vbak

WHERE audat BETWEEN w_pstdat AND w_inidat

AND vkorg IN r_vkorg

AND auart IN r_auart.

if it_vbak[] is not initial.

select matnr kwmeng from VBAP

into corresponding fields of table t_xx*

for all entries in it_vbak

where vbeln = it_vbak-vbeln.

if sy-subrc eq 0.

sort it_vbak by matnr.

endif.

Re: Joining VBAK and VBAP

Former Member — Tue, 24 Mar 2009 09:02:22 GMT

u have mentioned r_matnr having more than 90000 records. I think this is not supported. The max my system goes for a range table is 800 - 1000 records(Not sure of the accurate number)

Can you let me know the dump you are getting here?

Edited by: ZAFCO ABAP on Mar 24, 2009 1:02 PM

Re: Joining VBAK and VBAP

Former Member — Tue, 24 Mar 2009 09:07:40 GMT

Hi Srinivas M,

Thanks for send the amount of records on involved internal tables / range.

Please review the material filter, the native sql can't be processed with r_matnr with 96.000 records. Check the reason here:

Best Regards,

Fernando Da Ró

Re: Joining VBAK and VBAP

ThomasZloch — Tue, 24 Mar 2009 09:13:58 GMT

Also, what is the value of w_pstdat and w_inidat? If this range is typically narrow (a few days), then there is a good index VBAK~AUD available for the selection. If the range is large, look into introducing table VAPMA into your join for access via MATNR.

Thomas

Re: Joining VBAK and VBAP

Former Member — Tue, 24 Mar 2009 10:08:46 GMT

> Check for the View on VBAP AND VBAK that will help to increase the preformence

> instead of inner join

oh, a new option is recommended frequently in recent positing, after FAE is supported so much anymore.

BUT the View is only a dictionary defintion of a join, they are 100% identical on the database

There is not clear limit for the size of the ranges, usually in the order of 1000 or 2000. It will dump when whole resultings statement is above a limit, which is hard tto specify.


WHERE audat BETWEEN w_pstdat AND w_inidat
AND vkorg IN r_vkorg
AND auart IN r_auart
AND vbap~matnr IN r_matnr
AND vbap~werks EQ p_werks
AND vbap~pstyv IN s_pstyv
GROUP BY vbap~matnr.

This are actually several statements, which can be completely different. It depends which IN-Lists/Ranges are filled.

The join can react on that, it uses different accesses, if there are more conditions on vbak or

more vbap.

For the FAE you must programm two versions, one startung with vbak and one with vbak. And

you must determine which one will use. I doubt that you will better than the optimzer in the database.

Siegfried