topic Re: Improve performance - sql statement in Application Development and Automation Discussions

Improve performance - sql statement

Former Member — Mon, 11 Feb 2008 01:54:34 GMT

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 vbak_{vbeln = vbap}vbeln

JOIN kna1 ON vbak_{kunnr = kna1}kunnr

JOIN vbfa ON vbfa_{vbelv = vbap}vbeln

AND vbfa_{posnv = vbap}posnr

JOIN lips ON lips_{vbeln = vbfa}vbeln

AND lips_{posnr = vbfa}posnn

JOIN ltak ON ltak_{vbeln = vbfa}vbeln

JOIN vbfa AS vbfa_s

ON vbfa_s_{vbelv = vbfa}vbeln

JOIN vbfa AS vbfa_p

ON vbfa_p_{vbelv = vbfa}vbeln

AND vbfa_p_{posnv = vbfa}posnn

JOIN vttk ON vttk_{tknum = vbfa_s}vbeln

JOIN ltap ON ltap_{tanum = ltak}tanum

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_p_{vbeln = ( SELECT MAX( p}vbeln )

FROM vbfa AS p

WHERE p_{vbelv = vbfa}vbeln

AND p_{posnv = vbfa}posnn

AND p~vbtyp_n = 'U'

)

AND NOT EXISTS ( SELECT * " exclude SO items with PGI & Billing

FROM vbfa AS a

WHERE a_{vbelv = vbap}vbeln

AND a_{posnv = vbap}posnr

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 vbap_{vbeln vbap}posnr.

DELETE ADJACENT DUPLICATES FROM i_vbap_rob COMPARING ALL FIELDS. "SSR5465.n

Re: Improve performance - sql statement

former_member156446 — Mon, 11 Feb 2008 02:26:48 GMT

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...

Re: Improve performance - sql statement

Former Member — Mon, 11 Feb 2008 03:35:07 GMT

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.

Re: Improve performance - sql statement

Former Member — Mon, 11 Feb 2008 03:36:17 GMT

Write the same prg.. using FOR ALL ENTRIES and observe the graph in SE30. U can find a lot of difference.

Re: Improve performance - sql statement

Former Member — Mon, 11 Feb 2008 04:02:22 GMT

The problem is not with the number JOINs It is likely with the sub query or with not using proper indexes.

Rob

Re: Improve performance - sql statement

Former Member — Mon, 11 Feb 2008 04:27:28 GMT

Hi Rob

Can you tell the incorrect usage of index?

..and can anyone elaborate more on using for all entries..?

Thanks

Re: Improve performance - sql statement

Former Member — Mon, 11 Feb 2008 14:52:42 GMT

I would start over again and simplify.

Rob

Re: Improve performance - sql statement

Former Member — Sun, 11 May 2008 14:54:35 GMT

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.

Re: Improve performance - sql statement

christine_evans — Sun, 11 May 2008 16:09:32 GMT

> 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.