fast execution

[Former Member]

Hi experts,

m sending u a part of code that i have created but it is taking a lot of time that makes the executin very slow..Can u plz tell me some other alternatives that can improve its performance..

select * into corresponding fields of table customer
                                              from kna1
*                                              AS A INNER JOIN KNVP AS B ON A~KUNNR = B~KUNN2
                                              where regio in regio.
*                                              AND PARVW = 'ZA'.
    clear del.
    del =  sy-subrc .
    sort customer by kunnr.
    delete adjacent duplicates from customer.

*    IF DEL = 0.

      select a~bukrs a~gjahr a~belnr  a~budat a~gjahr a~blart b~kunnr b~buzei b~shkzg b~sgtxt b~bldat b~zuonr b~dmbtr b~pswsl b~zterm b~vbeln
                                     into corresponding fields of table itab_bsid
                                     from bkpf as a inner join bsid as b on a~belnr = b~belnr and a~gjahr eq b~gjahr
                                       and a~mandt = b~mandt and a~bukrs = b~bukrs
                                       for all entries in customer
                                       where  a~budat le p_date
                                       and   a~bukrs eq p_bukrs
                                       and   ( b~umskz eq space or b~umskz eq 'A')
                                       and   b~kunnr eq customer-kunnr.


      select  a~bukrs a~gjahr a~belnr  a~budat a~gjahr a~blart b~kunnr  b~buzei b~shkzg b~sgtxt b~bldat b~zuonr b~dmbtr b~pswsl b~zterm b~vbeln
                                                  into corresponding fields of table itab_bsad
                                                  from bkpf as a inner join bsad as b on a~belnr = b~belnr and a~gjahr eq b~gjahr
                                                    and a~mandt = b~mandt and a~bukrs = b~bukrs
                                                    for all entries in customer
                                                    where  a~budat le p_date
                                                    and   a~bukrs eq p_bukrs
                                                    and   ( b~umskz eq space or b~umskz eq 'A')
                                                    and   b~kunnr eq customer-kunnr.

thanks,

Raman

Added code tags and moved to the correct forum

Edited by: Rob Burbank on Mar 9, 2009 9:23 AM

[Former Member]

Hi,

select * into corresponding fields will not give a good performance,instead declare a field string with the fields which are required.

And use the select statement with those fields without using into corresponding fields.

Regards,

jaya

[Former Member]

Hi,

Firstly,

In the select queries try removing into corresponding fields of by mentioning the field names instead of * and also the field names should be in the same order as in the internal table, as they are very costly statements with respect to runtime,

Then try ignoring joins as even they hit on runtime, instead split the select query and with internal tables try removing unnecessary documents

Correcting the above two statements will surely decrease runtime to some extent

Regards,

Siddarth

[Former Member]

Hi,

Its better if you can use as less "CORRESPONDING" statements you can. Try populating your internal table without the "CORRESPONDING" statements. See the order in which you have declared the fields in your internal table and see if you can populate your internal table in that order (even while joining), that should improve performance.

Another important issue is the joining part, taking values directly from the database each time. The more you hit the database the more there are performance issues. Instead take all values you require at once using SELECT * to specific internal tables and then join the internal tables to get the final table you require.

Regards.

Edited by: rajan roy on Mar 9, 2009 7:26 AM

[Former Member]

hi,

1. dont use * for feching custmer related data.

use specific fields selection and put them in order as in kna1.

remove the corresponding fields of stmt.

use.

select field1 filed2 field3......... from kna1 into table t_kna1

where condition.

2. don't use inner join b/w bsid and bkpf or bsad and bkpf.

select specific fields of bsid and use for all entries in t_kna1 .

same for bsad.

select specifc fields of bkpf on the basis of bsid using for all entris on bsid.

and same for fillds corresponding to bsad.

both bsid and bsad entries append in final table.

no need of fetching the fields of bkpf as all fields that u mentioned are exist in bsid and bsad table.

cheers

regards

vijay dwivedi

Edited by: vijay dwivedi on Mar 9, 2009 2:36 AM

[Former Member]

Hi,

I suppose three things u can do:

1. Remove select* : create a structure with required field .

2. Fetch fields with out adding CORRESPONDING in select query.

3. Use for all entries inpite of inner join.

Hope for the best.

Cheers,

Rudhir

[Former Member]

Hi Raman,

where a~budat le p_date

and a~bukrs eq p_bukrs

you can find these fields in BSIK/ BSAK as well then why are you going for a Join ?

If possible dont use the join and use the fields from table Bsik bsak directly.

This will help you considerably in your performance.

Revert.

[Sm1tje]

Nice for all experts to give you some pointers, but have you already identified where the 'pain' is, which of these select statements? That way it will be possible to give a more precise answer.

But for starters, try to use the BUKRS field first in select statement, in stead of BUDAT, loose the LE statement in select (do this is next step). If possible try to use an index.

BTW: Forget about the MANDT, this fields is not relevant.

[Former Member]

It looks to me like the problem is in the first SELECT, where no index is used.

Rob

[Former Member]

.... I would like to see one example program where the INTO CORRESPONDING FIELDS

really causes a performance program.

Please add SAP release, Database manufacturer and release.

In new release I would expect a 1% performance problem.

The problem with these statements is index support !!!!!!

Example: Search in large library just know the title, but nothing tells you where to start.

The INTO CORRESPONDING tells you how to order the copies of some pages.

Siegfried

[Former Member]

I guess the select *, corresponding fields will create perf issues. try to use =,< rather than eq or le bec it depends on the dbi. And rather than using OR in WHERE clause try to have ranges and use in operator.