Performance problem in This program

[Former Member]

Hi friends ,

Below is the one of the perform in my program which is taking more time .. Can you please suggest me to find a solution to take less time for execution.

Thanks in Adv .

Varma ..

DATA: l_count TYPE i.

SELECT m~customer

m~name

m~payer

t~txtmd

INTO TABLE gt_cust2

FROM /bi0/pcustomer AS m

JOIN /bi0/tcustomer AS t

ON m_{customer EQ t}customer

WHERE m~objvers EQ 'A'

AND m~accnt_grp NE space.

SORT gt_cust2 BY customer.

SELECT m~customer

m~name

m~payer

t~txtmd

INTO TABLE gt_cust

FROM /bi0/pcustomer AS m

JOIN /bi0/tcustomer AS t

ON m_{customer EQ t}customer

WHERE m~objvers EQ 'A'

AND m~accnt_grp IN s_ktokd

AND m~payer NE space.

IF sy-subrc = 0.

SORT gt_cust BY customer.

LOOP AT gt_cust.

CLEAR l_count.

LOOP AT gt_cust2 WHERE payer = gt_cust-payer.

ADD 1 TO l_count.

IF l_count > 1.

EXIT.

ENDIF.

ENDLOOP.

gt_soldto-customer = gt_cust-customer.

gt_soldto-payer = gt_payer-payer = gt_cust-payer.

IF l_count > 1.

• Payer found

CLEAR gt_cust2.

READ TABLE gt_cust2 WITH KEY customer = gt_cust-payer

BINARY SEARCH.

IF NOT gt_cust2-name IS INITIAL.

gt_payer-name = gt_cust2-name.

ELSE.

gt_payer-name = gt_cust2-txtmd.

ENDIF.

ELSE.

• Stand alone customer

gt_soldto-payer = gt_payer-payer = p_nonpyr.

gt_payer-name = p_nonpnm.

ENDIF.

COLLECT: gt_soldto,

gt_payer.

ENDLOOP.

SORT: gt_soldto,

gt_payer.

ENDIF.

[Former Member]

hi

good

check with following things in your progam

1-Use Move corresponding in all the select statement.

2-If you r reading single field from the table than mention that field name in the select statement rather than select all the fields.

thanks

mrutyun^

[Former Member]

Hi Varma,

Always use Joins in an appropriate situation. Always split the complex and time consuming queries and use FOR ALL ENTRIES instead of using JOINS. Try to write your code as below, I think this may help you. Try to avoid nested LOOPs, instead use

LOOP AT ITAB1......

READ TABLE ITAB2.......

.....................................

.....................................

ENDLOOP.

SELECT customer name payer FROM /bi0/pcustomer INTO TABLE it_pcustomer

WHERE objvers EQ 'A'

AND accnt_grp IN s_ktokd

AND payer NE space.

• if you don't want duplicates in IT_PCUSTOMER uncomment below two lines

• sort it_pcustomer by customer.

• delete adjacent duplicates from it_pcustomer comparing customer.

IF it_pcustomer[] IS NOT INITIAL.

SELECT txtmd FROM /bi0/tcustomer INTO TABLE it_tcustomer

FOR ALL ENTRIES IN it_pcustomer

WHERE customer = it_pcustomer-customer.

ENDIF.

Reward with points if the solution is helpful.

Cheers,

Venkat.

[Former Member]

Hi Venkat ..

In above code m~accnt_grp IN s_ktokd in one select is there and

m~accnt_grp NE space is in another select

How can we do it ???

[Former Member]

You have a couple of potential problems - the SELECTs and the nested LOOPs. Have you done a performance trace to pinpoint the problem?

Rob

[Former Member]

hi varma,

use for all entries instead of inner join,

and try to avoid nested loops instead u can use read statement in the loop.

regards,

seshu.

[Former Member]

hi,

just remove joins and use for all entries.

if sy-subrc = 0.

use delete adjacent duplicates from itab comparing key fields.(it will increase performance)

then write another select statement.

endif.

some tips:

Always check the driver internal tables is not empty, while using FOR ALL ENTRIES

Avoid for all entries in JOINS

Try to avoid joins and use FOR ALL ENTRIES.

Try to restrict the joins to 1 level only ie only for tables

Avoid using Select *.

Avoid having multiple Selects from the same table in the same object.

Try to minimize the number of variables to save memory.

The sequence of fields in 'where clause' must be as per primary/secondary index ( if any)

Avoid creation of index as far as possible

Avoid operators like <>, > , < & like % in where clause conditions

Avoid select/select single statements in loops.

Try to use 'binary search' in READ internal table. Ensure table is sorted before using BINARY SEARCH.

Avoid using aggregate functions (SUM, MAX etc) in selects ( GROUP BY , HAVING,)

Avoid using ORDER BY in selects

Avoid Nested Selects

Avoid Nested Loops of Internal Tables

Try to use FIELD SYMBOLS.

Try to avoid into Corresponding Fields of

Avoid using Select Distinct, Use DELETE ADJACENT

Go through the following Document

Check the following Links

http://www.sapgenie.com/abap/performance.htm

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTunin

[Former Member]

Hi there

Try to do the following:

DATA: l_count TYPE i.

SELECT m~customer

m~name

m~payer

m~accnt_grp

t~txtmd

INTO TABLE gt_cust

FROM /bi0/pcustomer AS m

JOIN /bi0/tcustomer AS t

ON m_{customer EQ t}customer

WHERE m~objvers EQ 'A'

AND m~accnt_grp IN s_ktokd

AND m~payer NE space.

if sy-subrc EQ 0.

DELETE gt_cust where accnt_grp EQ space.

SORT gt_cust BY payer.

clear l_count.

LOOP AT gt_cust .

gt_soldto-customer = gt_cust-customer.

gt_soldto-payer = gt_payer-payer = gt_cust-payer.

ADD 1 TO l_count.

if l_count = 1.

g_payer = gt_cust-payer.

else.

if g_payer = gt_cust-payer.

IF NOT gt_cust-name IS INITIAL.

gt_payer-name = gt_cust-name.

ELSE.

gt_payer-name = gt_cust-txtmd.

ENDIF.

clear : g_payer ,

l_count.

ELSE.

• Stand alone customer

gt_soldto-payer = gt_payer-payer = p_nonpyr.

gt_payer-name = p_nonpnm.

ENDIF.

COLLECT: gt_soldto,

gt_payer.

ENDIF.

ENDLOOP.

ENDIF.

SORT: gt_soldto,

gt_payer.

i have not done a syntax check for this. But this should help you in some way.

cheers

shivika