topic Re: Strange performance for BSEG in Application Development and Automation Discussions

Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 13:34:22 GMT

Hi guys

I have a simple query on BSEG:

select belnr buzei bschl umskz from bseg

into (bseg-belnr, bseg-buzei, bseg-bschl, bseg-umskz)

where bukrs = p_bukrs

and belnr in s_belnr.

move-corresponding bseg to appo_bseg.

append appo_bseg.

endselect.

If the select-option S_BELNR is filled with:

from <number> to <number>, the select is very fast.

If the select-option S_BELNR is filled with only some single values (number), the select becomes very slow.

Does any of you know the why?

Thanks

Max

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 13:41:21 GMT

probpably the optimizer is doing optimisation in bad way.

When getting a list of values in S_BELNR, optimizer is doing or statment.

...where S_belnr1 or s_belnr2. MAybe some in that step is going not well.

TO work fast it should be interpreted as

select....where ( bukrs = xy and belnr = zz ) or ( bukrs = xy and belnr = zz2 ) and so on to use primary key of that table . If it isnt done that way by optimiser , it is doing sequential read of table.

Have you tried STO5N trace and Explanation of that query ??

What results about optimiser strategy it gives back ??

BR< JAcek

Message was edited by: Jacek Slowikowski

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 13:52:53 GMT

good

sometime is use to happend with the select statement, sometime though we use to give the right data in the selection screen the select statement takes more time to get the data from the database.

You try with correspoing field statement and check the changes.

thanks

mrutyun^

Re: Strange performance for BSEG

andreas_mann3 — Thu, 31 Aug 2006 13:57:44 GMT

hi max,

i don't understand , why do'nt you fill gjahr.

is your selection over several fiscal years?

-> without gjahr you can get duplicate doc-no's

Message was edited by: Andreas Mann

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 14:17:39 GMT

Hi Andreas

You're right I'm not using fiscal year, but I've to update an old program reading BSEG table.

The old select I found didn't use the company code, it was:

select belnr buzei bschl umskz from bseg

into (bseg-belnr, bseg-buzei, bseg-bschl, bseg-umskz)

where belnr in s_belnr.

move-corresponding bseg to appo_bseg.

append appo_bseg.

endselect.

In a second step the program checks company code and fiscal year.

So I added the company code in WHERE condition to improve the performance and after I noticed the strange behavior I wrote above.

Now I only would like to understand that behavior and the fiscal year is not interesting for this question.

Hi Jacek

I believe it's a problem on optimisation of SQL query too, but I've not ST05N in

my release (4.6C) and trx ST05 doesn't work very well.

Max

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 14:21:50 GMT

I agree that it's a database issue - so your best bet is just to take it to your DBAs.

I ran your code with no real differences that I could see.

Rob

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 14:26:09 GMT

Hi MAx !!

I will check st05 on our old 4.6 C. (currently i am workin on 4.7 but our 4.6 C is still running :).

BR< JAcek

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 14:32:54 GMT

Ok you have right. ST05 isnt working fine with cluster tables on 46 C.

But i have proposal for you. Check what DB you have and try tu use HINTS in your abap query to force db to use rigt optimisation method. You have to check what are supported hints for your db.

in abap it looks like

select.....from....

where....

%_HINTS ADABAS 'INDEX("SPFLI" "SPFLI~001")'.

it is put after where statement. (in 4.7 - MaxDB)

Very often it is a good solution to force the optimiser to work fine

BR< JAcek

Message was edited by: Jacek Slowikowski

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 14:44:23 GMT

I take it back - both selects ran pretty quickly and that fooled me, but when I looked at the EXPLAIN, the indivdual values took about twice as long to run as the between.

Rob

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 14:49:05 GMT

Rob - of course it should be slower in case of using or. But

Max write "very slow" for only a few sinlge records... For me it still is strange behaviour.

I've checked standard bseg read (SE16N) and select options with some values. It works very fast and my optimiser works fine: results of explanation :

SELECT

"MANDT" , "BUKRS" , "BELNR" , "GJAHR" , "PAGENO" , "TIMESTMP" , "PAGELG" , "VARDATA"

FROM

"RFBLG"

WHERE

"MANDT" = ? AND "BUKRS" = ? AND ( "BELNR" = ? OR "BELNR" = ? OR "BELNR" = ? OR "BELNR" = ? OR

"BELNR" = ? )

ORDER BY

"MANDT" , "BUKRS" , "BELNR" , "GJAHR" , "PAGENO"

Execution Plan

OWNER TABLENAME COLUMN OR INDEX STRATEGY PAGECOUNT

SAPEHD RFBLG DIFFERENT STRATEGIES FOR OR-TERMS 1592

RANGE CONDITION FOR KEY

MANDT (USED KEY COLUMN)

BUKRS (USED KEY COLUMN)

BELNR (USED KEY COLUMN)

RANGE CONDITION FOR KEY

MANDT (USED KEY COLUMN)

BUKRS (USED KEY COLUMN)

BELNR (USED KEY COLUMN)

RANGE CONDITION FOR KEY

MANDT (USED KEY COLUMN)

BUKRS (USED KEY COLUMN)

BELNR (USED KEY COLUMN)

RANGE CONDITION FOR KEY

MANDT (USED KEY COLUMN)

BUKRS (USED KEY COLUMN)

BELNR (USED KEY COLUMN)

RANGE CONDITION FOR KEY

MANDT (USED KEY COLUMN)

BUKRS (USED KEY COLUMN)

BELNR (USED KEY COLUMN)

RESULT IS COPIED , COSTVALUE IS 25

So it works fine at all.

Message was edited by: Jacek Slowikowski

Re: Strange performance for BSEG

sridhar_k1 — Thu, 31 Aug 2006 15:37:12 GMT

Is your DB version > oracle 8i?

When you use range for BELNR, native SQL uses operator BETWEEN in the where condition and uses FILTER operation to select records, and when single values are used, Operator IN is used in the where condition and operation INLIST ITERATIOR is used.

INLIST ITERATOR iterates over each value afte IN in the where condition.

Before 8i there was no INLIST ITERATOR and IN used to take more time.

I think same number of single values takes more time than using range even with INLIST ITERATOr being used.

Regards

Sridhar

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 15:40:34 GMT

"Very slow" means you can go to wash your car while the select is running and I'm not sure it'll be finised as soon as you come back.

Thank Jacek, but HINTS seems not to find out any positive effect.

Max

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 15:44:35 GMT

Hi Sridhar

My DB is ORACLE version 9.2.0.5.0

Max

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 15:49:43 GMT

Well, as I said earlier - it's a DBA issue. When was the last time they did an index or database re-org?

Rob

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 15:51:05 GMT

:/. I don't have any more ideas. I have MaxDB not Oracle sa i cant help any more with that topic.

But if you will find a solution or a bug please describe it here. I'm very interested in the reason of it.

Wishing luck, Jacek

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 15:54:34 GMT

As Rob cites... you need to start by running "stats" (a table re-org) on the table to guarantee that the table and indexes are built to maximize performance.

In this way, the Optimizer has the best chance of using the best index for record extraction.

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 16:04:44 GMT

Hi Rob

I don't know, but I can ask it.

I'm working as freelance for a big company where several consulting companies are working too.

One of these consulting companies manages SAP system administration and, if the problem is not very important, it usually takes a long time to give an answer.

So I tried to find a solution by myself and with your help.

Max

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 16:16:09 GMT

If performance is poor on BSEG, it's probably not a small problem. Fixing it may speed up other programs.

Rob

Re: Strange performance for BSEG

Former Member — Thu, 31 Aug 2006 16:22:34 GMT

Max,

Hopefully, the client is running "stats" on highly, dynamic tables (like VBAK, VBAP, EKKO, EKPO, BESG, BKPF, etc) on a regularly, scheduled basis.

If not, that is a suggestion that could "earn you some big points" with the customer after they do it, and their system performance improves noticably.

Re: Strange performance for BSEG

sridhar_k1 — Thu, 31 Aug 2006 16:22:48 GMT

Just deleted stats of table RFBLG in my DEV system, it took 36 times more time to select data from BSEG using sinlge vales than ranges. Stats might be very old or corrupted in ur system, rebulding them might improve performance.

Regards

Sridhar