11-14-2007 3:58 AM
Hello to everyone,
I have to improve the performance of this select, because it takes several minutes.
Could someone help me ?
-
select a~bukrs "Company code
a~belnr "Accounting Document Number
a~gjahr "Fiscal Year
a~blart "Document type
a~monat "Fiscal Period
a~bldat "Document Date
a~budat "Posting Date
a~xblnr "Reference Document Number
a~bktxt "Document Header Text
a~stblg "Reverse Document Number
*
b~hkont "General Ledger Account
b~aufnr "Order
b~projk "WBS Element
b~augbl "Document Number of the Clearing Document
b~geber "Fund
b~fkber "Functional Area
b~fipos "Commitment Item
b~gsber "Business Area
b~wrbtr "Amount in document curr
b~kostl "Cost Center
b~prctr "Profit Center
b~fistl "Funds Center
b~shkzg "Debit/Credit Indicator
a~cpudt "Entry Date
*
into corresponding fields of table itab
from bkpf as a
inner join bsis as b on bbukrs = abukrs "Company Code/Comp Bank
and bbelnr = abelnr "Accounting Document Number
and bgjahr = agjahr "Fiscal Year
and bmonat = amonat "Fiscal Period
where a~monat le g_mth "Fiscal Period for current/ytd period
and a~gjahr eq g_year "Fiscal Year
and a~bukrs eq p_bukrs "Company Code
and b~blart in r_blart1 "Document Type
and b~gsber eq p_gsber
and b~projk in so_projk
and b~fistl in so_kostl
and b~hkont in so_hkont
and b~fkber in so_fkber
and ( b~bstat ne 'S'
and b~bstat ne 'V' )
and b~geber in so_geber.
11-14-2007 4:08 AM
Hi,
remove the inner join. First get all the data from BKPF and then query BSIS using FOR ALL ENTRIES IN
regards,
Atish
11-14-2007 5:19 AM
Hi
USE FOR ALL ENTRIES ALL WAYS , IF USE JOINS THE DATABASE CONNECTION WILL REMAIN EXIST FOR THAT TABLES UP TO THE PROGRAM EXECUTION SO IT IMPACTS ON DTABASE LOAD RESULT IN BAD PERFORMANCE
IF USE FOR ALL ENTRIES THEN THE DATA FROM THAT TABLES WILL EXTRACT AT TIME
AND DON'T USE CORESPONDING FIELDS IT WILL ALSO REDUCE THE PERFORMNCE
ALLWYS KEEP THE ORDER OF SELECT QUERY FIELDS IN THE ORDER OF INTERNAL TABLE DECLARATION
REWARD IF USEFUL
11-14-2007 9:34 AM
Hi,
1. Use For all entries instead of Inner joins
2. Avoid NE operations in Where clause.
3. Use select fields in the same order as they appear in database table, do the same in where clause also.
4. Use secondary indexes in improve performance, When considering secondary indexes use them at the first place in where clause order.
<b>Reward points if it helps,</b>
Satish
11-14-2007 10:07 AM
Run the SQL trace for your select and read the results:
SQL trace:
/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy
Never try to do performance improvements without running a trace, you just come from unclear state to another.
There is no general recommendation to change JOINs into FOR ALL ENTRIES.
Many people write this here in the forum, but it is simply not true!
Read my blog, then you will see that I know what I am talking about.
Siegfried
11-14-2007 2:29 PM
I've changed your WHERE somewhat:
WHERE a~monat LE g_mth "Fiscal Period for current/ytd period
AND a~gjahr EQ g_year "Fiscal Year
AND a~bukrs EQ p_bukrs "Company Code
AND a~blart IN r_blart1 "Document Type
AND a~bstat IN (' ', 'A', 'B', 'D', 'M', 'W', 'Z')
AND b~gsber EQ p_gsber
AND b~projk IN so_projk
AND b~fistl IN so_kostl "<============ ??????
AND b~hkont IN so_hkont
AND b~fkber IN so_fkber
AND b~geber IN so_geber.
There is an index for BKPF on document type (blart) that is not available in BSIS. But the BSTAT "<>" would prevent it from being used so I changed it to "=" (using the remaining values) and also used BKPF here instead.
It also looks like you may be confusing fund centers (fistl) with cost centers (kostl). You should check your logic there.
And yes, leave the JOIN as is for now. JOINS are generally faster then FOR ALL ENTRIES.
Rob
Message was edited by:
Rob Burbank
11-14-2007 2:46 PM
Rob, you are in principle right, but this statement belongs to a selection screen
and a disussion of the improvements only makes sense, if ones knows which
conditions are actually filled:
where a~monat le g_mth "Fiscal Period for current/ytd period
and a~gjahr eq g_year "Fiscal Year
and a~bukrs eq p_bukrs "Company Code
and b~blart in r_blart1 "Document Type
and b~gsber eq p_gsber
and b~projk in so_projk
and b~fistl in so_kostl
and b~hkont in so_hkont
and b~fkber in so_fkber
and ( b~bstat ne 'S'
and b~bstat ne 'V' )
and b~geber in so_geber.
Not all conditions are always filled.
AND as everybody can see, some of the conditions are with a
and a~gjahr eq g_year "Fiscal Year
and a~bukrs eq p_bukrs "Company Code
and b~blart in r_blart1 "Document Type
and b~gsber eq p_gsber
and b~projk in so_projk
and b~fistl in so_kostl
and b~hkont in so_hkont
and b~fkber in so_fkber
and ( b~bstat ne 'S'
and b~bstat ne 'V' )
and b~geber in so_geber.
so sometimes it is better to start with bkpf = a and sometimes
with bsis = b. This is done by the join. This can not be rewritten as ONE
as FOR ALL ENTRIES (that is for all people with standard answers)!
Not all combinations of select options will be optimal! You must try to
optimze the important ones.
The SQL Trace will tell you which fields are actually and what is done on
the database. Fill different fields and you will get different behavior!
Siegfried
11-14-2007 3:08 PM
Of course Siegfried - I am trying to improve the SELECT as it was written. You'll notice that I didn't suggest checking if parameters and select options are completed or not, because at this stage I wouldn't worry about that. It's a separate question.
But I would start with the SELECT the way I have re-written it and then do more analysis if there are still problems. I wouldn't start trying to analyze something that you know already has problems.
Rob
11-15-2007 1:29 AM
ooo....thanks my friend... i'll try the solutions and let you all know the result.
many thanks.
11-15-2007 3:52 AM
Use the ST05 EXPLAIN function, not run times in your analysis. The run times will be affected by buffering.
Rob
11-15-2007 12:30 PM
Rob,
you are right, if the bstat is used then your change will help.
Why would you not recommend to use runtimes of the SQL Trace, they are perfectly o.k. However, one must exploit the database caching, i.e. measure only after several initialization runs. Then the numbers can be used for comparison.
Siegfried
11-15-2007 2:25 PM
Siegfried - I'm not saying don't use the trace. It's a very useful tool. I'm saying that it makes much more sense to fix the obvious problems first and then run the trace.
Repeat as necesaary.
Rob