Strange performance for BSEG

[Former Member]

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

[Former Member]

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

[Former Member]

hi

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^

[andreas_mann3]

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

A.

Message was edited by: Andreas Mann

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[sridhar_k1]

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

[Former Member]

hi

"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

[Former Member]

Hi Sridhar

My DB is ORACLE version 9.2.0.5.0

Max

[Former Member]

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

Rob

[Former Member]

:/. 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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

Rob

[Former Member]

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.

[sridhar_k1]

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

[Former Member]

Hi Rob

I think so too, but I can't decide what's important here.

Anyway I'll try to speak about this question to my boss and so he'll ask an help to administrators....sometimes it's very hard to work!

Max

[Former Member]

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

[Former Member]

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

Message was edited by: Jacek Slowikowski

[Former Member]

Hi Max. I recomend that you use INTO TABLE instead of SELECT ENDSELECT.

Because BSEG is a Pool Table of tables BSID, BSIS, BSAD, BSAS, etc.; if possible, use the corresponding table instead, for example, if you only need clients document then use BSID table

Hope it Helps

Jesus

[Former Member]

Hi Jesus

thanks, but I know it, here the company code and document number are used so a select of BSEG should be ok.

Hi Jhon and Sridhar

What does "stats" mean?

Max

[Former Member]

"Stats" are the statistics about the database table.

It includes info about the table size, record length, etc.

As well as similar info about each index of the DB table.

The DB Optimizer "looks to" this information to determine what native SQL to generate to meet the requirements of your Open SQL.

It does this with the intention of trying to get the data as fast as possible. IF the "stats" are old/stale, the Optimizer sometimes will pick a less efficient index OR even resort to sequential reads.

Keeping the "stats" up-to-date on high, usage-volume tables is essential to the entire system's performance.

[Former Member]

Max,

Does that help to explain it?

Don't forget the points, too... I'll never catch you without them.

[Former Member]

Hi

Before deciding how many points have to be assigned I'll have to speak to my administrator about it.

Anyway thanks very much John

Max