topic Re: performance issue..help me out in Application Development and Automation Discussions

performance issue..help me out

Former Member — Mon, 12 Jun 2006 21:44:31 GMT

Hi all,

when i am checking the performance issue SE30..i am seeing the database time is 94%...how to decrease the database time and increase performance issue...

for the below program...

TABLES : ekko.

DATA : lv_record_identifier(3) type c value '100',

lv_sp_id(3) type c value '100',

lv_sp_name(60) type c value 'HI',

lv_creation_date(8) type c,

lv_creation_time(6) type c,

lv_file_format(3) type c value 'BYE',

lv_file_version(3) type c value '1.1'.

DATA : lv_record_identifier1(3) type c value '110',

lv_org_id(4) type c value '1744',

lv_org_name(60) type c value 'SEE U'.

DATA : BEGIN OF lt_ekko OCCURS 0,

lv_record_identifier2(3) type c,

lv_org_id1(4) type c,

ebeln type ekko-ebeln,

bedat type ekko-bedat,

bsart type ekko-bsart,

lifnr type ekko-lifnr,

zsrm_pcnum type ekko-zsrm_pcnum,

name1 type lfa1-name1,

END OF lt_ekko.

DATA : lv_record_identifier3(3) type c value '130',

lv_org_id2(4) type c value '1744',

lv_org_name1(60) type c value 'go',

lv_detail_record_count(5) type c.

DATA : lv_record_identifier4(3) type c value '140',

lv_extract_record_count(5) type c,

lv_file_record_count(5) type c.

DATA : lv_record_count type i.

SELECT-OPTIONS : so_bedat for ekko-bedat,

so_bsart for ekko-bsart.

DATA : output_string1 type string,

output_string2 type string,

output_string3 type string,

output_string4 type string,

output_string5 type string.

CONSTANTS : c_tab TYPE x VALUE '09'.

lv_creation_date = sy-datum.

lv_creation_time = sy-uzeit.

concatenate lv_record_identifier

lv_sp_id

lv_sp_name

lv_creation_date

lv_creation_time

lv_file_format

lv_file_version

into output_string1

separated by c_tab.

write 😕 output_string1.

concatenate lv_record_identifier1

lv_org_id

lv_org_name

into output_string2

separated by c_tab.

write 😕 output_string2.

select a~ebeln

a~bedat

a~bsart

a~zsrm_pcnum

a~lifnr

b~name1

into corresponding fields of table lt_ekko

from ekko as a inner join lfa1 as b

on a_{lifnr = b}lifnr

where a~bedat in so_bedat

and a~bsart in so_bsart

and bedat = sy-datum

and ( a_{bsart = 'V1' OR a}bsart = 'B1' )

and a~zsrm_pcnum is not null.

lv_record_count = sy-dbcnt.

lv_detail_record_count = lv_record_count.

lv_extract_record_count = lv_record_count - 2.

lv_file_record_count = lv_record_count + 4.

loop at lt_ekko.

clear output_string3.

lt_ekko-lv_record_identifier2 = '120'.

lt_ekko-lv_org_id1 = '1744'.

concatenate lt_ekko-lv_record_identifier2

lt_ekko-lv_org_id1

lt_ekko-ebeln

lt_ekko-bedat

lt_ekko-bsart

lt_ekko-lifnr

lt_ekko-name1

into output_string3

separated by c_tab.

write 😕 output_string3.

endloop.

concatenate lv_record_identifier3

lv_org_id2

lv_org_name1

lv_detail_record_count

into output_string4

separated by c_tab.

write 😕 output_string4.

*concatenate for File Footer Record

concatenate lv_record_identifier4

lv_extract_record_count

lv_file_record_count

into output_string5

separated by c_tab.

write 😕 output_string5.

Re: performance issue..help me out

RichHeilman — Mon, 12 Jun 2006 21:54:17 GMT

Vj, I'm looking into your issue. Meanwhile, can you please revist your other posts and award helpful answers and mark as solved if solved completely. Thanks.

Regards,

Rich Heilman

Re: performance issue..help me out

RichHeilman — Mon, 12 Jun 2006 22:43:55 GMT

Your select statement isn't really performing too badly in my system. How long does it take in your system? There is an index on EKKO which includes BEDAT, but the first field is BSTYP. If you can use field in your WHERE clause it might help with performance. Also, in your WHERE clause you are restricting with SO_BSART, but also filtering on two static values. I would suggest using one or the other in this case.




where  a~BSTYP = 'F'
   and a~bedat in so_bedat
*  and a~bsart in so_bsart
*  and bedat = sy-datum
   and ( a~bsart = 'V1' OR a~bsart = 'B1' ).
   and a~zsrm_pcnum is not null.

Regards,

Rich Heilman

Re: performance issue..help me out

Former Member — Tue, 13 Jun 2006 01:06:02 GMT

Hi all,

for the below code i want to insert fields for ekpo table also by using ekko-ebeln field as a selection to ekpo table.........BUT THE VALUES FOR EKPO TABLE ARE NOT DISPLAYED ???????? AND I WANT TO SELCT EKKN FIELDS ALSO BASED ON EBELN ITSELF HOW TO INSERT THE CODE .......PLEASE HELP ME........

TABLES : ekko.

DATA : lv_record_identifier(3) type c value '100',

lv_sp_id(3) type c value '100',

lv_sp_name(60) type c value 'HI',

lv_creation_date(8) type c,

lv_creation_time(6) type c,

lv_file_format(3) type c value 'BYE',

lv_file_version(3) type c value '1.1'.

DATA : lv_record_identifier1(3) type c value '110',

lv_org_id(4) type c value '1744',

lv_org_name(60) type c value 'SEE U'.

DATA : BEGIN OF lt_ekko OCCURS 0,

lv_record_identifier2(3) type c,

lv_org_id1(4) type c,

ebeln type ekko-ebeln,

bedat type ekko-bedat,

bsart type ekko-bsart,

lifnr type ekko-lifnr,

zsrm_pcnum type ekko-zsrm_pcnum,

name1 type lfa1-name1,

END OF lt_ekko.

DATA : BEGIN OF lt_ekpo OCCURS 0,

ebeln type ekpo-ebeln,

VRTKZ type ekpo-vrtkz,

AFNAM type ekpo-afnam,

TXZ01 type ekpo-txz01,

NETWR type ekpo-netwr,

MENGE type ekpo-menge,

KNTTP type ekpo-knttp,

MWSKZ type ekpo-mwskz,

END OF lt_ekpo.

DATA : BEGIN OF lt_ekkn OCCURS 0,

VPROZ type ekkn-vproz,

SAKTO type ekkn-sakto,

END OF lt_ekkn.

DATA : lv_record_identifier3(3) type c value '130',

lv_org_id2(4) type c value '1744',

lv_org_name1(60) type c value 'go',

lv_detail_record_count(5) type c.

DATA : lv_record_identifier4(3) type c value '140',

lv_extract_record_count(5) type c,

lv_file_record_count(5) type c.

DATA : lv_record_count type i.

SELECT-OPTIONS : so_bedat for ekko-bedat,

so_bsart for ekko-bsart.

DATA : output_string1 type string,

output_string2 type string,

output_string3 type string,

output_string4 type string,

output_string5 type string.

CONSTANTS : c_tab TYPE x VALUE '09'.

lv_creation_date = sy-datum.

lv_creation_time = sy-uzeit.

concatenate lv_record_identifier

lv_sp_id

lv_sp_name

lv_creation_date

lv_creation_time

lv_file_format

lv_file_version

into output_string1

separated by c_tab.

write 😕 output_string1.

concatenate lv_record_identifier1

lv_org_id

lv_org_name

into output_string2

separated by c_tab.

write 😕 output_string2.

select a~ebeln

a~bedat

a~bsart

a~zsrm_pcnum

a~lifnr

b~name1

into corresponding fields of table lt_ekko

from ekko as a inner join lfa1 as b

on a_{lifnr = b}lifnr

where a~bedat in so_bedat

and a~bsart in so_bsart

and bedat = sy-datum

and ( a_{bsart = 'V1' OR a}bsart = 'B1' )

and a~zsrm_pcnum is not null.

IF NOT lt_ekpo IS INITIAL.

select a~ebeln

b~VRTKZ

b~AFNAM

b~TXZ01

b~NETWR

b~MENGE

b~KNTTP

b~MWSKZ

into corresponding fields of table lt_ekpo

from ekko as a inner join ekpo as b

on a_{ebeln eq b}ebeln

where b~ebeln = lt_ekko-ebeln.

ENDIF.

lv_record_count = sy-dbcnt.

lv_detail_record_count = lv_record_count.

lv_extract_record_count = lv_record_count - 2.

lv_file_record_count = lv_record_count + 4.

loop at lt_ekko.

clear output_string3.

lt_ekko-lv_record_identifier2 = '120'.

lt_ekko-lv_org_id1 = '1744'.

concatenate lt_ekko-lv_record_identifier2

lt_ekko-lv_org_id1

lt_ekko-ebeln

lt_ekko-bedat

lt_ekko-bsart

lt_ekko-lifnr

lt_ekko-name1

lt_ekpo-VRTKZ

lt_ekpo-AFNAM

lt_ekpo-TXZ01

v_netwr

v_MENGE

lt_ekpo-KNTTP

lt_ekpo-MWSKZ

into output_string3

separated by c_tab.

write 😕 output_string3.

endloop.

concatenate lv_record_identifier3

lv_org_id2

lv_org_name1

lv_detail_record_count

into output_string4

separated by c_tab.

write 😕 output_string4.

concatenate lv_record_identifier4

lv_extract_record_count

lv_file_record_count

into output_string5

separated by c_tab.

write 😕 output_string5.

Re: performance issue..help me out

Former Member — Tue, 13 Jun 2006 01:57:49 GMT

Hi,

This is your problem,

select a~ebeln
a~bedat
a~bsart
a~zsrm_pcnum
a~lifnr
b~name1
into corresponding fields of table lt_ekko
from ekko as a inner join lfa1 as b
on a~lifnr = b~lifnr
where a~bedat in so_bedat
and a~bsart in so_bsart
* and bedat = sy-datum
and ( a~bsart = 'V1' OR a~bsart = 'B1' )
and a~zsrm_pcnum is not null.

Instead using "Inner Join",it's better using "FOR ALL ENTRIES". And "Into Corresponding Fields" replace with "Select Field1, Field2,..,..,.., Into Table ITAB".

For more detail step in using For All Entries, you can read this . And I believe it will be very helping you.

Regards,

Re: performance issue..help me out

Former Member — Tue, 13 Jun 2006 03:32:02 GMT

Just because SE30 shows a high percentage of database use doesn't automatically mean that the program is not performing well. Some programs are database intensive; some are CPU intensive. What really matters is if it takes too long.

Having said that, you have to make sure that so_bedat is not empty before running the select; otherwise, it will do a full table scan on EKKO.

Rob

Re: performance issue..help me out

Former Member — Tue, 13 Jun 2006 05:06:56 GMT

Instead using "Inner Join",it's better using "FOR ALL ENTRIES".

And donot use "Into Corresponding Fields" ... use "Select Field1, Field2,..,..,.., Into Table ITAB".

instead of making directly internal table first make str then use"itab type standard table of str"

reward points if it is helpful.

Re: performance issue..help me out

Former Member — Tue, 13 Jun 2006 05:44:19 GMT


<b>ranges: r_lifnr for lfa1-lifnr.</b>
step 1.
select a~ebeln
a~bedat
a~bsart
a~zsrm_pcnum
b~name1
into corresponding fields of table lt_ekko
from ekko 
where a~bedat in so_bedat
* and bedat = sy-datum
and  a~bsart in ('V1','B1' )
and a~zsrm_pcnum ne ''.

step2.
loop at it_ekko.
r_lifnr- sign = 'I'.
r_lifnr-optiomn = 'EQ'.
r_lifnr-low = it_ekko-lifnr.
append r_lifnr.
clear r_lifnr.
endloop.
sort r_lifnr.
delete adjacent duplicates from r_lifnr comparing low.
sort r_lifnr.
if r_lisnr[] is not initial.
selet lifnr name1
 from lfa1 
into corresponding fields of it_lfa1
where lifnr in r_lifnr.
endif.
after that update ur it_ekko with vendor name.
endif.
get the Vendor name /code for above selected Codes

dont check syntax error in the above code , its just sample code Only.

Regards

Prabhu