topic Re: ABAP Select statement performance (with nested NOT IN selects) in Application Development and Automation Discussions

ABAP Select statement performance (with nested NOT IN selects)

Former Member — Thu, 19 Apr 2007 22:51:02 GMT

Hi Folks,

I'm working on the ST module and am working with the document flow table VBFA. The query takes a large amount of time, and is timing out in production. I am hoping that someone would be able to give me a few tips to make this run faster. In our test environment, this query take 12+ minutes to process.

SELECT vbfa~vbeln

vbfa~vbelv

Sub~vbelv

Material~matnr

Material~zzactshpdt

Material~werks

Customer~name1

Customer~sortl

FROM vbfa JOIN vbrk AS Parent ON ( Parent_{vbeln = vbfa}vbeln )

JOIN vbfa AS Sub ON ( Sub_{vbeln = vbfa}vbeln )

JOIN vbap AS Material ON ( Material_{vbeln = Sub}vbelv )

JOIN vbak AS Header ON ( Header_{vbeln = Sub}vbelv )

JOIN vbpa AS Partner ON ( Partner_{vbeln = Sub}vbelv )

JOIN kna1 AS Customer ON ( Customer_{kunnr = Partner}kunnr )

INTO (WA_Transfers-vbeln,

WA_Transfers-vbelv,

WA_Transfers-order,

WA_Transfers-MATNR,

WA_Transfers-sdate,

WA_Transfers-sfwerks,

WA_Transfers-name1,

WA_Transfers-stwerks)

WHERE vbfa~vbtyp_n = 'M' "Invoice

AND vbfa~fktyp = 'L' "Delivery Related Billing Doc

AND vbfa~vbtyp_v = 'J' "Delivery Doc

AND vbfa~vbelv IN S_VBELV

AND Sub~vbtyp_n = 'M' "Invoice Document Type

AND Sub~vbtyp_v = 'C' "Order Document Type

AND Partner~parvw = 'WE' "Ship To Party(actual desc. is SH)

AND Material~zzactshpdt IN S_SDATE

AND ( Parent_{fkart = 'ZTRA' OR Parent}fkart = 'ZTER' )

AND vbfa~vbelv NOT IN

( SELECT subvbfa~vbelv

FROM vbfa AS subvbfa

WHERE subvbfa_{vbelv = vbfa}vbelv

AND subvbfa~vbtyp_n = 'V' ) "Purchase Order

AND vbfa~vbelv NOT IN

( SELECT DelList~vbeln

FROM vbfa AS DelList

WHERE DelList_{vbeln = vbfa}vbelv

AND DelList~vbtyp_v = 'C' "Order Document Type

AND DelList~vbelv IN "Delivery Doc

( SELECT OrderList~vbelv

FROM vbfa AS OrderList

WHERE OrderList~vbtyp_n = 'H' ) "Return Ord

APPEND WA_Transfers TO ITAB_Transfers.

ENDSELECT.

Cheers,

Chris

Re: ABAP Select statement performance (with nested NOT IN selects)

Former Member — Fri, 20 Apr 2007 01:22:50 GMT

1. Break up the query into sub queries and use internal tables for ABAP procesing by removing the Inner queries.

2. Most of the join conditions have been performed without the complete primary key.Infact the self join on VBFA is based on only on VBELN instead of the entire key VBELV POSNV VBELN POSNN VBTYP_N. It really depends upon the business case to justify whether it is requried at all. More information in this regard shud help

3. For ideal cases donot use more than 3 table joins.

Although the ABAP time would increase it would lead to significant lesser time as for each Select ...end select it is now hitting the DB with addtional inner queries which we are not sure how the Query optimizer is handling.

Re: ABAP Select statement performance (with nested NOT IN selects)

Former Member — Fri, 20 Apr 2007 01:39:43 GMT

1) Break up your query into at least 2 or three separate queries.

2) Use "INTO TABLE" instead of into a work area and using an endselect loop to minimize SAP to DB roundtrips.

3) Remove your subqueries - instead try to find an alternative.

Instead of

AND vbfa~vbelv NOT IN

( SELECT subvbfa~vbelv

FROM vbfa AS subvbfa

WHERE subvbfa_{vbelv = vbfa}vbelv

AND subvbfa~vbtyp_n = 'V' ) "Purchase Order

create a range for data element VBTYP and include M and exclude V.

r_vbtyp-sign = 'I'. "include

r_vbtyp-line-option = 'EQ'.

r_vbtyp-line-low = 'M'.

append r_vbtyp.

r_vbtyp-sign = 'E. "exclude

r_vbtyp-line-option = 'EQ'.

r_vbtyp-line-low = 'V'.

append r_vbtyp.

AND Sub~vbtyp_n in r_vbtyp "Invoice Document Type

I'm sure that with the second one, there is a way to optimize/remove it as well.

Re: ABAP Select statement performance (with nested NOT IN selects)

Former Member — Fri, 20 Apr 2007 21:41:07 GMT

Both helpful suggestions. I've attempted to break the query into its subcomponents, but there isn't much (0-10%) improvement. The problem lies in the aspect that this is a huge table, and I'm following an iterative path to find an associated document that does not exist...(in the same table) so I need to have a NOT IN select (or code of the equivalent).

I'm coming to the realization that I'm going to have to run this as a batch or in the background. Although I'm going to leave this open for a few more days to see if someone has any other suggestions?

Re: ABAP Select statement performance (with nested NOT IN selects)

Former Member — Sat, 21 Apr 2007 09:31:33 GMT

you are correct. the NOT IN clause will negelect the index search and will lead to full table scan. it is suggested to have EQ condition in the WHERE clause, if possible.

My observations:

1. Avoid SELECT-ENDSELECT.

2. Avoid NOT IN condition in your where clause.

3. Avoid complex joins.

4. Avoid sub queries.

regards,

madhu

Re: ABAP Select statement performance (with nested NOT IN selects)

Former Member — Sat, 21 Apr 2007 13:55:16 GMT

I am sending u some of the performance isuues that are to be kept in mind while coding.

1.Donot use Select *...... instead use Select <required list>......

2.Donot fetch data from CLUSTER tables.

3.Donot use Nested Select statements as. U have used nested select which reduces performance to a greater extent.

Instead use views/join .

Also keep in mind that not use join condition for more for more than three tables unless otherwise required.

So split select statements into three or four and use Select ......for all entries....

4.Extract the data from the database atonce consolidated upfront into table.

i.e. use INTO TABLE <ITAB> clause instead of using

Select----

End Select.

5.Never use order by clause in Select ..... statement. instead use SORT<itab>.

6.When ever u need to calculate max,min,avg,sum,count use AGGREGATE FUNCTIONS and GROUP BY clause insted of calculating by userself..

7.Donot use the same table once for Validation and another time for data extraction.select data only once.

8.When the intention is for validation use Select single ....../Select.......up to one rows ......statements.

9.If possible always use array operations to update the database tables.

10.Order of the fields in the where clause select statement must be in the same order in the index of table.

11.Never release the object unless throughly checked by st05/se30/slin.

12.Avoid using identical select statements.