topic Re: Poor performance reading MBEWH table in Application Development and Automation Discussions

Poor performance reading MBEWH table

guilherme_frisoni — Fri, 10 Jul 2009 21:13:50 GMT

Hi,

I'm getting serious performance problems when reading MBEWH table directly.

I did the following tests:


  GET RUN TIME FIELD t1.
  SELECT mara~matnr
    FROM mara
    INNER JOIN mbewh ON mbewh~matnr = mara~matnr
    INTO TABLE gt_mbewh
    WHERE mbewh~lfgja = '2009'.
  GET RUN TIME FIELD t2.

  GET RUN TIME FIELD t3.
  SELECT mbewh~matnr
    FROM mbewh
    INTO TABLE gt_mbewh
    WHERE mbewh~lfgja = '2009'.
  GET RUN TIME FIELD t4.

t2 = t2 - t1.
t4 = t4 - t3.
write: 'With join: ', t2.
write /.
write: 'Without join: ', t4.

And as result I got:


With join:      27.166
Without join:  103970.297

All MATNR in MBEWH are in MARA.

MBEWH has 71.745 records and MARA has 705 records.

I created an index for lfgja field in MBEWH.

Why I have better performance using inner join?

In production client, MBEW has 68 million records, so any selection takes too much time.

Thanks in advance,

Frisoni

Re: Poor performance reading MBEWH table

Former Member — Fri, 10 Jul 2009 21:21:17 GMT

Hi Guilherme,

Check what happen if you do this:

  SELECT mbewh~matnr
    FROM mbewh
    INNER JOIN mbewh ON mara~matnr = mbewh~matnr
    INTO TABLE gt_mbewh
    WHERE mbewh~lfgja = '2009'.

Regards, Fernando Da Ró

Re: Poor performance reading MBEWH table

guilherme_frisoni — Fri, 10 Jul 2009 21:29:20 GMT

Hi Fernando,


  GET RUN TIME FIELD t3.
    SELECT m1~matnr
    FROM mbewh as m1
    INNER JOIN mbewh as m2 ON m2~matnr = m1~matnr
    INTO TABLE gt_mbewh
    WHERE m1~lfgja = '2009'.
  GET RUN TIME FIELD t4.

Result:


Without join:  259346.845

Not so good. 😕

Frisoni

Re: Poor performance reading MBEWH table

Former Member — Fri, 10 Jul 2009 21:33:01 GMT

Even worst...

Perhaps, your database is taking some strange decision when accessing the data.

First, using [ST05|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/7205] [original link is broken] [original link is broken] [original link is broken]; check the execution plan.

My guess is that your database statistics aren't updated (check it on DB20 for each table), and share your impressions with us.

OPS... I'm sorry Guilherme I made a mistake, I "read" that without join was better than join... Anyway, the execution plan gathered on ST05 will solve your doubt.

Regards, Fernando Da Ru00F3s

Edited by: Fernando Ros on Jul 10, 2009 11:35 PM

Re: Poor performance reading MBEWH table

former_member156446 — Fri, 10 Jul 2009 21:37:56 GMT

if you look at the table MATNR BWKEY BWTAR LFGJA LFMON are the primary keys... the more primary keys u can use more performance can be improved... try to work on it...

for more easier test : se30 > tips and tricks button..

write your two codes side by side and hit Measure runtime...

Re: Poor performance reading MBEWH table

Former Member — Sat, 11 Jul 2009 15:54:14 GMT

your measurment comes for a test system, with this totals for the tables??

> MBEWH has 71.745 records and MARA has 705 records.

... if yes, what is the question?

With this setup the bahavior is o.k.

But does it help for your production system? Not at all. How many records are in MARA in the production system?

How records will come back?

Maybe archiving could be a godd starting point.

Otherwise, please add ST05 information to such questions!!!!

Siegfried

Re: Poor performance reading MBEWH table

guilherme_frisoni — Mon, 13 Jul 2009 14:41:19 GMT

Hi,

I just found that besides the current client has just 80000 records, there are two hidden clients in the same server with 50 million rocords.

Checking ST05, when I run SELECT just with MBEWH, any index is used, even after I created an Index in the where field.

But when using a JOIN with MARA, the primary index is used and performance is much better.

Thanks,

Frisoni

Re: Poor performance reading MBEWH table

Former Member — Mon, 13 Jul 2009 15:10:23 GMT

> any index is used,

there is no any index, either an index is used, then it has a name or no index.

You can see the index only in the ST05, with the function DB-explain

A secondary index can be as good as the primary index!

The data of th other clients are irrelvant you will not see and not search them.

Re: Poor performance reading MBEWH table

guilherme_frisoni — Mon, 13 Jul 2009 16:17:11 GMT

Hi,

ST05 execution plans are below. In same order as the code posted before.


SELECT STATEMENT ( Estimated Costs = 51.113 , Estimated #Rows = 555.197 )

       3 NESTED LOOPS
         ( Estim. Costs = 51.113 , Estim. #Rows = 555.197 )
         Estim. CPU-Costs = 1.091.447.843 Estim. IO-Costs = 51.021

           1 INDEX FAST FULL SCAN MARA~0
             ( Estim. Costs = 48 , Estim. #Rows = 7.282 )
             Estim. CPU-Costs = 9.706.161 Estim. IO-Costs = 47
             Filter Predicates
           2 INDEX RANGE SCAN MBEWH~0
             ( Estim. Costs = 7 , Estim. #Rows = 76 )
             Search Columns: 3
             Estim. CPU-Costs = 148.550 Estim. IO-Costs = 7
             Access Predicates Filter Predicates


SELECT STATEMENT ( Estimated Costs = 76.169 , Estimated #Rows = 1.768.067 )

       1 TABLE ACCESS FULL MBEWH
         ( Estim. Costs = 76.169 , Estim. #Rows = 1.768.067 )
         Estim. CPU-Costs = 19.049.481.694 Estim. IO-Costs = 74.562
         Filter Predicates

I created the following index:


NONUNIQUE  Index   MBEWH~Z01

Column Name                     #Distinct

LFGJA                                          5
MANDT                                          6

Why this index is not used, and a full scan is made?

Thanks,

Frisoni

Re: Poor performance reading MBEWH table

Former Member — Mon, 13 Jul 2009 17:38:01 GMT

Add Mandt on ur select statement

SELECT mbewh~matnr

FROM mbewh

INTO TABLE gt_mbewh

WHERE mbewh~lfgja = '2009'

and mandt = sy-mandt.

Re: Poor performance reading MBEWH table

HermannGahm — Tue, 14 Jul 2009 07:00:42 GMT

Hi Frisoni,

your first examle reads all records (keys) of a small table (MARA) in the most efficient way possible (index fast full scan),

and joins these small number of lines to a big table where for each line a range scan with quite selective fields (MATNR) is done.

for your second example:


> Why this index is not used, and a full scan is made?
>

From all the different figures in your post, i don't get how much records your MBEWH table has. However, the optimizer

makes the following assumption:

estimated nr. of records = ( nr of table rows / nr of clients (6) / nr of LFGJA (5) )

so estimated nr. of records = (nr. of table rows / 30 )

A quite unselective index range scan has to be performed. That is probably not as efficient as one full table scan.

Reccomendation for THIS example:

Add the MATNR field to your index and the optimizer will probaly swich from a full table scan to a (fast)full index scan.

But that might still be slower than your join, since your join starts on MARA (few records) and access MBWEH only a few times for the few records.

For your index design you have to use the figures from your productive systems...

nr. of clients in production, nr. of records in MARA and MBWEH how much records fullfil your condition WHERE mbewh~lfgja = '2009' on MBWEH.... with this information one can come up with a recommendation for production...

Kind regards,

Hermann

Re: Poor performance reading MBEWH table

Former Member — Tue, 14 Jul 2009 07:38:15 GMT

I don't understand what you expect from your index!

Go to this explain TABLE ACCESS FULL MBEWH

Doubleclick on the tablename MBEWH

=> what do the statistics show?

How many records are in the table,

Scroll down what indexes do you see, is there your Z01 index, does it have statistics data?

If no, it can not be used.

If yes, what is the number of distinct values of lfgja. It can hardly be larger than 10.

Siegfried

Re: Poor performance reading MBEWH table

guilherme_frisoni — Tue, 14 Jul 2009 14:15:10 GMT

Hi Siegfried,

the index statistics in MBEWH are as following:


NONUNIQUE  Index   MBEWH~Z01

Column Name                     #Distinct

LFGJA                                          5
MANDT                                          6

Last statistics date                  10.07.2009
Analyze Method               Sample 531.679 Rows
Levels of B-Tree                               3
Number of leaf blocks                    148.100
Number of distinct keys                       12
Average leaf blocks per key               12.341
Average data blocks per key              171.933
Clustering factor                      2.063.200

I can't understand why this index is not used, or what I have to to to create an index by LFGJA.

Thanks,

Frisoni

Re: Poor performance reading MBEWH table

HermannGahm — Tue, 14 Jul 2009 14:24:49 GMT

Hi,

the optimizer uses these figures to calculate the cost for your range scan:

> Levels of B-Tree 3

> Number of distinct keys 12

> Average leaf blocks per key 12.341

> Average data blocks per key 171.933

> Clustering factor 2.063.200

the resulting cost is higher than the cost for a full table scan.

therefore it chooses a full table scan.

For cost comparison the table statistics are missing.

you can try to enforce the index usage:

%_hints oracle("MBEWH~Z01" "MBWEH")

but it will probaly be not faster... (depending on your datadistribution).

in case of unequal data distribution a hint (but not the index hint, rather substitute values and histograms)

could make sense.

Kind regards,

Hermann

Re: Poor performance reading MBEWH table

guilherme_frisoni — Tue, 14 Jul 2009 15:10:20 GMT

Thanks Hermann,

I forced to use Z01 index with %_hints oracle 'INDEX(MBEWH "MBEWH~Z01")', and got a much better result for without join statement:


With join:      96.217
Without join:      93.781

The execution plan was that:


SELECT STATEMENT ( Estimated Costs = 184.550 , Estimated #Rows = 1.768.067 )

       2 TABLE ACCESS BY INDEX ROWID MBEWH
         ( Estim. Costs = 184.550 , Estim. #Rows = 1.768.067 )
         Estim. CPU-Costs = 3.217.515.212 Estim. IO-Costs = 184.279

           1 INDEX RANGE SCAN MBEWH~Z01
             ( Estim. Costs = 12.427 , Estim. #Rows = 4.420.167 )
             Search Columns: 2
             Estim. CPU-Costs = 974.045.977 Estim. IO-Costs = 12.345
             Access Predicates

Now i'm going to test this results in production.

Thanks,

Frisoni

Re: Poor performance reading MBEWH table

HermannGahm — Tue, 14 Jul 2009 15:23:03 GMT

Hi Frisoni,

> I forced to use Z01 index with %_hints oracle 'INDEX(MBEWH "MBEWH~Z01")', and got a much better result for without join statement:

> > With join: 96.217

> > Without join: 93.781

that doesnt sound MUCH better... more or less equal...

However BE CAREFULL with forcing index usage if your data distribution is not equal

and your where conditions are changing (different values) you can easily get results

which are MUCH worse than the original full table scan.

Analyze your data distribution and your queries (WHERE conditions) on your production system... THEN think about

how to optimize your accesses...

Kind regards,

Hermann

Re: Poor performance reading MBEWH table

HermannGahm — Tue, 14 Jul 2009 15:49:38 GMT

Hi Frisoni,

as said the estimated cost for the fts is lower:



SELECT STATEMENT ( Estimated Costs = 76.169 , Estimated #Rows = 1.768.067 ) 
       1 TABLE ACCESS FULL MBEWH
       ...

SELECT STATEMENT ( Estimated Costs = 184.550 , Estimated #Rows = 1.768.067 )
 ...
       2 TABLE ACCESS BY INDEX ROWID MBEWH
...
           1 INDEX RANGE SCAN MBEWH~Z01

the reason why it runs faster is probably the uneven data distribution. So, again,

be carefull with forcing index usage when you access the data with different

variables for mandt and lfgja.. .

and btw...

for this query:

SELECT mbewh~matnr

FROM mbewh

INTO TABLE gt_mbewh

WHERE mbewh~lfgja = '2009'.

you could try with an index with an index on MANDT, LFGJA, MATNR.

Then there is NO table access required anymore. Therfore the optimizer may choose

it without a hint since it is a covering index. And the optimizer can go for a

index fast full scan, which is faster than an index range scan (what you have

currently with the index hint).

and

Kind reards,

Hermann

Re: Poor performance reading MBEWH table

Former Member — Tue, 14 Jul 2009 16:16:17 GMT

> With join: 96.217

> Without join: 93.781

before downtime I wanted to write that this is equal, repeat measurement and it might change.

The costs show the opposite as Hermann pointed out.

So if there is a runtime difference, then is depends on the actual distribution of the data in the different years compared to the equal distribution which the optimizer assumes.

One field with 5 different vaules can not really help to get fast access,

only if you search for the value which is very rare much lower than 10%.

If you system has the last 5 years of data, then this years has still less than 1/5 of data, but hopefully after 6 six months already

much more 1/10 = 10% ( > 5% - 10% is the value where the full table scan becomes better)

Siegfried

Re: Poor performance reading MBEWH table

Rui_Dantas — Tue, 14 Jul 2009 16:50:06 GMT

Guilherme, Hermann, Siegfried,

I have just seen this thread and read it from top to bottom, and I would say now is a good time to make a summary..

This is want I got from Guilherme's comments:


1) MBEWH has 71.745 records 
2) There are two hidden clients in the same server with 50 million rocords.
3) Count Distinct mandt = 6
4) In production client, MBEW has 68 million records

First measurement


With join               : 27.166
Without join            :  103970.297

Second measurement


With join               : 96.217
Without join            : 93.781            << now with hint

The original question was to understand why using the JOIN made the query much faster.

So the conclusions are:

1) Execution times are really now much better (comparing only the not using join case, which is the one we are working on), and the original "mystery" is gone

2) In this client, MANDT is actually much more selective that the optimizer thinks it is (and it's because of this uneven distrubution, as Hermann mentioned, that forcing the index made such a difference)

4) Bad news is that this solution was good because of the special case of your development system, but will probably not help in the production system

5) I suppose the index that Hermann suggested is the best possible thing to do (the table won't be read, assuming you really only want only MATNR from MBEWH, and that it wasn't a simplification for illustration purposes); anyway, noone can really expect that getting all entries from MBEWH for a given year will be a fast thing...

Rui Dantas

Re: Poor performance reading MBEWH table

guilherme_frisoni — Tue, 14 Jul 2009 18:55:49 GMT

Hi again,

after some tests in production, i'm writing the results:

MBEWH has 68.466.116 records, all in the same MANDT.

The select statement is as follow:


    SELECT matnr
           bwkey
           lfgja
           lfmon
           SUM( lbkum )
           SUM( salk3 )
      FROM mbewh
      INTO TABLE gt_mbewh
      WHERE lfgja = '2009'
        AND bwtar NE space
      GROUP BY matnr
               bwkey
               lfgja
               lfmon
      %_HINTS ORACLE 'INDEX(MBEWH "MBEWH~Z01")'.   *Used in case 2 only

Z01 index is updated:


MANDT                                          1
LFGJA                                          5

Last statistics date                  14.07.2009
Analyze Method              mple 68.466.122 Rows
Levels of B-Tree                               3
Number of leaf blocks                    190.714
Number of distinct keys                        5
Average leaf blocks per key               38.142
Average data blocks per key              375.429
Clustering factor                      1.877.145

Case 1, withou use Z01 index:


SELECT STATEMENT ( Estimated Costs = 211.498 , Estimated #Rows = 11.737.048 )

       2 HASH GROUP BY
         ( Estim. Costs = 211.498 , Estim. #Rows = 11.737.048 )
         Estim. CPU-Costs = 40.495.510.483 Estim. IO-Costs = 208.081

           1 TABLE ACCESS FULL MBEWH
             ( Estim. Costs = 95.980 , Estim. #Rows = 11.737.048 )
             Estim. CPU-Costs = 25.870.861.489 Estim. IO-Costs = 93.797
             Filter Predicates

Time to execute: 139.392.371