In continuation to the response below there are few more things that you need to
do :
table vbup is check table of vbep ,
-so in your select sequence vbup would come before vbep
- there are readymade indexes shipped by sap ( for example at matnr of vbap )
besides I am sure in your environment some of earlier folks would have created their
own indexes , know what they are and use them in where clause.
even if you get extra data by focusing on indexes it would be better sql , one can always
filter out unrequired data from internal tables . Look for existing views for these four tables .
check the buffering status of these tables. so you need to speak to your manager (who is giving the requirements ) to the functional genius who is translating them and also to basis
guy to guide regarding the buffers .
but before you do all that you need to keep your own house in order .
if you have to create new indexes (as many on this forum have advised ) think twice and get advised by dba .
but by and large once you have incorporated all the suggestions ...you will be pleasantly
surprised .
last but not the least ...measure the time taken by the old and new code ....measurement
in such case is a great motivator .
keep us posted about the results of measurement .
//br
alok
| | | ---------------Original Message---------------
From: alok
Sent: Friday, June 24, 2011 1:59 AM
Subject: Performance Issue
Arjun I am providing a snippet here.
Access vbak and vbap first
then collect all the order numbers , then get all the scheduling data.
I have straightened the data structure in the order they appear in database ...
Still I am using into corresponding fields ....this is just in case of a field mismatch ...
Once you are sure about data compatibility remove the corresponding keyword ...
Once you have the order and scheduling data the common thread is vbeln ....
So you know what to do with your internal tables for the objective ...
This will work like a supersonic .....
Also explore extract dataset not to solve this problem but as a template for
performance tuning .
I have commented the zfields and selection parameters in where clause ...
uncomment as they are relevant in your system ....
**it is a wonder that companies pay for atrocious coding
*>>>>alok code
REPORT YTUNE.
tables : vbak , vbap , vbep , vbup .
data : lines type i .
data : begin of itab occurs 0 ,
vbeln like vbak-vbeln,
netwr like vbak-netwr,
vkgrp like vbak-vkgrp,
posnr like vbap-posnr , "3
matnr like vbap-matnr , "4
matkl like vbap-matkl , "8
arktx like vbap-arktx , "9
prodh like vbap-prodh , "17
kdmat like vbap-kdmat , "29
waerk like vbap-waerk , "45
grkor like vbap-grkor , "34
kwmeng like vbap-kwmeng , "49
werks like vbap-werks , "69
*zzvarcd like vbap-zzvarcd ,
*zzcwd like vbap-zzcwd ,
end of itab .
data : begin of num_tab occurs 0,
vbeln like vbak-vbeln ,
end of num_tab .
data : begin of s_tab occurs 0 , "schedule lines table
vbeln like vbep-vbeln ,
bmeng like vbep-bmeng ,
lifsp like vbep-lifsp , "34
wbsta like vbup-wbsta , "9
fksta like vbup-fksta , "10
kosta like vbup-kosta , "14
lvsta like vbup-lvsta, " 15
pksta like vbup-pksta , "26
koqua like vbup-koqua ," 27
end of s_tab .
SELECT vbak~vbeln vbak~vkgrp vbak~netwr
vbap~posnr vbap~matnr vbap~matkl vbap~arktx vbap~prodh
vbap~kdmat vbap~waerk vbap~grkor vbap~kwmeng vbap~werks
FROM ( vbak INNER JOIN vbap ON vbap~vbeln = vbak~vbeln )
into corresponding fields of table itab
*WHERE vbak~auart IN r_ord_types
WHERE vbak~auart = 'TA'
*AND vbak~lifsk IN r_spebe
AND vbap~abgru = space
*AND vbap~werks IN s_werks
*AND vbap~zzcwd IN s_zzcwd "D11K932334
.
write : sy-dbcnt .
sort itab .
num_tab[] = itab[] .
delete adjacent duplicates from num_tab[].
describe table num_tab lines lines .
write : lines .
select
vbep~bmeng
vbep~lifsp
vbup~wbsta
vbup~fksta
vbup~lvsta
vbup~kosta
vbup~pksta
vbup~koqua
FROM ( vbep INNER JOIN vbup ON vbep~vbeln = vbup~vbeln )
into corresponding fields of table s_tab for all entries in num_tab where vbep~vbeln = num_tab-vbeln
AND ( vbup~lfsta = space OR "not relevent
vbup~lfsta = 'A' OR "not yet processed
vbup~lfsta = 'B' ) "partially processed
*Delivery relevent
AND vbep~lfrel = 'X' .
*Schedule line date
*AND vbep~edatu LE two_week_end_date.
write : / sy-dbcnt . |
| __.____._ Copyright © 2011 Toolbox.com and message author.
Toolbox.com 4343 N. Scottsdale Road Suite 280, Scottsdale, AZ 85251
| | Popular White Papers
In the Spotlight  _.____.__ |
