topic Re: Tune this Select Statement in Application Development and Automation Discussions

Tune this Select Statement

Former Member — Mon, 04 Aug 2008 15:31:39 GMT

My Aim to fetch Sales Orders for the customers Number is I have in i_kna1.

I coded as below but it takes lots of time as no proper index is used.

Is there any other way to fetch sales orders for customers or any clues on how to improve this statement.

Fetch the sales order numbers w.r.t customer from partner data

SELECT a~vbeln " Sales Order

a~kunnr " Customer Number

b~gbstk " Processing Status

b~cmgst " Status of credit checks

FROM vbpa AS a INNER JOIN vbuk AS b

ON a_{vbeln EQ b}vbeln

INTO TABLE l_i_vbpa_vbuk

FOR ALL ENTRIES IN i_kna1

WHERE a~kunnr EQ i_kna1-kunnr.

SORT : l_i_vbpa_vbuk.

Thanks

Kiran

Re: Tune this Select Statement

ian_maxwell2 — Mon, 04 Aug 2008 15:43:49 GMT

SELECT a~vbeln " Sales Order
a~kunnr " Customer Number
b~gbstk " Processing Status
b~cmgst " Status of credit checks
FROM vbpa AS a INNER JOIN vbuk AS b
ON a~vbeln EQ b~vbeln
INTO TABLE l_i_vbpa_vbuk
FOR ALL ENTRIES IN i_kna1
WHERE a~kunnr EQ i_kna1-kunnr.
SORT : l_i_vbpa_vbuk.

My recomendation would be to do a trace via ST05 and take a look at the explain plan that is being used on the database (what DB are you using? Oracle?). If you can post the explain plan on here I'll be able to tell you more of what is going on. With this explain plan we'll be able to tell a few things about the optimizer:

- The order that the tables will be joined in

- The inexes that will be used

- The alogithms that will be used for searching and joining

~Ian

Re: Tune this Select Statement

former_member584282 — Mon, 04 Aug 2008 16:41:49 GMT

Kiran,

Try using the BAPI BAPI_SALESORDER_GETLIST, you can pass the following parameters to the BAPI

CUSTOMER_NUMBER

SALES_ORGANIZATION

MATERIAL

DOCUMENT_DATE

DOCUMENT_DATE_TO

PURCHASE_ORDER

TRANSACTION_GROUP

PURCHASE_ORDER_NUMBER

Hope this helps...

Amit Purohit.

Re: Tune this Select Statement

Former Member — Mon, 04 Aug 2008 16:55:40 GMT

Hi,

If you want to use the SELECT statement only then we can do this way by filtering -

sort i_kna1 by keyfields.

if i_kna1[] is not initial.

select * from vbpa

into table i_vbpa

for all entries in i_kna1

where kunnr = i_kna1-kunnr.

endif.

sort i_vbpa by keyfields.

if i_vbpa[] is not initial.

select * from vbuk

into table i_vbuk

for all entries in i_vbpa

where vbeln = i_vbpa-vbeln.

endif.

Regards

Chandralekha.

Re: Tune this Select Statement

Former Member — Mon, 04 Aug 2008 16:57:59 GMT

Hi Ian,

I didnt exactly get what you are trying to say.

Any more clues?

Thanks

Kiran

Re: Tune this Select Statement

Former Member — Mon, 04 Aug 2008 17:52:59 GMT

Hi Amit,

I cannot use a FM because I will have to loop at KAN1 Itab again which will bring down performance.

ANy more clues.

Thanks

Kiran

Re: Tune this Select Statement

Former Member — Mon, 04 Aug 2008 18:21:07 GMT

This message was moderated.

Re: Tune this Select Statement

Former Member — Mon, 04 Aug 2008 19:13:52 GMT

Simply run a performance trace on the "sold-to party" search functionality for VA02.

Rob

Re: Tune this Select Statement

ThomasZloch — Mon, 04 Aug 2008 20:35:16 GMT

That trace will probably reveal that table VAKPA is the one to use when trying to quickly find sales orders for given partner numbers.

Thomas

Re: Tune this Select Statement

Former Member — Mon, 04 Aug 2008 20:44:59 GMT

Actually a view based on that table. Either should be OK.

Rob

Re: Tune this Select Statement

ian_maxwell2 — Mon, 04 Aug 2008 21:28:47 GMT

>I didnt exactly get what you are trying to say.

By going to ST05 you can turn on tracing, run your select and then look at what is called the explain plan from the DB. This explain plan will reveal everything that we'll need in order to tell why it is going slowly.

>SELECT a~vbeln " Sales Order

>a~kunnr " Customer Number

>b~gbstk " Processing Status

>b~cmgst " Status of credit checks

>FROM vbpa AS a INNER JOIN vbuk AS b

>ON a_{vbeln EQ b}vbeln

>NTO TABLE l_i_vbpa_vbuk

>FOR ALL ENTRIES IN i_kna1

>WHERE a~kunnr EQ i_kna1-kunnr.

>SORT : l_i_vbpa_vbuk.

Taking another look at your SQL statement and checking how the tables are setup in a standard SAP environment, I would say that the reason it is running slowly is because the VBPA table has no secondary indexes and therefore searchs by Kunnr are going to always be exhaustive and sequential. The VBPA table is optimized for the retrieval of records based on knowing the document number. In addition to this, as you'll see in a trace, becuase it is using For All Entries it is actually chunked into a number of executions of the SQL statement usually each one executing for around 4 or 5 Kunnrs, each of these executions will scan the entire table each time because each chunk executes independently of the others. In that case the for all entires is atleast some what better then individual select singles in a loop (by a factor of 4 to 5), but the fact that it doesn't have an index is going to cause you issues no matter what.

The join itself would be ok if it was able to use an index for Kunnr because the VBUK table will be joined in based on the values of VBELN which is a key.

One of the options that I would give you is the putting of a new secondary index on the VBPA table on the fields MANDT, and KUNNR. Another better option is to take a look at the VAKPA table (Sales Index: Orders by Partner Function) which is optimized to select based on Kunnr. This table VAKPA is optimized for exactly what you want.

This will spead things up for you a lot, because if you think your current select statement is slow you should see how slow it will be as the VBPA table grows in size over time, since it will mean that every search will have to sequentially go through more records:

>SELECT a~vbeln " Sales Order

>a~kunnr " Customer Number

>b~gbstk " Processing Status

>b~cmgst " Status of credit checks

>FROM VAKPA AS a INNER JOIN vbuk AS b

>ON a_{vbeln EQ b}vbeln

>NTO TABLE l_i_vbpa_vbuk

>FOR ALL ENTRIES IN i_kna1

>WHERE a~kunnr EQ i_kna1-kunnr.

>SORT : l_i_vbpa_vbuk.

Note - Only difference is the changeing of VBPA to VAKPA.

Even if you do what one of the other suggestions said to to and split the join into two selects via a For All Entries, you would still have this same issue unless you used VAKPA instead of VBPA becuase the first select would be doing sequeuntial reads.

On a side note, something to start doing is using ST05 and read up on DB explain plans, it will allow you to quickly resolve issues like this.

Also, after this change if it doesn't work a lot faster, ask you DBA to run statistics on the tables VBUK and VAKPA. If the statistics are not up to date then the DB optimizer may make strange decisions on the join.

~Ian

Edited by: Ian Maxwell on Aug 4, 2008 5:29 PM

Re: Tune this Select Statement

Former Member — Wed, 06 Aug 2008 18:51:01 GMT

Hi Ian,

Thanks for your big explanation.

I tried with VAKPA too..

But I ended up with a solution to use a view VBAKUK.

Thanks everone for your time.

Kiran