when select a big volumn table...

[rt50896]

Dear all,

I have a question about performance when select big volumn table in different situation:

1. when server performance good(not working day),the same selection clause process is perfect..

2. when server performance bad(especially period end closing),it's bad to select.

the code1 is the original,in situation 1 is ok,but in situation 2 is bad:

LOOP AT ITAB. --> data exist
  SELECT * INTO TABLE ATAB FROM table WHERE XX = ITAB-XX
                                        AND YY = ITAB-YY.
  LOOP AT ATAB.
    process...
  ENDLOOP.
ENDLOOP.

the code2 is tune version,but in situation 2 execute,its performance is bad:

SELECT * INTO TABLE ATAB FOR ALL ENTRIES IN ITAB
                           WHERE XX = ITAB-XX
                             AND YY = ITAB-YY.
LOOP AT ITAB.
  LOOP AT ATAB WHERE XX = ITAB-XX
                 AND YY = ITAB-YY.
    process...
  ENDLOOP.
ENDLOOP.

Can you give me some ideas how to tune this kind of programs?

ps. I also create index for table select clause,but system seems not use it...why?

BR

Regina

[rt50896]

Dear all,

I have a question about performance when select big volumn table in different situation:

1. when server performance good(not working day),the same selection clause process is perfect..

2. when server performance bad(especially period end closing),it's bad to select.

the code1 is the original,in situation 1 is ok,but in situation 2 is bad:

LOOP AT ITAB. --> data exist
  SELECT * INTO TABLE ATAB FROM table WHERE XX = ITAB-XX
                                        AND YY = ITAB-YY.
  LOOP AT ATAB.
    process...
  ENDLOOP.
ENDLOOP.

the code2 is tune version,but in situation 2 execute,its performance is bad:

SELECT * INTO TABLE ATAB FOR ALL ENTRIES IN ITAB
                           WHERE XX = ITAB-XX
                             AND YY = ITAB-YY.
LOOP AT ITAB.
  LOOP AT ATAB WHERE XX = ITAB-XX
                 AND YY = ITAB-YY.
    process...
  ENDLOOP.
ENDLOOP.

Can you give me some ideas how to tune this kind of programs?

ps. I also create index for table select clause,but system seems not use it...why?

BR

Regina

[Former Member]

Regina,

The index will have to be created as a index with all the fields in the where clause, only then it will be used by the system.

A work around solution could be force the user to schedule the program in the background rather than in the foreground.

Also, if its a report program forcing the user to give more selection criteria could be a option.

Regards,

Ravi

Note : Please mark the helpful answers

[Former Member]

Hi

Another problem could be on loop if the internal tables have many hits.

You can try to improve the performace using SORTED TABLE instead of STANDARD TABLE:

DATA: ATAB LIKE SORTED TABLE OF <TABLE> WITH

UNIQUE/NON-UNIQUE KEY XX YY WITH HEADER LINE.

I use XX and YY as key for internal table because they belong to WHERE condition in the loop.

SELECT * INTO TABLE ATAB FOR ALL ENTRIES IN ITAB

WHERE XX = ITAB-XX

AND YY = ITAB-YY.

LOOP AT ITAB.

LOOP AT ATAB WHERE XX = ITAB-XX

AND YY = ITAB-YY.

process...

ENDLOOP.

ENDLOOP.

Max

[dani_mn]

When the quantity of data becomes larger than the optimization factors gets changes.

We have a report in which select was used in loop endloop.

We have given a task to a developer to improve the performance he uses for all entries and joins.

In developement and quality server new report was showing improvement but in production where amount of data was huge and dynamic. This optimize report was taking more time than the old one.

So its depend on the situation you are in.

Regards,

Wasim Ahmed

[Former Member]

Hi in ur optimized code,

--> sort the internal table ATAB.

instead of using another loop inside the loop u can use <b>read table with key</b>

--> Read the table using Binary Search.

SELECT * INTO TABLE ATAB FOR ALL ENTRIES IN ITAB

WHERE XX = ITAB-XX

AND YY = ITAB-YY.

LOOP AT ITAB.

• LOOP AT ATAB WHERE XX = ITAB-XX

• AND YY = ITAB-YY.

<b>Read TABLE ATAB with key XX = ITAB-XX

YY = ITAB-YY binary search.</b>

process...

ENDLOOP.

ENDLOOP.

[Former Member]

Hi Regina,

even in the situation1, where server is free with which you are getting response bit faster,but this may be for only few more months/days, as days progresses data will be added to the database & takes same time(as in situation 2,as it has to handle lot of data).

here are the few tips of optimization:

1) Dont use * in any selects,instead give what fields you want from the database.

2) dont use SELECT within LOOPS.most of the cases,we can avoid this situation by FOR ALL ENTRIES. so use it.

3) in the fetching of ITAB data, have you given any where clause to restrict the values? if not give the fields ther also. if you increase no of records in ITAB, obviously that will effect ATAB records also. so try to get only the RECORDS required,by giving WHERE CLAUSE OF that select which will fillup ITAB Table.

3) in the second loop of ATAB, you mentioned PROCESS. pl let me know what are doing in that?

It seems to be your code2 is acceptable(except * in select).

Really we find some scenarios where lots of data comes to the program & needs to handle it. In such cases instead of executing the program in foreground/directly, give it to the background system(background job) & give execution time will be at night sothat server becomes bit free.

through these ways we can make our programs works faster.

check this & let us all know about your resolution.

regards

Srikanth.

[Former Member]

Hi Regina,

Code 2 optimisation ways can be :

1> Do not use SELECT * statement, instead use specific fields which are relevant to your requireement.

2>In where clause of select statement, fields compared should be in the same sequence as given in the indexing else indexing wont be of any help.

e.g. If indexing is done on the field XX and YY then

in select statement should be

select field1 field2 into table ATAB from (table_name)

where XX = itab-XX

and YY = itab-YY.

Sequence in where clause is important.

3>Try avoiding Loop within Loop, instead use READ statement. eg.

Below code :

LOOP AT ITAB.

LOOP AT ATAB WHERE XX = ITAB-XX

AND YY = ITAB-YY.

process...

ENDLOOP.

ENDLOOP.

should be written as :

SORT ATAB.

LOOP AT ITAB.

READ TABLE ATAB WHERE XX = ITAB-XX

YY = ITAB-YY BINARY SEARCH.

<process>

ENDLOOP.

4> Run volume intensive programs in background rather than online and use selection criteria to restrict the volumes.

Cheers,

Vikram

Please reward for helpful replies!!

[former_member186741]

it depends on where you are getting the problems. Is it on the sql or in the multiple passes of the loop? You can try and find this out by using SE30 if your test environment is set up like prod but it may not work if you have too much data.

So it's difficult to say but I would code it like this:

• declare ATAB as a sorted table

data ATAB type sorted table of ?? with non-unique key of xx yy with header line.

*declare work copy of ITAB

data itab2 type sorted table of ?? with non-unique key of xx yy with header line.

if not itab[] is initial.

itab2[] = itab[].

delete adjacent duplicates from itab2 comparing xx yy.

SELECT * INTO TABLE ATAB FOR ALL ENTRIES IN ITAB2

WHERE XX = ITAB2-XX

AND YY = ITAB2-YY.

LOOP AT ITAB2.

LOOP AT ATAB WHERE XX = ITAB2-XX

AND YY = ITAB2-YY.

process...

ENDLOOP.

ENDLOOP.

endif.

[rt50896]

Hi all,

Thanks for your suggestion,I think my description is not enough to let you understand what the situation 1 or 2,but from records retrieve status: from internal table ITAB is about 2,000_{9,000 records,and through ITAB to get ATAB is about 9,000}900,000 records, this ranges are very large...Select for ATAB is ok,but loop ATAB to compare with ITAB processing is also a performance issue(code2)..

although code1 in loop ITAB to retrieve to ATAB then process,this way may get less data processing by each XX,YY criteria...but sometimes this looks bad way...why?

especially this program is usually by many users retrieve when server performance low,I face a difficult choice...How can i do to solve it ?

if use sort table,how to change the statement as my require:

SORT ATAB BY XX YY ASCENDING ZZ DESCENDING

ps. index create as select clause,but system still not use it as "optimization"...

Regina

[Former Member]

1. SORTED internal table can help you with your performance issue.

2. As the no. of records is really large, you can look at using the HASHED internal tables.

3. In cases where internal tables have a problem with the performance, FIELD GROUPS can also be used.

If you want to use SORTED internal table, then declare ATAB as a SORTED table with unique / non-unique columns that you are going to use in your WHERE clause.

DATA : ATAB TYPE SORTED TABLE OF XXXX WITH NON-UNIQUE KEU COLUMN1 COLUMN2.

Now, these columns will be indexed and will be used when you loop at the table with a where clause on this table

Regards,

Ravi

[former_member186741]

If you have to have your table sorted with the mixture of ascending and descending you could use this approach.

SELECT * INTO TABLE ATAB FOR ALL ENTRIES IN ITAB

WHERE XX = ITAB-XX

AND YY = ITAB-YY.

LOOP AT ITAB.

read table atab with

key XX = ITAB-XX

AND YY = ITAB-YY binary search.

if sy-subrc = 0.

*start loop from the first matching entry

LOOP AT ATAB from sy-tabix.

*leave the loop as soon as all matching entries are processed

if atab-XX <> ITAB-XX

or atab-YY <> ITAB-YY.

exit.

endif.

*process...

ENDLOOP.

endif.

ENDLOOP.

[Former Member]

Hi Regina,

The performance of queries written on DB are always data specific.

If u have many entries in the table, the time taken will always be high compared to table with less entries.

In your case, in first code, u have written select inside a loop.

That should be avoided.

In case of FOR ALL ENTRIES, use a check before it to chck whether the contents of internal table are not initial.

Or else the entire contents of the table would be copied into the internal table n then u have looped on this big volume table...

Use

if not itab is initial,

select query...

endif.

if itab the bigger table or atab.

Use the bigger table in outer loop n smaller table in the inner loop.

use Read with binary search inside the loop instead of looping inside a loop.

Hope that helps.

Regards,

Tanveer.

<b>Please mark helpful answers</b>

[Former Member]

Hi,

A few tips you can use for finetuning a Report

1) Use mostly primary key to access data.

2) before READ u sort the itab

3) use mostly the indexes fields in ur where clause.

4) restrict the the fields retrieved by your select sentences to the minimal set. (avoid select *)

5) try to use specify where clause so the abap sql optimizer chooses the right index.

6) avoid sentences like select lifnr name1 into corresponding fields of lfa1 from lfa1 where ....

(you should declare a working area and select into the working area, is twice faster)

7) use hashed tables instead of standard tables. They are faster.

😎 Avoid the use of collect as much as you can.

1 Always check the driver internal tables is not empty , while using FOR ALL ENTRIES

2 Avoid for all entries in JOINS

3 Try to avoid joins and use FOR ALL ENTRIES.

4 Try to restrict the joins to 1 level only ie only for 2 tables

5 Avoid using Select *.

6 Avoid having multiple Selects from the same table in the same object.

7 Try to minimize the number of variables to save memory.

8 The sequence of fields in 'where clause' must be as per primary/secondary index ( if any)

9 Avoid creation of index as far as possible

10 Avoid operators like <>, > , < & like % in where clause conditions

11 Avoid select/select single statements in loops.

12 Try to use 'binary search' in READ internal table. Ensure table is sorted before using BINARY SEARCH.

13 Avoid using aggregate functions (SUM, MAX etc) in selects ( GROUP BY , HAVING,)

14 Avoid using ORDER BY in selects

15 Avoid Nested Selects

16 Avoid Nested Loops of Internal Tables

17 Try to use FIELD SYMBOLS.

18 Try to avoid into Corresponding Fields of

19 Avoid using Select Distinct , Use DELETE ADJACENT.

***********************

Regards,

Irfan Hussain

Note: Please reward helpful answer's.

[Former Member]

u have to check alternative Solutions Like .

1. is There any FM to give Required data.

2. Is there any Views of LDBs.

3. To Enhance the Perfomance of ur Code

LOOP AT ITAB. --> data exist  SELECT * INTO TABLE ATAB FROM table WHERE XX = ITAB-XX                                        AND YY = ITAB-YY.  LOOP AT ATAB.    process...  ENDLOOP.ENDLOOP.
<b>dont use select stmt with in the Loop, to avoid this one maintain one more table, 
like .
ranges: R_XX for atab-XX,
        R_YY for atab-YY.
loop at itab.
R_XX-sign = 'I'.
R_XX-option = 'EQ'.
R_XX-low = itab-XX.
append R_XX.
clear  r_xx.
endloop.
sort R_XX by low.
delete adjacent duplicates from R_XX comparing low.
sort R_XX by low.
if r_xx[] is not initial.
select * from atab into corresponding fields of i_atab
where XX in R_XX.
endif.
after that Put ur Logic .
</b>

it will reduce 30% of DB Cost.
4.Let Us know how u Created Indexs and on what fields.
5.Run St05, check the Open stmt of the table ATAB which index it is using

let me know if ur required more info to improve performance of the Report.

Regards

prabhu

[email protected]

Part of SAP CPO program.

[Former Member]

Hi..

As mentioned though creating INDEX it didnt have any impact on the perfomance.

I would like to comment on this point.

ABAP Cost based optimizer will take an acess stratergy based on path which takes minimum time.

Now we need to identify wheather the select has used your new INDEX created using the fields of the select.Its not mandatory even if you mention all the index fields in your where conditions Optimizer may skip the index and take other indexes.

You can check this using the tool ST05 and see for the select statement written which index it has used and acess stratergy.If its full table scan or Range scan try to make it unique scan by changing the where condition which could satisy the index.Always make sure we take help of Index scan for fetching records which makes fetching always faster independent of the records.

Thanks and Regards

Tharun