Expedite Table Lookups using Internal Tables

Jimbo's picture

speed up ABAP codePart of almost any pre-validation of data in LSMW calls for a query against tables in SAP. Some of these tables are enormous with thousands or even millions of records in older systems. Comparing data to be loaded against documents already in the system using discreet external keys is a great way to avoid creating duplicate documents and is the best way to facilitate delta loads as obstacles to loading are exposed and removed. This often entails checking the key against fields that are not indexed and that can take seconds per document and make an otherwise well-developed LSMW object run for hours or even time out.

Pulling a small subset of records from the table in question into an internal table is a better solution than trying to coax a BASIS team into indexing a single free-text field for a one-time load from a legacy system. This takes a lot of memory and processing power, but it's for a one-time load so few people will actually care.

speed up ABAP codeCreate the internal table in the Global Data area in the Field Mapping and Conversion Rules. Internal tables are often prefixed with "it_" for easy identification. For the purposes of this white paper, the MKPF table is used. The BKTXT field is used to hold an external key added by the data conversion team and, because it is not indexed, look-ups on this field take as long as 3 seconds or about 42 hours for 50,000 records.

This is not the most efficient way to do this, but it only takes a few seconds longer than selecting the records directly into the table and makes it easier to understand what is happening. Here the it_MKPF internal table is defined with the same structure as the MKPF table and a working area called wa_MKPF is created for working with the data in it_MKPF.

data: it_MKPF type standard table of MKPF initial size 0.
data: wa_MKPF like MKPF.

The second step is to populate the internal table with a small subset of data from the larger MKPF table. Few of the documents have the BKTXT field populated, so filtering on those records that have content in the BKTXT field allow for a much smaller and faster subset. It's possible to reduce the subset even further by filtering on a specific prefix used for data conversion, but this bit of code alone shaves more than 95% off of the conversion process run time. Add this code to the Begin of Processing section of the LMSW Field Mapping and Conversion Rules.

select * from MKPF into wa_MKPF where BKTXT ne ''.
  append wa_MKPF to it_MKPF.

Now, with this smaller subset of pertinent records, a quick search through the internal table pulls up any matches from the data file. This snippet of sample code can be added to the ABAP code for the field that is used as the discreet key; the code needs to be adjusted based on the object and the field used as the discreet key. Free-text fields in obscure, seldom-used screens are great places to store discreet keys.

loop at it_MKPF into wa_MKPF where BKTXT eq MB11S-BKTXT.

And, with just ten lines of code the conversion with pre-validation on 50,000 records is cut from 40 hours to 40 minutes. More effort and time can be invested to shave additional seconds from the process in a case-by-case basis, but this code will most likely be used no more than once.

speed up ABAP code