This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

SAP R3load table splitter – Table Analysis Performance

Darryl Griffiths
Be careful when using the R3load table splitter from the Software Provisioning Manager screens.
You are asked to supply your table split file, in the install guide, in the format “<TABLE>%<# SPLITS>”.
However, this does not supply the table column to split by.

When splitting large tables, during the Table Splitting preparation phase (before you even start exports), R3ta can run for quite a while whilst is performs scans of the available INDEXES and then COUNTs the number of rows in the table(s) to be split.

It’s trying to define the most apt column(s) to use for generating the WHR files which contain the query predicates.

I tried adding a specific column during the initial table splitter screens, where you can specify the column to use. However, this seems to be completely ignored.

The best advice, is to prepare your table split during your proof phase in the sandbox environment, then potentially manually adjust the final WHR file to account for any additional rows in the table(s) to be split.
This will save a lot of time and effort in the true production conversion run.
Also, ensure that the indexes on those tables, especially the ones that the WHR predicates refer to, are rebuilt if possible.

6 thoughts on SAP R3load table splitter – Table Analysis Performance

  1. You've probably investigated this already, but….
    You know that R3ta uses the R3ta_hints.txt file to specify the table column to split by. From memory, it gets extracted to the Installation Directory, and I would expect R3ta to be running from that directory as well.
    Is R3ta ignoring the hints file or is the hints file missing ?

    Reply

  2. Hi Martin,

    Very useful info. Thanks for your contribution.
    I've not heard of this file until now…

    Looking on here:
    https://websmp204.sap-ag.de/~form/sapnet?_SCENARIO=01100035870000000202&_SHORTKEY=01100035870000735220&_OBJECT=011000358700000288002012E

    It says:

    "With the file R3ta_hints.txt, you can instruct R3ta to use specific columns of a table to create a WHERE condition. The file has to be located in the directory in which you start R3ta.
    With one line in the file R3ta_hints.txt, you specify the table name followed by one or more column names, separated by a blank. The columns have to be part of the primary key of the table. These specifications are used by R3ta to create the WHERE condition.
    A file R3ta_hints.txt with column hints for several tables is delivered with the Installation Master DVD. After the Table Splitting Preparation, make sure you select the Split predefined tables checkbox and add the R3ta_hints.txt file in the Split STR Files dialog box. If you want to modify this file, copy it from the directory /COMMON/INSTALL to the installation directory and adapt it according to your requirements as soon as the Parameter Summary dialog appears, and before starting the processing phase.    ".

    So, this would appear to be the same file that we are talking about.
    The only thing I can think of, is that it is getting ignored, as you say.
    I guess it's possible to manually generate the "split file" or hints file (whatever we should call it) but I don't know exactly what the format of the file is, since the above text states to separate the table from the column name with a "blank", but the install guide I've previously read, shows a "%" between the table name and the number of splits. How confusing.

    I'm not in a position to test this out, so if anyone has a working SAP system and can confirm the text file format…

    Reply

  3. Hello Martin,

    hopefully you have received a response to your comment.

    The R3ta_hints.txt file is used by the R3ta binary automatically. It only helps speed up the splitting if there is an entry for the table(s) you want to split.

    The file that contains the %<# of splits> is referred to as the whr.txt file, but you can call it what you want. That file is generated by you manually (no empty line is allowed at the end of the file, by the way). You have to use SWPM to use the "whr.txt" file and specify it in the field specified as "Location of whr.txt file" or some similar field label.

    If you use R3ta manually, then you just create the R3ta command for each table that you want to split and the %<# of splits> is specified as a parameter in your R3ta command. R3ta does not automatically read the whr.txt file by itself. That is a benefit of using SWPM.

    You can manually modify the R3ta_hints.txt file, if you already know which key field to use to get the highest number of distinct values. There are already tables and their keys in the file for some of the more common tables that need to be split, but you can add any table you like to the list, for example, using a BW table:

    /BIC/AZS_PMON00 RECORD

    where the first column is the table name and the second column is the key field. In the SAP delivered R3ta_hints.txt the tablename and key are separated as a tab. Supposedly, you can also specify multiple key fields (sepearated by a space), but I haven't had any luck with this and the R3ta ends up checking the selectiveness of each key field. In essence, it ignores the entry for that table in R3ta_hints.txt.

    If you are using SWPM for the splitting, you specify the location of the R3ta_hints.txt file. If you are manually running the R3ta command, the R3ta_hints.txt file needs to be in the same directory as the R3ta binary.

    I hope this helps you or, at least, the next person who finds this post.

    Kind regards,
    Warren Chirhart

    Reply

  5. This table splitting and optimization still puzzles me. Maybe someone can help me understand it better.

    After migration the DEV system(oracle db but small system) I migrated the QAS system. DB around 1.5 TB. Time was not a problem but I still did table splitting for the first 10 tables with size>10GB. Export time around 16 hours.

    After the migration of QAS was finished I wanted better times for having a better time on PRD. I followed lots of notes, changed DB parameters, listener parameters etc. I did another kind of table splitting: because I have only 8 CPU I only spitted the first 10 large tables in maximum 8×3=24 splits. I've also set to unload in a sorted order – by name, setting the 10 largest ones (most of them have names starting with S) to be the first ones.

    Results: A little bit better. From 16 hours, now I exported the system in 12 hours. Good,..but not so great..

    This weekend I tried something different:

    1. No table splitting at all.
    2. At package splitting screen, I am letting everything as default – just one exception: I have set on "Numbers of tables to be extracted" 40 instead of 10(as it is by default).

    Result: BOOM – total export time–> 5 h 44 minutes.

    Nice. I am very happy for this result, but I learned nothing. Why is now so fast? Without any table splitting..

    Packages are the STR files, so for the first largest 40 tables it extracts them and creates new separate STR files for each one of them. Great! But I dont get it. This files are just structure definitions of the tables. Why is this making the entire unload to go so fast?

    Can someone please enlighten me?

    Reply

  6. Hello,

    It could be that when you choose to split tables, the export process first spends a lot of time scanning the tables to see how to optimally split them (which columns to use).
    On large tables, this extra work can sometimes cause a lot of extra effort if you don't do some additional tuning upfront.
    If you don't split the tables then this work is not required.

    You should consider using the export time analysis Java tool (search for 'migtime') to tell you where it spent the most time during the export process.

    Regards,

    Darryl

    Reply

Add Your Comment

* Indicates Required Field

Your email address will not be published.

*