12-26-2014 10:44 AM
Hi Guys,
I have a program which updates tcodes from tstc table into a Ztable.
It has to be run on daily basis as a background job.
My problem is that if I create a new Tcode it will update only that tcode into custom table but before that it compares all the entries of tstc and ztable which is impacting the overall performance.
I means if I am having 100000 entries in both the tables and if I create a new tcode then to update only that record, I have to create a loop which will iterate for 100000 times.
Can anyone please suggest me any method which will improve the performance of report?
Thanks,
Neha
12-26-2014 5:36 PM
hi neha,
If you use hashed internal table then it is working very fast.
I have tried with this code. You can have a look at it.
------------------------------------------------------------------------
REPORT ZR_TSTC.
TABLES: TSTC.
DATA: ITAB TYPE HASHED TABLE OF TSTC WITH UNIQUE KEY TCODE,
ZITAB TYPE HASHED TABLE OF TSTC WITH UNIQUE KEY TCODE
.
data: count TYPE i.
field-symbols:
<fs> type TSTC,
<fs2> type TSTC.
START-OF-SELECTION.
SELECT * INTO TABLE ITAB FROM TSTC .
SELECT * INTO TABLE ZITAB FROM ZTSTC.
LOOP AT ITAB assigning <fs>.
READ TABLE zitab ASSIGNING <fs2> WITH KEY tcode = <fs>-tcode.
IF SY-SUBRC NE 0.
INSERT INTO ZTSTC VALUES <fs>.
IF SY-SUBRC EQ 0.
WRITE: 'Record inserted', <fs>-tcode.
else.
WRITE: 'unable to insert record in tstc ', <fs>-tcode.
ENDIF.
count = count + 1.
ENDIF.
ENDLOOP.
IF count = 0.
write ' both the contents are same'.
ELSE.
write: / 'Different ', COUNT.
ENDIF.
12-26-2014 11:13 AM
Hi Neha,
Follow the below step / logic.
1. Do the Query on your Z Table.
2. If Z table internal table contains Zero records,
3. Do the Selected * query from TSTC table and modify the all records in Z table.
4. If Z table is not empty, take the all data in internal table.
5. Do the query on TSTC with FOR ALL ENTRIES and where condition would be TCODE NE internal_table-Tcode.
Regards.
Praveer.
12-26-2014 11:54 AM
Hi Pravin,
Thanks for your reply but I have already tried this solution and it is impacting too much on the performance of report and giving TimeOut dump.
Code is executing smoothly without the addition of FOR ALL ENTRIES.
The performance is impacting when I am comparing both the ztable data and tstc data.
Please check the following code and suggest me the way to improve the performance:
12-26-2014 12:01 PM
Hi Neha,
Try
Instead of work area Assign Field symbols and check.
Hope it helpful.
Regards,
Venkat.
12-26-2014 12:18 PM
Hi Neha,
Use the below code, hope it could fast the process.
declare the variable as per below logic.
Star-of-selection
select * from ztcode into table it_ztcode.
if lines( it_ztcode ) eq 0.
select tcode pgmna from tstc into table it_ztcode.
if lines( it_ztcode ) ne 0.
modify table ztcode from table it_ztcode.
endif.
else.
select tcode pgmna from tstc into table it_ztcode1.
loop at it_ztcode1 assign <ws>.
l_index = sy-tabix.
read table it_ztcode1 assign <ws1> with key tcode = <ws>-tcode.
if sy-subrc eq 0.
delete it_ztcode1 index l_index
endif.
endloop.
if lines( it_ztcode1) ne 0.
modify ztcode from table it_ztcode1.
endif.
Regards.
Praveer
12-26-2014 5:36 PM
hi neha,
If you use hashed internal table then it is working very fast.
I have tried with this code. You can have a look at it.
------------------------------------------------------------------------
REPORT ZR_TSTC.
TABLES: TSTC.
DATA: ITAB TYPE HASHED TABLE OF TSTC WITH UNIQUE KEY TCODE,
ZITAB TYPE HASHED TABLE OF TSTC WITH UNIQUE KEY TCODE
.
data: count TYPE i.
field-symbols:
<fs> type TSTC,
<fs2> type TSTC.
START-OF-SELECTION.
SELECT * INTO TABLE ITAB FROM TSTC .
SELECT * INTO TABLE ZITAB FROM ZTSTC.
LOOP AT ITAB assigning <fs>.
READ TABLE zitab ASSIGNING <fs2> WITH KEY tcode = <fs>-tcode.
IF SY-SUBRC NE 0.
INSERT INTO ZTSTC VALUES <fs>.
IF SY-SUBRC EQ 0.
WRITE: 'Record inserted', <fs>-tcode.
else.
WRITE: 'unable to insert record in tstc ', <fs>-tcode.
ENDIF.
count = count + 1.
ENDIF.
ENDLOOP.
IF count = 0.
write ' both the contents are same'.
ELSE.
write: / 'Different ', COUNT.
ENDIF.
12-29-2014 4:59 AM
Hi ,
Use of hashed table resolved the problem.
Thanks a lot.
Regards,
Neha
12-26-2014 9:21 PM
Hi,
Can you please explain why you need this Z table? What do you store in it (besides tcode)?
There may be a much simpler solution.
cheers
Paul
12-29-2014 4:09 AM
Hi Paul,
This Ztable is further used by BI module for some reports and this table stores only tcode and program name of tcode.
Regards,
Neha
12-29-2014 5:06 AM
If that's all it stores, why not just use table TSTC? It has both those fields.
Or, if you need to have a Z table for some reason, why not just refresh it with the complete contents of TSTC each time.
That would be much faster than comparing each item, hashed table or not.
cheers
Paul