Knowledge Management

Question:
I created a query to join VBAK and VBAP. It also join VBRP using
aubel = vbap-vbeln
and aupos = vbap-posnr.
I select 1 year of data, It ran for all day and abended. Is there a way to make it faster?
thanks
select vbak~auart vbak~erdat vbak~kunnr vbak~vbeln vbak~vkgrp
vbap~abgru vbap~kwmeng vbap~matnr vbap~posnr vbap~vbeln
vbap~vrkme
into (vbak-auart , vbak-erdat , vbak-kunnr , vbak-vbeln , vbak-vkgrp
, vbap-abgru , vbap-kwmeng , vbap-matnr , vbap-posnr , vbap-vbeln
, vbap-vrkme )
from ( vbak
inner join vbap
on vbap~vbeln = vbak~vbeln )
where vbak~auart in sp$00001
and vbak~erdat in sp$00002
and vbak~vkgrp in sp$00003
and vbap~abgru in sp$00004.
check sp$00001.
check sp$00002.
check sp$00003.
check sp$00004.
clear: _vbeln, _fkimg.
select single vbeln fkimg into (_vbeln, _fkimg)
from vbrp where aubel = vbap-vbeln
and aupos = vbap-posnr.
check _fkimg <> vbap-kwmeng.
*check vbrp-fkimg <> vbap-kwmeng.
select single name1 into soldname from kna1
where kunnr = vbak-kunnr.
invoice_no = _vbeln.
*inVOICE_NO = VBRP-VBELN.
act_inv_qty = _fkimg.
*aCT_INV_QTY = VBRP-FKIMG.
add 1 to %count-vbak.
%linr-vbak = '01'.
extract %fg01.
endselect.
Answer:
Hi,
Eliminate SELECT - ENDSELECT. You can inner join more than two tables.
Eliminate into (vbak-auart , vbak-erdat , vbak-kunnr . Instead use INTO TABLE
Eliminate double checking of sp$00001, sp$00002, et cetera. It is enough if there are in SELECT statement (actually for the speed reason they should be a part of SELECT statement).
But then you can not use EXTRACT. I am not sure if it is possible to loop through internal table and do EXTRACT. You may try it.
In my system there is no good index available for this query so it would likely do full table scan. This maybe the main reason why it is taking so much time. Check you system. Look at the trx. ST05 during SELECT execution to see how this it is handle. Maybe you can read necessary data from different tables or specify some additional fields in SELECT statement.
If system is doing full table scan you may also consider (as a last option) to create additional index over VBAK.
regards,
_________________
Andrzej Wanicki
Answer:
Hi,
Try to use the secondary index table VAPMA instead of directly using VBAK and VBAP
aRs
Chennai-India
Answer:
You may find the joining these tables kills the perfromance of the system - both are big transaction tables containing potentially 000's records. Instead of creating the view in the ways that have been suggested, create projection views over both these tables for the fields that you need and then select on the project views. You may well find it helps long term performance.
Just a thought
RP