Performance problem in Report

[Former Member]

hey guys,

I have performance problem in my Tax report making calcuation with

Various tables specially BSIS and BSAS.

Since i want to generate report with respect to each Company code and Business area.

I made outer loop for Company code and made select statement consecutively like this below.

[KK = Duration. if i input 10 as KK in screen 10 times this loop executes]

loop at itab_t001.(in this internal table i have company code list)

perform taxcode.

perfrorm taxdsec..

..

..

etc(in the middle)

do KK times

select x from BSIS where bukrs = itab_t001-bukrs (not sure of my index key)....

select y from BSAS where bukrs = itab-t001-bukrs(not sure of my index key)

select z from BSIS where bukrs =itab-t00‚P-bukrs (not sure of my index key)....

select w from BSAS where bukrs = itab-t001-bukrs(not sure of my index key)

kk=kk+1

enddo.

endloop.

It takes nearly 3hrs to execute this report completely. because i have table bsis and bsas

with records in thousands.

I dont know how to reduce the time using this query.

I tried following things.

1-I found that above loop executes no of times with respect to input KK.

(if KK= 01(1 month), it displays record in 15min,if i give 10months it takes 10*15min time)

2-i checked SE80 and SQL trace and found the above portion is bad.

could you pls confirm one more thing.

1-my friend says@instead of loop use comparison as IN while comparing Burks field

ie like this(s_pukrs is slection parameter)

select x.y.z from bsis where bukrs IN s_pukrs

does the IN statement is faster than using loop statement?

2-if i send you the code of that select part could you pls tell me

whether the index is properly set or not?

if so pls tell me which way i can send you the code.

since i am working in 40B system. i dont have code inspector tool to analyse also since

we found the problem is within the above case..we are in need of proceeding further.

could somebody take up this posting and help us.

Thanks in advance for your help.

ambichan.

[andreas_mann3]

Hi ambi,

it is very <b>IMPORTANT</b> that you

select the field <b>HKONT</b> !!!

1) so try to collect the accounts, which are should be

analysed.

2) if you can't restrict the accounts -> make sel-options s_hkont

and

a)preselect the accounts from ska1 or skb1

into acc_tab.

b)

loop at acc_tab.
select x from BSIS where bukrs = itab_t001-bukrs 
                    and  hkont = acc_tab-hkont
...

regards <a href="https://www.sdn.sap.com:443/irj/servlet/prt/porta

l/prtroot/com.sap.sdn.businesscard.SDNBusinessCard?u=i

Wo3ssHlIihvCrADIEGqaw%3D%3D">Andreas</a>

[Former Member]

Hello Ambi Chan,

Before I can figure out where is the problem, I need to know what you have on selection screen, what information you want to report. Just give us basic idea of the purpose of this report. It is very difficult to tell where the performance is taking a hit.

Just looking at the logic you pasted, I have a couple of questions.

When you say 'DO n TIMES', you don't have to increment n within the loop.

Next, why do you have two select statements for each of the BSIS and BSAS tables? Can you not do one select statement?

'not sure of my index key'--> in SE12, there are no default indexes created on these tables. So apart from BUKRS, are there any other key field values that you can pass to these tables?

Your friend's suggestion about creating a range or select option for the company codes is good. See if you can include fiscal year into the selection.

Srinivas

[Former Member]

Perf Rule #1: Believe ST05 & SE30.

Perf Rule #2: Avoid nested loop.

a.) Remove your outer loop and make it as a selection table or a range table. If you have more than 200 entries in the selection table, then do not use the selection table instead build an internal table to be used for 'FOR ALL ENTRIES'.

b.) Avoid 'DO..ENDO'. Instead select all records in a year and filter it later or use the field MONAT to filter your records in the selection.

Perf Rule #3: Use Joins or Views.

a.)Do not use multiple slect statement of the same table. Try to merge the selection into one.

b.)Use INNER JOIN to join BSIS & BSAS.

Perf Rule #4: Always use index (primary or secondary).

a.)It is not acceptable to just use some fields in the WHERE clause. Make sure you have index fields used.

BSIS & BSAS have the following fields as index:

BUKRS

BELNR

GJAHR

b.)Use all key fields in the selection if you are using FOR ALL ENTRIES.

Perf Rule #5: Be aware of the number of entries in a table.

a.) You can check it or get help from BASIS to count the numbers.

b.) Before using FOR ALL ENTRIES, be sure of the table used in the FOR ALL ENTRIES statement. Avoid duplicates by copying the table into other name and delete the duplicates before it is used for FOR ALL ENTRIES.

5.) Perf Rule #4: Believe ST05 & SE30.

[Former Member]

Hi Ambichan!

I agree with your friend: Try to build a select-options for the company codes.

Second thing: Try to select the data into internal tables like this example:

data: wa_bsis like bsis, it_bsis like table of wa_bsis.

(same of bsas)

select-options: so_bukrs for wa_bsis-bukrs.

select x z into corresponding fields of table it_bsis

from bsis

where bukrs in so_bukrs... and...

select y w into corresponding fields of talbe it_bsas

from bsas

where bukrs in so_bukrs... and...

After selecting the data into your internal tables, you can do all the statements in a loop over the internal table:

sort it_bsis by bukrs.

loop at it_bsis into wa_bsis.

... all statements you would perform between select ... endselect

endloop.

This should reduce to number of database-accesses and the time needed for the query.

Hope this helps, regards, Kathrin!