Performance issue

[Former Member]

Hi all,

The following is my select query.

it is taking a long time for execution.

SELECT kunnr bukrs belnr gjahr

FROM ebpp_ar_contact

INTO TABLE i_ebpp

FOR ALL ENTRIES IN i_knkk

WHERE kunnr = i_knkk-kunnr

AND spras = sy-langu.

There are 57 lac records in i_knkk.

it is checking all the records.

there are 677 records in ebpp_ar_contact.

it is fetching all the 677 records.

Can anybody tell me how can we increase the performance of this statement.

Thanks & Regards,

sudheer

[Former Member]

hi use more condtions in the where command ...then it is easy to fecth the records with high performance

[Former Member]

Use sequence of fields same as in database table EBPP_AR_CONTACT in select query.

i.e.

BUKRS

BELNR

GJAHR

KUNNR

sample code-

if not i_knkk[] is initial.

SELECT bukrs belnr gjahr kunnr

FROM ebpp_ar_contact

INTO TABLE i_ebpp

FOR ALL ENTRIES IN i_knkk

WHERE kunnr = i_knkk-kunnr

AND spras = sy-langu.

endif.

Regards,

Aparna Gaikwad

[Former Member]

Hi,

Optimize the code as follows:

change the order of fields in i_ebpp as in ebpp_ar_contact.

DELETE ADJACENT DUPLIATES FROM i_knk

COMPARING KUNNR.

IF NOT i_knkk[] IS INITIAL.

SELECT distnct bukrs belnr gjahr kunnr

FROM ebpp_ar_contact

INTO TABLE i_ebpp

FOR ALL ENTRIES IN i_knkk

WHERE kunnr = i_knkk-kunnr

AND spras = sy-langu.

ENDIF.

Then it will be executed much faster.

Regards,

Rama.

[vinod_vemuru2]

Hi Sudheer,

If driver table has 57L records then it will take time. But still u can increace the performance by these steps.

IF NOT i_knkk IS INITIAL.

SORT i_knkk BY kunnr.

li_knkk[] = i_knkk[].

DELETE ADJACENT DUPLICATES FROM li_knkk COMPARING kunnr.

SELECT kunnr bukrs belnr gjahr

FROM ebpp_ar_contact

INTO TABLE i_ebpp

FOR ALL ENTRIES IN li_knkk

WHERE kunnr = li_knkk-kunnr

AND spras = sy-langu.

ENDIF.

This definitely improves the performance. Also u r passing the last key field to this table.

If u don't pass first key then full table scan happens Check if u can pass the bukrs field. This tremendously improves the performance.

Also change the field list structure as suggested by others. It is also very important to have the field list in the same order as they appear in data base table.

Thanks,

Vinod.

Edited by: Vinod Reddy Vemuru on Aug 7, 2008 2:10 PM

[Former Member]

Hi Vinod,

Thanks for your quick reply.

There is no bukrs field in knkk table.

the only common field in these two tables is kunnr.

I dont understand the statement li_knkk] = i_knkk[.

is it li_knkk[] = i_knkk[].

Please explain.

Thanks & Regards,

Sudheer

Edited by: sudheer kumar on Aug 7, 2008 10:53 AM

[vinod_vemuru2]

Hi,

Probably it got printed wrongly. It is internal table assignment.

When we use for all entries it is very important that we delete the duplicates from driver table. Why i am taking another local table is u might be using i_knkk soem where else. So u don't want to delete the duplicates based on customer. Thats why one extra table.

It is just assignment.

li_knkk[ ] = i_knkk[ ].

It is not necessary that ur where clause fields should have comparision from i_knkk. Probably u can give bukrs as selection screen parameter so that u can use it in where clause.

Hope u understand.

Another option could be a batch job provided u don't have any front end interactions like File download to PC, Grid ALV etc.

Thanks,

Vinod.

[Former Member]

Hi Vinod,

Thanks for ur reply.

Here im working in a support project and for this there is no data in development and data exists in quality but i cannot modify the program in quality. This is the problem im facing.

Any way your is answer is very much helpful to me

Thank you very much.

Thanks & Regards,

Sudheer.

[Former Member]

This message was moderated.

[Former Member]

Hi Sudheer,

Check whether i_knkk is initial or not. If this table is initial all the conditions will fail and the select behaves like SELECT * and fetches all the records.

Change your code as:

If not i_knkk[] is initial.

SELECT kunnr bukrs belnr gjahr
FROM ebpp_ar_contact
INTO TABLE i_ebpp
FOR ALL ENTRIES IN i_knkk
WHERE kunnr = i_knkk-kunnr
AND spras = sy-langu.

endif.

Regards,

Chandra Sekhar

[Former Member]

hai,

u can use the primary fields in the where condition or else u can create the indexes for the fields u want it in where condition then it will fetch the records fastly.

try it it may work.

with regards,

R.Dhineshraj.

[Former Member]

I would recomend you to change ur programs in such a way

Data begin of it-kunnr occurs 0,

kunnr type kna1-kunnr,

end of it_kunnr.

Loop at it-knkk.

Move-Corresponding i_knkk to it_kunnr.

Append it_kunnr.

Clear it_kunnr.

endloop.

sort it_kunnr.

Delete Adjacent Duplicates From it_kunnr.

SELECT kunnr bukrs belnr gjahr

FROM ebpp_ar_contact

INTO TABLE i_ebpp

FOR ALL ENTRIES IN it_kunnr

WHERE kunnr = it_kunnr-kunnr AND

spras eq sy-langu.

rgds

rajesh