Performance problems selecting form vbak and vbap

[Former Member]

Hello,

I am try to select data from the vbak and vbap databasetables. The tables have more than a million of entries. I tried my best with two related selectstatements and the 'FOR All ENTRIES' syntax. The query takes more than 30 minutes. Is there a more efficient way than the following query, for example an inner join?. Tanks.

Regards, Lars.

-

---

FORM firstselect.

SELECT k_{vkorg k}vtweg k_{spart k}auart k~kunnr

k_{vsbed k}augru k_{vbeln k}mandt

FROM VBAK AS k

INTO CORRESPONDING FIELDS OF TABLE t_outtab1

WHERE k~vkorg IN s_vkorg AND

k~vtweg IN s_vtweg AND

k~spart IN s_spart AND

k~auart IN s_auart AND

k~kunnr IN s_kunnr AND

k~vsbed IN s_vsbed AND

k~augru IN s_augru AND

k~vbeln IN s_vbeln.

ENDFORM.

FORM secondselect.

SELECT p_{posnr p}vstel p~werks

p_{abgru p}matnr p~kwmeng

p_{zzurmeng p}vrkme p_{mandt p}vbeln

FROM VBAP AS p

INTO CORRESPONDING FIELDS OF TABLE t_outtab2

FOR ALL ENTRIES IN t_outtab1

WHERE

p~vstel IN s_vstel AND

p~werks IN s_werks AND

p~matnr IN s_matnr AND

p~abgru IN s_abgru AND

• t_outtab1-mandt = p~mandt AND

p~vbeln = t_outtab1-vbeln.

ENDFORM.

[jayanthi_jayaraman · Accepted solution]

Hi,

One thing is declare the types accordingly.

For example:

types : begin of ty_vbak,

vkorg type vbak-vkorg,

vtweg type vbak-vtweg,

...

end of ty_vbak.

data t_outtab1 type standard table of ty_vbak.

Then instead of corresponsding fields,you can use directly

SELECT k_{vkorg k}vtweg k_{spart k}auart k~kunnr

k_{vsbed k}augru k_{vbeln k}mandt

FROM VBAK AS k

INTO TABLE t_outtab1

WHERE k~vkorg IN s_vkorg AND

k~vtweg IN s_vtweg AND

k~spart IN s_spart AND

k~auart IN s_auart AND

k~kunnr IN s_kunnr AND

k~vsbed IN s_vsbed AND

k~augru IN s_augru AND

k~vbeln IN s_vbeln.

[jayanthi_jayaraman]

Hi,

As Abhishek suggested,you can use innerjoin.

types : begin of ty,

vbeln type vbak-vbeln,

vkorg type vbak-vkorg,

vtweg type vbak-vtweg,

spart type vbak-spart,

auart type vbak-auart,

kunnr type vbak-kunnr,

vsbek type vbak-vsbek,

augru type vbak-augru,

posnr type vbap-vstel,

werks type vbap-werks,

abgru type vbap-abgru,

matnr type vbap-matnr,

kwmeng type vbap-kwmeng,

zzurmeng type vbap-zzurmeng,

vrkme type vbap-vrkme,

end of ty.

data itab type standard table of ty.

SELECT k_{vbeln k}vkorg k_{vtweg k}spart k_{auart k}kunnr

k_{vsbed k}augru p_{posnr p}vstel p~werks

p_{abgru p}matnr p~kwmeng

p_{zzurmeng p}vrkme

FROM ( VBAK AS k

inner join

VBAP AS p

on k_{vbeln = p}vbeln )

into table itab

WHERE k~vkorg IN s_vkorg AND

k~vtweg IN s_vtweg AND

k~spart IN s_spart AND

k~auart IN s_auart AND

k~kunnr IN s_kunnr AND

k~vsbed IN s_vsbed AND

k~augru IN s_augru AND

k~vbeln IN s_vbeln and

p~vstel IN s_vstel AND

p~werks IN s_werks AND

p~matnr IN s_matnr AND

p~abgru IN s_abgru.

[Former Member]

Hi Lars,

Just try out this inner join ...maybe this wud help a bit on the performance side , t_outtab1 is a combination of t_outtab1 and t_outtab2.

select VBAK_{KUNNR VBAK}SPART VBAK_{VTWEG VBAK}VBELN VBAK_{AUART VBAP}POSNR VBAP_{MATNR VBAP}VBELN

from ( VBAK

inner join VBAP

on VBAP_{VBELN = VBAK}VBELN )

into corresponding fields of table t_outtab1

where VBAK~VTWEG in s_vtweg

and VBAK~SPART in s_spart

and VBAK~AUART in s_auart

and VBAK~KUNNR in s_kunnr

and VBAP~MATNR in s_matnr.

Regards

Abhishek

[Former Member]

FORM firstselect.

SELECT vkorg 
       vtweg 
       spart 
       auart 
       kunnr
       vsbed 
       augru 
       vbeln 
       mandt
       FROM VBAK  
       INTO TABLE t_outtab1
WHERE vkorg IN s_vkorg AND
vtweg IN s_vtweg AND
spart IN s_spart AND
auart IN s_auart AND
kunnr IN s_kunnr AND
vsbed IN s_vsbed AND
augru IN s_augru AND
vbeln IN s_vbeln.
ENDFORM.

FORM secondselect.


SELECT posnr 
       vstel 
       werks
       abgru 
       matnr 
       kwmeng
       zzurmeng 
       vrkme
       mandt
      vbeln
  FROM VBAP TABLE t_outtab2
FOR ALL ENTRIES IN t_outtab1
WHERE  vbeln = t_outtab1-vbeln 
AND vstel IN s_vstel AND
werks IN s_werks AND
matnr IN s_matnr AND
abgru IN s_abgru .

ENDFORM.

Hope this helps u.

I will get u back with more answers.

One more important thing see that the order which u r using in the select query is the same as that in the table this will affect the performs a lot.

<i><b>The first field should be the vbeln and then see the order and change that first and the same order should be in the internal table taht u r defining also.

If u do these things surely u can improve performance.</b></i>

Message was edited by: Judith Jessie Selvi

[Former Member]

<b>Golden Rule</b> : Whenever you do , SELECT-FOR-ALL-ENTRIES, make sure the internal table that you are using in the clause is not empty.

i.e.

if no t_outtab1 is initial.

select-for-all-entries-in-t_outtab1 query here

endif.

Explore the following:

a) Which query takes longer ?

b) Is there a possible way of using the existing index present for the given tables ?

c) Should you create a secondary index for the table ?

d) Will the secondary index affect database performance ?

Regards,

Subramanian V.