Performance issue

[Former Member]

Hi all,

I have developed a Program to fetch data from ztable and modify the records in that table.

In development server it had successfully updated 3000 records.where as when it is migrated to Quality server,where in it has to fetch 37lakhs of records and update the same.it is not doing so.

We have scheduled the job.and it is running for more than 15000 seconds and still running..

Please suggest some performance tuning methods

Thanks,

Balaji.

[Former Member]

Hi

remove into corresponding if any are there

use field in same sequence of the table position

use the more fields in where conditions

use the primary keys in select

use modify at itab not using loop and update the ZTAB

Before using for all entries if any duplicates are there remove it and use by nmoving into a dummy itab

also use Binaryserach to read

Regards

Shiva

[Former Member]

Post your code. Are you getting some time out error or memory dumo error while running in background. Playing with 37 lakhs of records is not an easy game. So you cannot expect a very high performance.

[Former Member]

Hi,

I thank one and all for their valuable replies,

i have used fields in the same order in the table also used jey field for selection.

The below is the code for your reference.the local table z_pallet_details has only one key field palletid.

*----

---

• Definition of Tables

*----

---

tables: z_pallet_details. " QA details for storage unit number

*----

---

• Types Declaration

*----

---

types begin of type_pallet_details.

include structure z_pallet_details.

types end of type_pallet_details.

types:begin of type_s022,

arbpl type s022-arbpl,

aufnr type s022-aufnr,

end of type_s022.

*----

---

• Data Definition of Internal Tables.

*----

---

data: i_pallet_details type table of type_pallet_details with header line ,

i_s022 type table of type_s022 with header line.

*----

---

• Definition of Select-Options

*----

---

selection-screen begin of block b1 with frame title text-001.

select-options:s_pltid for z_pallet_details-zpalletid,

s_date for z_pallet_details-zprod_date.

selection-screen end of block b1.

======================================================================

*-I-N-I-T-I-A-L-I-Z-A-T-I-O-N--

---

======================================================================

initialization.

======================================================================

*-A-TS-E-L-E-C-T-I-O-NS-C-R-E-E-N--

---

======================================================================

at selection-screen.

perform validate_input.

======================================================================

*-S-E-L-E-C-T-I-O-NS-C-R-E-E-NO-U-T-P-U-T----

---

======================================================================

at selection-screen output.

======================================================================

*-S-T-A-R-T-O-F-S-E-L-E-C-T-I-O-N--

---

*======================================================================*

start-of-selection.

**-- read all Existing records from table z_pallet_details.

perform read_data.

**-- Update records

perform process_data.

======================================================================

*-E-N-D-O-F-S-E-L-E-C-T-I-O-N--

---

======================================================================

end-of-selection.

&----

---

*& Form validate_input

&----

---

*Subroutine that performs Selection-Screen Validations

----

---

• --> p1 text

• <-- p2 text

----

---

form validate_input .

• check for pallet id.

if not s_pltid[] is initial.

select single zpalletid

into z_pallet_details-zpalletid

from z_pallet_details

where zpalletid in s_pltid.

if sy-subrc <> 0.

• Invalid Pallet id.

message e002(zp) with text-002.

endif.

endif.

endform. " validate_input

&----

---

*& Form read_data

&----

---

• Getting all the records from z_pallet_details table and *

• related data from s022 table *

----

---

• --> p1 text

• <-- p2 text

----

---

form read_data .

refresh i_pallet_details.

if i_pallet_details[] is initial.

select * from z_pallet_details

into table i_pallet_details

***--Begin of Changes by Balaji for Mani on 03/17/2008 for Task 2778501/DEVK963137

**Fetch by KEY field

• WHERE aufnr <> space

• AND zpalletid IN s_pltid

where zpalletid in s_pltid

and zprod_date in s_date

and aufnr <> space

and arbpl eq space.

***--End of Changes by Balaji on 03/17/2008

endif.

refresh i_s022.

if not i_pallet_details[] is initial.

select arbpl aufnr from s022

into table i_s022

for all entries in i_pallet_details

where aufnr eq i_pallet_details-aufnr.

endif.

endform. " read_data

&----

---

*& Form process_data

&----

---

• Updating the table z_pallet_details with field workcenter.

----

---

• --> p1 text

• <-- p2 text

----

---

form process_data .

data:ctr_rec type i.

clear ctr_rec.

sort i_s022 by arbpl aufnr.

loop at i_pallet_details where arbpl eq space.

clear i_s022.

read table i_s022 with key aufnr = i_pallet_details-aufnr binary search.

if sy-subrc eq 0.

***--Begin of changes by Balaji for Mani on 03/17/2008 for Task 2778501/DEVK963137

if i_s022-arbpl is initial.

continue.

endif.

***--End of changes by Balaji for Mani on 03/17/2008

i_pallet_details-arbpl = i_s022-arbpl.

modify i_pallet_details transporting arbpl.

***--Begin of changes by Balaji for Mani on 03/18/2008 for Task 2778501/DEVK963158

• UPDATE z_pallet_details FROM i_pallet_details.

• ctr_rec = ctr_rec + 1.

• WRITE:/ 'Work Center was Updated for Pallet Id: ',i_pallet_details-zpalletid.

ctr_rec = ctr_rec + 1.

***--End of changes by Balaji for Mani on 03/18/2008

endif.

endloop.

***--Begin of changes by Balaji for Mani on 03/18/2008 for Task 2778501/DEVK963158

**Updating the Database table Z_PALLET_DETAILS.

if not i_pallet_details is initial.

update z_pallet_details from table i_pallet_details.

if sy-subrc eq 0.

commit work and wait.

endif.

endif.

***--End of changes by Balaji for Mani on 03/18/2008

if ctr_rec = 0.

write 'No Record was Updated'.

else.

write:/'Totally',ctr_rec,'Record(s) were updated'.

endif.

endform. " process_datafirst

[Former Member]

Hi

If the table i_pallet_details is having duplicate entries then move it one more itab and delete the duplicate entries and use new itab in for all entries

Regards

Shiva

[Former Member]

Hi,

I didn't get any error while running in background .it's still running for more than 80Ksec and still running and running.....

and the internal table i_pallet_details donot have any duplicate as the palletid is unique....

Let the job continue or do i need to make any changes in the code?

Thanks,

Balaji

[Former Member]

Hi,

Check if i_pallet_details-aufnr has duplicate entries.

if you are expecting more records to be fetched then split the records fetched...modify your select so that the required data is fetched in 2 or more iterations.

Regards,

Madhumitha