Skip to Content
In some posts here: Performance – what will kill you and what will leave you with only a flesh wound Anyone Got Some Real Benchmark Stats on “For all Entries”??? “Yes, Virginia, the check is in the mail” (or, why you can’t trust SQL) Rob Burbank and I have been tossing around various ideas on how to do efficient data retrieval when you’re forced to retrieve your data inside the tiger-cage of SQL.*** Here is yet another approach that some may not have thought of because to think of it, you have to think OUTSIDE the SQL box, with all its attendant nonsense concerning cardinalities, joins, views, etc. Your client wants a program against MARA in which customers must select by creation date (ERSDA) and may optionally select on group (MATKL) and type (MTART) as well. Fuirthermore, the ERSDA criteria will be narrow – this report is run frequently to find out certain information on new MARA data. So you do the obvious thing first – you ask your DBAs to check the cardinalities on prod mara-ersda, and when they tell you they’re OK, you ask them to add a custom index to MARA on ERSDA. But now – what about mtart and matkl? There is an SAP delivered index on these, but before you start thinking about adding these to your select … STOP! and do the following instead: 1) define a hashed itab i_mtart with one field in it: mtart 2) define a hashed itab i_matkl with one field in it: matkl. 3) Populate i_matkl from your select criteria for matkl using a routine like this: FORM get_matkls. CLEAR i_matkl. SELECT matkl FROM t023 INTO wa_matkl WHERE matkl in s_matkl. INSERT wa_matkl INTO TABLE i_matkl. ENDSELECT. ENDFORM. “get_matkls (Or, just a select into itab if this will work on a hashed itab – I don’t really remember.) 4) Do the same for mtart 5) When you retrieve your mara rows into an itab i_mara using the simple index you’ve created on ersda, make sure to grab matkl and mtart in this retrieval. 6) Then after the mara retrieval, loop thru your i_mara itab doing hashed look-ups on i_mtart and i_matkl (either or both, depending on your selection criteria). When these look-ups return sy-subrc <> 0, delete i_mara. So here’s the challenge to all you SQL-jockeys out there. Take your best shot at a select statement against MARA with just ersda, matkl, and mtart in it, and post your statement in response to this blog post. (Assume when writing your select that mara-ersda has a custom singleton index on it, as noted above.) I will then test your select against the hashed approach and report the results. Anyone wanna bet on the outcome? (All proceeds going to Craig , Mark, Marilyn, and Mark, of course – they’re “the house”.) Finally, for those who missed yesterday’s post on why the SAP consulting community must do a better job of policing itself, here is the link to that post: Why the SAP Consulting Community Must Become a Collective Change-Agent
To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. David Halitsky Post author
    Because one thing for sure is that the “standard” approach is going to yield different results depending on the complexity and ranges of the matkl and mtart criteria …

    … if your matkl/mtart selects were simple, then what your DB did is to use the mtart/maktl index even though it’s below ersda in your select, and then cull out the unwanted ersda’s from the foundset.

    (0) 
  2. David Halitsky Post author
    tests that vary the complexity of the mtart/maktl select options. 

    Also – can you check the number of records in your actual test mara table – not the foundset – the full table you’re going against …

    Thanks very much for taking the time to code the code.  I will follow up on this and post results.

    (0) 
  3. David Halitsky Post author
    mtart and maktl are optional but ersda is obligatory.

    So suppose I’m correct that what Rich’s DB did is to use the maktl/mtart index and then cull out the unwanted ersda’s from the resulting foundset.

    Then in this “standard” approach, when neither maktl nor mtart is specified, the select will force the DB into a full-table scan on mara, unless you put a custom index on ersda.  Therefore, all approaches to this problem must assume a custom index on ersda.

    In the non-standard approach which I exhibited, you must, of course, make the hashed lookups conditional on whether or not maktl and mtart select criteria are initial or not …

    (0) 
  4. David Halitsky Post author
    one of twp things is happening:

    a) either your optimizer is using up the extra time to see if the cardinalities on the SAP-delivered matkl/mtart index make it worth using this index and then deciding to use this index;

    b) or – your optimizer is deciding that on 250K records, it might as well do a full table scan.

    Since you have not yet indexed ersda on your system, one piece of additional information that might be useful is to know whether you do better or worse than .15 when you just run with the same ersda range but no mtart and no maktl criteria. (In this case there must be a full-table scan because ersda is not indexed and no values have been provided for the DB to use in the mtart/maktl index.)

    Also – it would be useful to run a SQL trace and find out what the DB is doing in your original (.15) case – full table scan or maktl/maktr fetch followed by ersda filter …

    (0) 
  5. David Halitsky Post author

    During the migration process problems have been reported for this blog. The blog content may look corrupt due to not supported HTML code on this platform. Please adjust the blog content manually before moving it to an official community.

    (0) 
  6. David Halitsky Post author
    that our expectation is correct: a full-table scan is being invoked when matkl/mtart are not present, simply because ersda has not yet been indexed.

    So that means that the select DOES use the matkl/mtart index when it has been provided with values for either of these in the select criteria.

    And that PROBABLY means (note I say PROBABLY) that I can design SOME set of mtart/maktl criteria which will run faster in my approach because the criteria are so complex that they’ll just screw up the optimizer and force it into a full-table scan …

    And this was my point – SQL is an 80/20 solution that will get you just so far … after that you really do have to think “outside the SQL box”.

    (0) 
  7. Dirk Herzog
    David,
    I don’t understand why you need to populate those extra tables from the database. If the ERSDA selection is very selective I’d do the following:

    SELECT (fields) from MARA into l_s_wa
        where ERSDA in s_ersda.
      CHECK l_s_wa-matkl in s_matkl.
      CHECK l_s_wa-mtart in s_mtart.
      INSERT l_s_wa INTO TABLE l_t_table.
    ENDSELECT.

    The separate hashed table is bad if you have lots of different matkl and mtart and the population of the help tables takes too long.

    Of course you could also create an index on ERSDA, MATKL and MTART.

    Best regards
    Dirk

    (0) 
    1. David Halitsky Post author
      (with a lot of or’s that make it difficult for the optimizer to resolve cardinalities), the mtart/maktl combo is going to force the optimizer into a full-table scan on mara.

      So the question is – where do you want your full table-scan: a) on mara (which is relatively huge); b) or on the config tables (which are relatively small.)

      And as I said to Rich, this was really the point of my post: SQL and relational databases really comprise an 80/20 solution which does not take you beyond the trivial unless you’re willing to add indices.  And as we know, the relational DB’s aren’t too forgiving about performance on update/insert nor retrieval when you start adding more than 5 or 6 indices.

      Plus – I also wanted to point out that SAP’s selection machinery allows for contingencies that are far too complex for the native DB optimizers to handle, and that real care must therefore be exercised.

      (0) 
      1. Dirk Herzog
        But my SELECT statement does no single full table scan at the DB at all. You select on the MARA with the ERSDA index and then use the IN operator without any additional database access.
        But you’re right. Today I would expect a DB to get the selections on all three fields and optimize its selection accordingly. And relational DBs aren’t made for this.
        (0) 
        1. David Halitsky Post author
          Your way is probably a better way of avoiding the optimizer than mine!

          But I’m glad we agree that because SAP’s selection machinery is so rich, we sometimes (often?) have to avoid the current DB optimizers.

          There was a time, of course, when DB’s used bit-mapped indices that could be and’d or or’d in a heartbeat.  But that was before the coming of Codd and Ellison his prophet.

          (0) 
  8. Dirk Herzog
    David,
    I don’t understand why you need to populate those extra tables from the database. If the ERSDA selection is very selective I’d do the following:

    SELECT (fields) from MARA into l_s_wa
        where ERSDA in s_ersda.
      CHECK l_s_wa-matkl in s_matkl.
      CHECK l_s_wa-mtart in s_mtart.
      INSERT l_s_wa INTO TABLE l_t_table.
    ENDSELECT.

    The separate hashed table is bad if you have lots of different matkl and mtart and the population of the help tables takes too long.

    Of course you could also create an index on ERSDA, MATKL and MTART.

    Best regards
    Dirk

    (0) 

Leave a Reply