topic Re: Optimisation Advice required for a code in Application Development and Automation Discussions

Optimisation Advice required for a code

Former Member — Thu, 15 Sep 2011 04:35:25 GMT

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.

Re: Optimisation Advice required for a code

Former Member — Thu, 15 Sep 2011 05:01:37 GMT

Hi,

for db access you need an index on table /BIC/PZREB_SDAT for fields

/BIC/ZREB_SDAT

AGREEMENT

/BIC/ZLITEM1

For internal table access table i_tab should be a sorted table for direct key access or a standard table sorted by the above listed fields for BINARY SEARCH access option.

Regards,

Klaus

Re: Optimisation Advice required for a code

Former Member — Thu, 15 Sep 2011 05:11:01 GMT

I already have 1 index on table for the fileds

/BIC/ZREB_SDAT

AGREEMENT

AND /BIC/ZLITEM1

your suggestion woild be to make 3 index for this table separately for each field or 1 which is already existing.

I have created the index with option of Unique index(database required). Is that OK.

Howeveer I_TAB is already Sorted. I will use binary search.

But my majotr concern is getting the data faster from the /BIC/PZREB_SDAT table.

Re: Optimisation Advice required for a code

HermannGahm — Thu, 15 Sep 2011 06:39:04 GMT

Hi,

> Howeveer I_TAB is already Sorted. I will use binary search.

good idea!

> I already have 1 index on table for the fileds

> /BIC/ZREB_SDAT

> AGREEMENT

> AND /BIC/ZLITEM1

> But my majotr concern is getting the data faster from the /BIC/PZREB_SDAT table.

well, if the index is in place (and used) the only thing left is the FAE.

Make sure the driver table (RESULT_PACKAGE) is not empty

Make sure you don't have dupliates in the driver table (RESULT_PACKAGE)

You can fine tune the FAE by inreasing the bloking factor (hint max_bloking_fator).

Depending on the current value (and db platform) this could make some differene.

Kind regards,

Hermann

Re: Optimisation Advice required for a code

Former Member — Thu, 15 Sep 2011 06:50:33 GMT

I am not sure about FAE, Please throw some light on this.

Thanks

Dheeraj

Re: Optimisation Advice required for a code

HermannGahm — Thu, 15 Sep 2011 07:02:47 GMT

Hi,

welll the FOR ALL ENTRIES:

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

/BIC/PZREB_SDAT

into CORRESPONDING FIELDS OF table i_tab

FOR ALL ENTRIES IN RESULT_PACKAGE***************MAKE SURE NOT EMPTY AND NO DUPLICATES

where

/bic/ZREB_SDAT = RESULT_PACKAGE-/BIC/ZREB_SDAT

AND

AGREEMENT = RESULT_PACKAGE-AGREEMENT

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

%_HINTS ORACLE '&max_blocking_factor 50&'. ***************ADAPT to your db platform ...

Kind regards,

Hermann

Re: Optimisation Advice required for a code

volker_borowski2 — Fri, 16 Sep 2011 06:43:31 GMT

Hi,


SELECT /BIC/ZSETLRUN AGREEMENT /BIC/ZREB_SDAT /BIC/ZLITEM1 from
:
where
/bic/ZREB_SDAT = RESULT_PACKAGE-/BIC/ZREB_SDAT
AND AGREEMENT = RESULT_PACKAGE-AGREEMENT
AND /BIC/ZLITEM1 = RESULT_PACKAGE-/BIC/ZLITEM1.
:

So effectively, you are only selecting a single field, as the other 3 are already known...

What DB type is this?

If Oracle (or other DB, that does NOT store the tables as B-Trees) you can try to include

field /BIC/ZSETLRUN as the last field into that index. This might save the GET of the related datablock per row.

On Oracle, if your blevel of the index is 3 AND DOES NOT CHANGE when you add the field to the index,

you'll gain 20% (4 gets instead of 5 per record). If Blevel is 4 the ratio is 6 to 5 it (17%).

If you blevel is higher after you added the field, revert.

Volker

Re: Optimisation Advice required for a code

yuri_ziryukin — Fri, 16 Sep 2011 08:41:28 GMT


Hi,
> > 
> So effectively, you are only selecting a single field, as the other 3 are already known...
> 
> What DB type is this?
> If Oracle (or other DB, that does NOT store the tables as B-Trees) you can try to include 
> field /BIC/ZSETLRUN as the last field into that index. This might save the GET of the related datablock per row.
> On Oracle, if your blevel of the index is 3 AND DOES NOT CHANGE when you add the field to the index, 
> you'll gain 20% (4 gets instead of 5 per record). If Blevel is 4 the ratio is 6 to 5 it  (17%).
> 
> If you blevel is higher after you added the field, revert.
> 
> Volker

Hello Volker,

don't forget please that the problem is not only (and very often not at all) in the buffer gets, but in the disk reads. And adding the field to be selected at the end of the index quite often dramatically reduces the response time due to the avoided disk reads.

Cheers,

Yuri

Re: Optimisation Advice required for a code

volker_borowski2 — Fri, 16 Sep 2011 09:53:20 GMT

how should I forget that

...but a GET is what you alway get, and a READ is what you MIGHT get with luck in this example,

esp. while you are debugging, because it's in the cache due to repeated execution.

But of course you are right.

Re: Optimisation Advice required for a code

HermannGahm — Fri, 16 Sep 2011 10:56:37 GMT

Hi Volker,

thanks for adding that one. Since i didn't pay attention to the fieldlist (i tend to do this only after checking all the other things ;-).

> What DB type is this?

> If Oracle (or other DB, that does NOT store the tables as B-Trees) you can try to include

> field /BIC/ZSETLRUN as the last field into that index.

This is in principle valid for tables stored in B-Trees as well, isn't it? A covering index is on

all plattforms a good thing no matter if we have heap or b-tree tables (I don't have this table

so i don't know wheter /BIC/ZSETLRUN is part of the primary key in that case the field is stored

in all ather indexes anyway).

> If you blevel is higher after you added the field, revert.

Thanks for adding this one! I've seen many people creating covering indexes not paying attention to that fact

Kind regards,

Hermann

Re: Optimisation Advice required for a code

volker_borowski2 — Fri, 16 Sep 2011 13:48:01 GMT

Hi Hermann,


> > If Oracle (or other DB, that does NOT store the tables as B-Trees) you can try to include 
> > field /BIC/ZSETLRUN as the last field into that index. 
> This is in principle valid for tables stored in B-Trees as well, isn't it? A covering index is on
> all plattforms a good thing no matter if we have heap or b-tree tables .

yes, sorry, I was obviously thinking a bit too confused.

Volker