Problem solve Get help with specific problems with your technologies, process and projects.

Improving run-time buffering/indexing

This excerpt from "ABAP with Style" looks at ways to improve run-time buffering and indexing.

This tip is excerpted with permission from his book titled "ABAP with Style." To purchase the full text, go to:... ------------------------------------------------------------------------------- You may realize that certain tables get hit a lot with the same key fields, which causes additional I/Os for data you already have. If memory permits, you can buffer the information yourself. If memory is scarce, you can still avoid multiple reads, if you sort the data to be processed accordingly. Suppose you read lots of MARC records (material master on the plant level) into an internal table and you need to read the T001W/T001K entries for each plant, you can sort the internal table by plant first and loop across it. Only if the plant changes, you need to issue a SELECT against the database. For all subsequent entries you just use the same data. Of course, you can re-sort it afterwards. Sorting tables in memory takes up some run-time, but that is easily offset by the gain in avoiding superfluous I/Os. Some tables have buffering turned on, which means a lot of records (if not all) are stored in memory, even if you issue a SELECT. But you can not always reply upon the data being available in memory, and if it is not, the system will go against the database again. Most databases buffer pages not records, so chances are that for each I/O to the database you will have a few records in memory afterwards. The decision to make a Z* table buffered should be based on the size and frequency of it being hit. Consult your Basis administrator for this decision. Usually the smaller the table and the higher the demand to read it, the more likely you should have it buffered. Both standard and custom tables have key fields for direct retrieval, but for a specific request you may not know them. When the table is to large for buffering you will end you will end up with very bad response times, if you try to select the records by non-key fields only. Many requests are based on a creation or update date (everything posted in the last week) or a status flag (all open items). In order to keep the run-time low, SAP provides secondary index tables (e.g.,BSIS/BSAS for BSEG) and matchcodes, which are separate tables with selected fields from the main table in a different order. Those tables are smaller, since they do not contain all fields, and they can have a key sequence according to what you need. They always contain the key fields of the target table. Read the entries you are interested in first, then use the keys to do direct SELECTs on the table for additional data. Despite adding I/O, the overall run-time is a lot better. You will have to code for those selections yourself explicitly. Be aware though, that these additional tables add to the system load during the update, because they have to be kept in synch when the main table changes. You can create your own matchcode IDs, but each new one takes up some processing time, -which given a high volume- can add up and slow down the posting transaction. Finally, the best run-time improver is an index in SE11. That index is handles by the database directly and its use is implicit. You select straight from the table in question. The database then makes a decision how exactly to retrieve the data, and if it finds a good index, it uses it. You cannot specify an index explicitly , but you can rearrange your WHERE clause. If you have additional criteria, use the DB trace to find out whether you can specify them in the WHERE clause as well. If it still uses the index, go for it. Those criteria that interfere with the proper index selection, however, should be taken out and put into a CHECK statement (which is contrary to common guidelines). An index also adds to the burden during posting, so be considerate when you define your own. Two or more indices with similar fields may cause the database to pick the worse one, so less can be more. Avoid using the main table's key fields as the high-level keys in an index- that does not buy you anything. If you know those key-fields, you might as well read the table directly. So can indices be created for standard tables? Absolutely (if the table type permits it, e.g., you cannot do it for cluster tables). Although they should be designed and tested during the initial project phase. Using an index is generally better than creating a Z* table and populating it in a user exit (this needs to be evaluated in light of the tasks you wish to perform).

This was last published in May 2002

Dig Deeper on SAP ABAP



Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.