topic Performance issue in Application Development and Automation Discussions

Performance issue

Former Member — Thu, 07 Aug 2008 08:30:17 GMT

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

Re: Performance issue

Former Member — Thu, 07 Aug 2008 08:31:52 GMT

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

Re: Performance issue

Former Member — Thu, 07 Aug 2008 08:33:34 GMT

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

Re: Performance issue

Former Member — Thu, 07 Aug 2008 08:35:52 GMT

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.

Re: Performance issue

vinod_vemuru2 — Thu, 07 Aug 2008 08:39:50 GMT

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

Re: Performance issue

Former Member — Thu, 07 Aug 2008 08:40:42 GMT

This message was moderated.

Re: Performance issue

Former Member — Thu, 07 Aug 2008 08:45:10 GMT

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

Re: Performance issue

Former Member — Thu, 07 Aug 2008 08:46:15 GMT

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.

Re: Performance issue

Former Member — Thu, 07 Aug 2008 08:49:18 GMT

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

Re: Performance issue

Former Member — Thu, 07 Aug 2008 08:51:41 GMT

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

Re: Performance issue

vinod_vemuru2 — Thu, 07 Aug 2008 08:58:04 GMT

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.

Re: Performance issue

Former Member — Thu, 07 Aug 2008 09:25:17 GMT

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.