06-12-2006 10:44 PM
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 alifnr = blifnr
where a~bedat in so_bedat
and a~bsart in so_bsart
and bedat = sy-datum
and ( absart = 'V1' OR absart = '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.
06-13-2006 6:44 AM
<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
06-12-2006 10:54 PM
06-12-2006 11:43 PM
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
06-13-2006 2:06 AM
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 alifnr = blifnr
where a~bedat in so_bedat
and a~bsart in so_bsart
and bedat = sy-datum
and ( absart = 'V1' OR absart = '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 aebeln eq bebeln
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.
06-13-2006 2:57 AM
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,
06-13-2006 4:32 AM
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
06-13-2006 6:06 AM
Instead using "Inner Join",it's better using "<b>FOR ALL ENTRIES</b>".
And donot use "Into Corresponding Fields" ... use "Select Field1, Field2,..,..,.., <b>Into Table</b> ITAB".
instead of making directly internal table first make str then use"itab type standard table of str"
<b>reward points if it is helpful.</b>
06-13-2006 6:44 AM
<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