topic Re: Performance issues : Selection data in Application Development and Automation Discussions

Performance issues : Selection data

Former Member — Wed, 14 Nov 2007 03:58:37 GMT

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 b_{bukrs = a}bukrs "Company Code/Comp Bank

and b_{belnr = a}belnr "Accounting Document Number

and b_{gjahr = a}gjahr "Fiscal Year

and b_{monat = a}monat "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.

Re: Performance issues : Selection data

Former Member — Wed, 14 Nov 2007 04:08:59 GMT

Hi,

remove the inner join. First get all the data from BKPF and then query BSIS using FOR ALL ENTRIES IN

regards,

Atish

Re: Performance issues : Selection data

Former Member — Wed, 14 Nov 2007 05:19:59 GMT

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

Re: Performance issues : Selection data

Former Member — Wed, 14 Nov 2007 09:34:02 GMT

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

Re: Performance issues : Selection data

Former Member — Wed, 14 Nov 2007 10:07:17 GMT

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

Re: Performance issues : Selection data

Former Member — Wed, 14 Nov 2007 14:29:32 GMT

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

Re: Performance issues : Selection data

Former Member — Wed, 14 Nov 2007 14:46:28 GMT

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

Re: Performance issues : Selection data

Former Member — Wed, 14 Nov 2007 15:08:50 GMT

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

Re: Performance issues : Selection data

Former Member — Thu, 15 Nov 2007 01:29:05 GMT

ooo....thanks my friend... i'll try the solutions and let you all know the result.

many thanks.

Re: Performance issues : Selection data

Former Member — Thu, 15 Nov 2007 03:52:59 GMT

Use the ST05 EXPLAIN function, not run times in your analysis. The run times will be affected by buffering.

Rob

Re: Performance issues : Selection data

Former Member — Thu, 15 Nov 2007 12:30:14 GMT

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

Re: Performance issues : Selection data

Former Member — Thu, 15 Nov 2007 14:25:35 GMT

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