Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Time out error on MSEG table

Former Member
0 Kudos

Hello,

We have a report which selects material document number & corresponding dates from MSEG table based on MATNR, WERKS & LGORT. However, it was timing out so we have added material document year (MJAHR) as an additional key in the where clause but it seemed not to have improved the performance as the report is still timing out. The report seems to be picking the same index (M) in both the cases (With or without the MJAHR key).

Index M has keys: MANDT, MATNR, WERKS, LGORT, BWART, SOBKZ.

MSEG table contains around 30M records.

Execution plan for the SQL statement with MJAHR key:

SQL Statement

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT

  "MBLNR", "MJAHR", "ZEILE", "BWART", "MATNR", "WERKS", "LGORT", "MENGE", "OIB_TIMESTAMP"

FROM

  "MSEG"

WHERE

  "MANDT"=:A0 AND "MJAHR" BETWEEN :A1 AND :A2 AND "MATNR"=:A3 AND "WERKS"=:A4 AND "LGORT"=:A5 OR

  "MANDT"=:A6 AND "MJAHR" BETWEEN :A7 AND :A8 AND "MATNR"=:A9 AND "WERKS"=:A10 AND "LGORT"=:A11 OR

  "MANDT"=:A12 AND "MJAHR" BETWEEN :A13 AND :A14 AND "MATNR"=:A15 AND "WERKS"=:A16 AND "LGORT"=:A17

  OR "MANDT"=:A18 AND "MJAHR" BETWEEN :A19 AND :A20 AND "MATNR"=:A21 AND "WERKS"=:A22 AND "LGORT"=

  :A23 OR "MANDT"=:A24 AND "MJAHR" BETWEEN :A25 AND :A26 AND "MATNR"=:A27 AND "WERKS"=:A28 AND

  "LGORT"=:A29

Execution Plan

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT STATEMENT ( Estimated Costs = 24 , Estimated #Rows = 5 )

  |

  ---   11 CONCATENATION

      |

      |--   2 TABLE ACCESS BY INDEX ROWID MSEG

      |   |   ( Estim. Costs = 5 , Estim. #Rows = 1 )

      |   |   Estim. CPU-Costs = 56,441 Estim. IO-Costs = 5

      |   |   Filter Predicates

      |   |

      |   ------1 INDEX RANGE SCAN MSEG~M

      |           ( Estim. Costs = 1 , Estim. #Rows = 22 )

      |           Search Columns: 4

      |           Estim. CPU-Costs = 6,577 Estim. IO-Costs = 1

      |           Access Predicates

      |

      |--   4 TABLE ACCESS BY INDEX ROWID MSEG

      |   |   ( Estim. Costs = 5 , Estim. #Rows = 1 )

      |   |   Estim. CPU-Costs = 56,441 Estim. IO-Costs = 5

      |   |   Filter Predicates

      |   |

      |   ------3 INDEX RANGE SCAN MSEG~M

      |           ( Estim. Costs = 1 , Estim. #Rows = 22 )

      |           Search Columns: 4

      |           Estim. CPU-Costs = 6,577 Estim. IO-Costs = 1

      |           Access Predicates

      |

      |--   6 TABLE ACCESS BY INDEX ROWID MSEG

      |   |   ( Estim. Costs = 5 , Estim. #Rows = 1 )

      |   |   Estim. CPU-Costs = 56,442 Estim. IO-Costs = 5

      |   |   Filter Predicates

      |   |

      |   ------5 INDEX RANGE SCAN MSEG~M

      |           ( Estim. Costs = 1 , Estim. #Rows = 22 )

      |           Search Columns: 4

      |           Estim. CPU-Costs = 6,577 Estim. IO-Costs = 1

      |           Access Predicates

      |

      |--   8 TABLE ACCESS BY INDEX ROWID MSEG

      |   |   ( Estim. Costs = 5 , Estim. #Rows = 1 )

      |   |   Estim. CPU-Costs = 56,443 Estim. IO-Costs = 5

      |   |   Filter Predicates

      |   |

      |   ------7 INDEX RANGE SCAN MSEG~M

      |           ( Estim. Costs = 1 , Estim. #Rows = 22 )

      |           Search Columns: 4

      |           Estim. CPU-Costs = 6,577 Estim. IO-Costs = 1

      |           Access Predicates

      |

      ---   10 TABLE ACCESS BY INDEX ROWID MSEG

          |    ( Estim. Costs = 5 , Estim. #Rows = 1 )

          |    Estim. CPU-Costs = 56,444 Estim. IO-Costs = 5

          |    Filter Predicates

          |

          ------9 INDEX RANGE SCAN MSEG~M

                  ( Estim. Costs = 1 , Estim. #Rows = 22 )

                  Search Columns: 4

                  Estim. CPU-Costs = 6,577 Estim. IO-Costs = 1

                  Access Predicates

Execution plan of SQL statement without MJAHR key:

SQL Statement

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT

  "MATNR", "MJAHR", "BWART", "MENGE", "OIB_TIMESTAMP", "WERKS", "LGORT"

FROM

  "MSEG"

WHERE

  "MANDT"=:A0 AND "MATNR"=:A1 AND "WERKS"=:A2 AND "LGORT"=:A3 OR "MANDT"=:A4 AND "MATNR"=:A5 AND

  "WERKS"=:A6 AND "LGORT"=:A7 OR "MANDT"=:A8 AND "MATNR"=:A9 AND "WERKS"=:A10 AND "LGORT"=:A11 OR

  "MANDT"=:A12 AND "MATNR"=:A13 AND "WERKS"=:A14 AND "LGORT"=:A15 OR "MANDT"=:A16 AND "MATNR"=:A17

  AND "WERKS"=:A18 AND "LGORT"=:A19

Execution Plan

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT STATEMENT ( Estimated Costs = 20 , Estimated #Rows = 112 )

  |

  ---   3 INLIST ITERATOR

      |

      ---   2 TABLE ACCESS BY INDEX ROWID MSEG

          |   ( Estim. Costs = 20 , Estim. #Rows = 112 )

          |   Estim. CPU-Costs = 274,290 Estim. IO-Costs = 20

          |

          ------1 INDEX RANGE SCAN MSEG~M

                  ( Estim. Costs = 2 , Estim. #Rows = 112 )

                  Search Columns: 4

                  Estim. CPU-Costs = 32,846 Estim. IO-Costs = 2

                  Access Predicates

Is there any way to improve the performance of the query other than creating an index on database table or using parallel processing?

Thanks,

Prashant

14 REPLIES 14

Sathish
Employee
Employee
0 Kudos

Could you create a new index with MJAHR as part of it and update the statistics?

Former Member
0 Kudos

Hi Sathish,

We already have lot of indexes on MSEG. So creating a new index with MJAHR might not be a good idea. I was thinking of any solution other than creating an index.

Thanks,

Prashant

matt
Active Contributor
0 Kudos

What is the ABAP that's generating this SQL, and what indexes do you already have defined on MSEG?

Former Member
0 Kudos

Hi Matthew,

The following select query generates this code:

SELECT mblnr mjahr zeile bwart matnr werks lgort menge oib_timestamp
         INTO TABLE it_mseg1
         FROM mseg
         FOR ALL ENTRIES IN it_mard1
         WHERE mblnr IN s_mblnr
           AND mjahr IN s_mjahr
           AND zeile IN s_zeile
           AND matnr = it_mard1-matnr
           AND werks = it_mard1-werks
           AND lgort = it_mard1-lgort.

We already have the standard secondary indexes - M, OIA, OIB, R & S.

Apart from this we have indexes on (MANDT, OID_EXTBOL) & (MANDT, MBLNR, MJAHR, BUKRS, WERKS, OIEXGNUM) & (MJAHR, AUFNR).

In the new enhancement pack we are also adding three new indexes as part of the remodeling of MB51 table as per OSS notes 1516684 and 1550000.

That is the reason why we are looking for any option other than index.

Thanks,

Prashant

matt
Active Contributor
0 Kudos

Rewrite as an INNER JOIN - this is usually better performing than FOR ALL ENTRIES; which is old technology invented before INNER JOIN became part of the ABAP language.

You can have very many indexes - you only get problems if two indexes are similar, or you have overly complicated where clauses. I've seen more than 11 indexes on mseg working quite happily.

Former Member
0 Kudos

Hi Matthew,

Thanks for replying.

Initially the requirement was coded using inner join but the select statement was still timing out so we decided to check if FOR ALL ENTRIES IN performs better.

As the index M is quite similar to what we intend to create using MJAHR. So we were scared if it might confuse the optimizer and affect other program's performance.

Thanks,

Prashant

matt
Active Contributor
0 Kudos

If they're close, then yes, it's better not to do it. I've also experience of such things really screwing things up!

INNER JOIN should be better, but if you're really timing out there's only a few options available, which really apply to any long running program:

1. Decouple the selection process - have a seperate program that gathers the data from the one that displays it

2. Narrow the selection so less data is selected

3. Run in background

4. You might be able to use parallel processing if you've got independent data selections - e.g. do one thread per plant

5. Consider if the report is really needed in ERP - perhaps it would be better to have it in BW?

At the moment the business isn't getting any data so anything you propose has got to be an improvement.

Former Member
0 Kudos

Hi Matthew,

The end user is not happy with running the report in background, so sadly we will have to stick with the options to optimize in foreground only.

I was thinking parallel processing and index creation as last resort, so still not giving up hope of some other solution.

I will need to check with BA if it can be handled in BW and if the user is fine with it. Thanks for this suggestion though

Thanks,

Prashant

matt
Active Contributor
0 Kudos

Index creation, from what you said, is unlikely to help, I think. Parallelisation is not too bad a solution, if the program lends itself to being split up into independent units.

BI will work if the user doesn't need the absolutest latest possible most up-to-date view. Remember this all comes down to actual business requirements - not what the user says he needs. It is often our duty as developers to push back. Give the users what they need - not what they want. Do you want the data? Then accept a background report!

I spend quite a lot of time optimising programs. The only solution can often be "select less data".

Sometimes users have to understand that what they want is not always possible. If I'm selecting a million records, I can't have a response time under 3 seconds! The point is that if it is simply not possible to run the report in foreground because the user is selecting too much (or too complex) data, they'll just have to remain unhappy!  Maybe they can get something from their doctor to treat that.

Former Member
0 Kudos

Well I agree to most of your points Matthew.

Currently I am analyzing the execution plan with the following documents in both the systems and checking what might be prompting the CBO to use different indexes:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/d0eefeb8-f789-2a10-baab-f7690451b...

Thanks,

Prashant

matt
Active Contributor
0 Kudos

Don't forget you can use database hints to force the use of a different index.

Former Member
0 Kudos

I already did that Matthew, I did that in ST05 query analyzer itself but the estimated costs are being shown quite high in comparison to the index chosen by CBO.

0 Kudos

I agree with Matthew in the suggestions of reducing the selections.

This is what even I do where there are large volume of data. Instead of just using fiscal year using date would also limit the amount of records.

Updating DB statistics and using DB hints would ensure correct index usage.

Former Member
0 Kudos

Hi Sathish,

I've tried all IFs and BUTs. Providing additional fields won't help until or unless the CBO chooses another index (with less estimated cost), which will only be possible if those additional fields are present in an index which already contains the fields which I am already using.

Thanks,

Prashant