Need help in optimisation for a select query on a large table

[Former Member]

Hi Gurus

Please help in optimising the code. It takes 1 hr for 3-4000 records. Its very slow.

My Select is reading from a table which contains 10 Million records.

I am writing the select on large table and Retrieving the values from large tables by comparing my table which has 3-4 k records.

I am pasting the code. please help

Data: wa_i_tab1 type tys_tg_1 .

DATA: i_tab TYPE STANDARD TABLE OF tys_tg_1.

Data : wa_result_pkg type tys_tg_1,

wa_result_pkg1 type tys_tg_1.

SELECT /BIC/ZSETLRUN AGREEMENT /BIC/ZREB_SDAT /BIC/ZLITEM1 from

/BIC/PZREB_SDAT *******************THIS TABLE CONTAINS 10 MILLION RECORDS

into CORRESPONDING FIELDS OF table i_tab

FOR ALL ENTRIES IN RESULT_PACKAGE***************CONTAINS 3000-4000 RECORDS

where

/bic/ZREB_SDAT = RESULT_PACKAGE-/BIC/ZREB_SDAT

AND

AGREEMENT = RESULT_PACKAGE-AGREEMENT

AND /BIC/ZLITEM1 = RESULT_PACKAGE-/BIC/ZLITEM1.

sort RESULT_PACKAGE by AGREEMENT /BIC/ZREB_SDAT /BIC/ZLITEM1.

sort i_tab by AGREEMENT /BIC/ZREB_SDAT /BIC/ZLITEM1.

loop at RESULT_PACKAGE into wa_result_pkg.

read TABLE i_tab INTO wa_i_tab1 with key

/BIC/ZREB_SDAT =

wa_result_pkg-/BIC/ZREB_SDAT

AGREEMENT = wa_result_pkg-AGREEMENT

/BIC/ZLITEM1 = wa_result_pkg-/BIC/ZLITEM1.

IF SY-SUBRC = 0.

move wa_i_tab1-/BIC/ZSETLRUN to

wa_result_pkg-/BIC/ZSETLRUN.

wa_result_pkg1-/BIC/ZSETLRUN = wa_result_pkg-/BIC/ZSETLRUN.

modify RESULT_PACKAGE from wa_result_pkg1

TRANSPORTING /BIC/ZSETLRUN.

ENDIF.

CLEAR: wa_i_tab1,wa_result_pkg1,wa_result_pkg.

endloop.

[Former Member]

Hi,

1) RESULT_PACKAGE internal table contains any duplicate records or not bassed on the where condotion like below

2) Remove the into CORRESPONDING FIELDS OF table instead of that into table use.

refer the below code is

RESULT_PACKAGE1[] = RESULT_PACKAGE[].
sort RESULT_PACKAGE1 by /BIC/ZREB_SDAT AGREEMENT /BIC/ZLITEM1.
delete adjustant duplicate form RESULT_PACKAGE1 comparing /BIC/ZREB_SDAT AGREEMENT /BIC/ZLITEM1.

SELECT /BIC/ZSETLRUN AGREEMENT /BIC/ZREB_SDAT /BIC/ZLITEM1 
from /BIC/PZREB_SDAT 
into table i_tab
FOR ALL ENTRIES IN RESULT_PACKAGE1
where
/bic/ZREB_SDAT = RESULT_PACKAGE1-/BIC/ZREB_SDAT
AND
AGREEMENT = RESULT_PACKAGE1-AGREEMENT
AND /BIC/ZLITEM1 = RESULT_PACKAGE1-/BIC/ZLITEM1.

and one more thing your getting 10 million records so use package size in you select query.

Refer the following link also

Regards,

Dhina..

Edited by: Dhina DMD on Sep 15, 2011 7:17 AM

[vimal]

Hi Dheeraj,

1) Delete the CORRESPONDING FIELDS statement , it makes it slower a lot if there are such number of records.

2) Please ensure that RESULT_PACKAGE is NOT INITIAL. And if there is any duplicate entries in RESULT_PACKAGE ,then FOR ALL ENTRIES will also pick the records multiple times.

3) Use the primary keys in WHERE condition as much as you can . And select all primary keys in SELECT statement , even if you are not using them further .

4)Try to make the layout of fields in SELECT statement according to the layout of fields in table. e.g if field BIC is at first position in table, make it first in SELECT statement.

Cheers,

Vimal

[Former Member]

Hi,

Also you could consider about Parallel processing when dealing with large amount of data, if your application server has enough work processs.

Packet those 3-4k records into small no. of packets, and submit them to the server based on available work process and wait untill next work process available, submit remaining packets.

then process all data when all of the jobs complited.

Regards,

Ravi.

Edited by: rshankar on Sep 15, 2011 11:51 AM