Performance problem in select query again

[Former Member]

Hi Experts,

I have another performance issue with this.

Select matnr werks from marc

Into table i_marc

For all entries in i_mara

Where matnr eq i_mara-matnr.

Loop at I_mara.

Read table i_marc with key matnr = I_mara-matnr.

Endloop.

When I showed this code to my boss he suggested me to do some performance tuning.But i do not have any idea what to do.I am already using for all entries.

Please suggest.

Neeti

[Former Member · Accepted solution]

Hi Neeti,

Generally we should sort the table first before using for all entries, so that you can use binary search in the read command.This follows binary search algorithm and thus improves the performance.Try this.

Select matnr from mara

into table i_mara

Where matnr in s_matnr.

Select matnr werks from marc

Into table i_marc

For all entries in i_mara

Where matnr eq i_mara-matnr.

<b>Sort I_marc by matnr.</b>

Loop at I_mara.

Read table i_marc with key

matnr = I_mara-matnr

<b>binary search.</b>

Endloop.

Hope it helps,

Suruchi

[Former Member]

As Naresh has pointed out, the problem is in your loop statement, but there's also a logic error as well. Since marc may have multiple entries for a material, the read will pick up only the first.

Rob

[Former Member]

Read statement fetches the record from the internal table using implicit key or explicit key. When no key or index is specified for the search criteria the key in the WA of the internal table is used implicitly for searching. Here the key or index is explicitly specified which is used for searching the records from internal table. This is faster than an implicit search.

When reading a single record in an internal table, the READ TABLE WITH KEY is not a direct READ. Therefore, SORT the table and use READ TABLE WITH KEY BINARY SEARCH.

Select matnr from mara

Into table i_mara

Where matnr in s_matnr.

Select matnr werks from marc

Into table i_marc

For all entries in i_mara

Where matnr eq i_mara-matnr.

Sort I_marc by matnr.

Loop at I_mara.

-

---

Read table i_marc with key

matnr = I_mara-matnr

binary search.

-

---

Endloop.

[Former Member]

Hi Neeti,

Do check the contents of the internal table i_mara before using it in the SELECT statment..coz if i_mara is empty it will fetch all the records!!

use: IF i_mara is not initial.

select..

endif

[Former Member]

Hi,

I do not understand how will i check the performance of my program.Any tools made for that or it is all hit and trial???

Neeti

[Former Member]

Use SE30 and ST05 to analyze the performance.

Cheers

VJ

Message was edited by: Vijayendra Rao

[Former Member]

Hi Neeti,

You can check the performance of the program using the t-code se30.You can also make use of the SAP Code Inspector which has the t-code SCI.

Regards,

SP.

[former_member927251]

Hi Neeti,

I have a very good document on the same.

Just give me your email id, I will send it.

Also, please reward some points if it helps.

Regards,

Amit Mishra

[Former Member]

Hi Neeti,

Go to se38.

Type the name of your program there.

Goto ....

System>Utilities>Runtime analysis-->Execute.

And then in the next screen ANALYSE

You can see the execution time in microseconds there.

Also in the program you can goto

se38>program name>program>check>Code inspector-->

In code inspector you have various performance checks.

Regards,

Suruchi

[Former Member]

Hi Neeti,

To add something to what I have said before.

In SE30(ABAP Runtime Analysis),

First give the program name and click execute.

Now come back and click on Analyse.

This will give you the performance of the program.

Anything given in RED barchart means you need to fine tune it.

Code Inspector inspects your code and gives you information regarding the type of issue that you are facing(like performance, syntax error) so that you can correct it later.

Regards,

SP.

[Former Member]

Hi,

Amit, I would be grateful if you can send the document to neetijaju@yahoo.com

Thanx in advance.

Thanx to the others too for your valuable help

SDN forum is great.

Would continue visiting it.

Neeti

[former_member927251]

Hi Neeti,

I have already sent you the doc.

Please confirm.

Also, mark all helpful answers, since we all have given our valuable time from our busy schedule. This is the only way to appreciate the efforts on SDN forum.

Regards,

Amit Mishra

[Former Member]

Hi Neeti,

Since there is only two tables you are looking up use a join to restrict the data fetched from the databse. Alternatively try using s subquery.

Cheers

VJ

[Former Member]

Hello Neeti,

U r accessing MARC table with just material number. If u look at the table MARC it has another key field werks. Is it possible for u to get this field also in select and in READ??

Also I am not sure what exactly r u trying to achieve using this code.

[former_member927251]

Hi,

Sort the table that you are reading in the loop with all the fields that are used in 'WITH' clause and then read it using BINARY SEARCH at the end of the read statement.

Refer this :

Sort itab1 by field1.

Loop at itab2 into wa_itab2.

Read table itab1 into wa_itab1 with key field1 = wa_itab2-field1 BINARY SEARCH.

*Always do this check

if sy-subrc eq 0.

• Do whatever you want to do.

endif.

Endloop.

Hope this helps. Please reward if it helps you.