02-11-2008 1:54 AM
Hi,
I am still new to abap. Below is a simple sql that I hope to streamline, so that the performance could be improved. It has taken some amount of time to process. How can I improve the sql below? Thanks
IF v_vtweg IS NOT INITIAL.
cond_vtweg = 'vbak~vtweg = v_vtweg'.
ENDIF.
IF p_werks IS NOT INITIAL.
cond_werks = 'vbap~werks = p_werks'.
ENDIF.
SELECT
vbap~vbeln " Order #
vbap~posnr " Order Position/Item
vbak~kunnr " Customer Code
kna1~name1 " Customer Name
vbap~matnr " Material
vbap~zlcrd " CRD
vbap~zlcrdp2 " CRDP2
vbap~zledd " EDD/MAD
vbap~zlfsd " FSD
vbap~kwmeng " Order Qty
vbap~werks " Plant
vbap~lgort " Sales Org
vbap~netwr " Order Amount
vbak~vtweg " Dist. Channel
lips~lgnum " Warehouse
lips~vbeln " Delivery #
lips~lfimg " Delivery Qty
lips~kcmeng " Cumulative Delivery Qty
ltak~tanum " TO #
ltak~kquit " TO Confirmed status
ltak~druck " TO Print Status
vttk~tknum " Shipment #
vttk~dpabf " Planned date for Shipment Completion
vbfa_p~vbeln " Proforma Invoice #
vbfa_p~erdat " Invoice Date
INTO TABLE i_vbap_rob
FROM vbap
JOIN vbak ON vbakvbeln = vbapvbeln
JOIN kna1 ON vbakkunnr = kna1kunnr
JOIN vbfa ON vbfavbelv = vbapvbeln
AND vbfaposnv = vbapposnr
JOIN lips ON lipsvbeln = vbfavbeln
AND lipsposnr = vbfaposnn
JOIN ltak ON ltakvbeln = vbfavbeln
JOIN vbfa AS vbfa_s
ON vbfa_svbelv = vbfavbeln
JOIN vbfa AS vbfa_p
ON vbfa_pvbelv = vbfavbeln
AND vbfa_pposnv = vbfaposnn
JOIN vttk ON vttktknum = vbfa_svbeln
JOIN ltap ON ltaptanum = ltaktanum
WHERE vbfa~vbtyp_n = 'J'
AND ( vbfa~vbtyp_v = 'C' " Regular Order
OR vbfa~vbtyp_v = 'I' )" Sample Order
AND lips~fkrel = 'A' " Select Deliveries relevant for Billing (in case split deliveries)
AND vbfa_s~vbtyp_n = '8'
AND vbfa_p~vbtyp_n = 'U'
In case there are multiple Porforma Inv., pick the latest copy.
AND vbfa_pvbeln = ( SELECT MAX( pvbeln )
FROM vbfa AS p
WHERE pvbelv = vbfavbeln
AND pposnv = vbfaposnn
AND p~vbtyp_n = 'U'
)
AND NOT EXISTS ( SELECT * " exclude SO items with PGI & Billing
FROM vbfa AS a
WHERE avbelv = vbapvbeln
AND aposnv = vbapposnr
AND ( a~vbtyp_n = 'R' OR " exclude PGI
a~vbtyp_n = 'M' " exclude Billing
)
)
AND ltak~kquit = 'X'
AND ltap~vdifm EQ 0 "SSR5465.n
AND vbak~kunnr IN s_kunnr
AND vbap~matnr IN s_matnr
AND vbap~vbeln IN s_vbeln
AND vbak~vkorg IN s_vkorg
AND vbap~lgort IN s_lgort
AND vttk~tknum IN s_ship
AND vttk~dpabf IN s_sdate
AND (cond_vtweg)
AND (cond_werks)
ORDER BY vbapvbeln vbapposnr.
DELETE ADJACENT DUPLICATES FROM i_vbap_rob COMPARING ALL FIELDS. "SSR5465.n
02-11-2008 2:26 AM
hi performance point of view, joining more tables is not preferred...and you are joining many tables... all you can do is to split the query into parts and use for all entires .. and obtain the output you wanted...
02-11-2008 2:26 AM
hi performance point of view, joining more tables is not preferred...and you are joining many tables... all you can do is to split the query into parts and use for all entires .. and obtain the output you wanted...
02-11-2008 3:35 AM
When you are using less number of tables then you can go for joins. Otherwise, better use FOR ALL ENTRIES statement. It would not have overload on the server. Everytime the data is fetched only from the internal tables except for the first time. I hope you understand. Get back if u still have queries.
Reward points if useful. Best of luck.
02-11-2008 3:36 AM
Write the same prg.. using FOR ALL ENTRIES and observe the graph in SE30. U can find a lot of difference.
02-11-2008 4:02 AM
The problem is not with the number JOINs It is likely with the sub query or with not using proper indexes.
Rob
02-11-2008 4:27 AM
Hi Rob
Can you tell the incorrect usage of index?
..and can anyone elaborate more on using for all entries..?
Thanks
02-11-2008 2:52 PM
05-11-2008 3:54 PM
hi
performance point of u its better to use for all entries instead of using joins.using joins will take lot of time. And you can check the table entries available or not by using sy-dbcnt before for all entries in.
05-11-2008 5:09 PM
>
> Hi,
>
> I am still new to abap. Below is a simple sql that I hope to streamline, so that the performance could be improved. It has taken some amount of time to process. How can I improve the sql below? Thanks
>
What you have written is definitely not a simple sql; it is an extremely complicated bit of sql with far too many joins - 10 tables is it, not counting the subqueries, with VBFA joined to itself multiple times. I use joins a lot - whatever anyone else says on this forum they can often be more efficient than the vastly overrated FOR ALL ENTRIES - but I would have doubts about joining more than about 6 tables unless I was really sure about what I was doing. The more tables you join, the more time it takes the database optimizer to parse the statement to find the optimal access path. And a very large join can be a support nightmare if it hasn't been carefully commented and needs to be maintained by one of the many Abapers who don't really do joins.
As already suggested, break it down and rewrite it as several statements, commenting each section so that anyone who has to maintain it has some idea what is going on.
The indexes on the joins etc look after a quick glance ok. Using WHERE NOT EXISTS can be inefficient. If you need to know about using FOR ALL ENTRIES, look it up in the SAP help or search for it here - it is a way of joining an ABAP internal table with a database table and can be very useful though it is not the solution to all efficiency problems.