Anthony Cecchini is the President of Information Technology 鶹TVվ (ITP), an SAP consulting company headquartered in Pennsylvania. ITP offers comprehensive planning, resource allocation, implementation, upgrade, and training assistance to companies. Anthony has over 20 years of experience in SAP R/3 business process analysis and SAP systems integration. His areas of expertise include SAP NetWeaver integration; ALE development; RFC, BAPI, IDoc, Dialog, and Web Dynpro development; and customized Workflow development. You can reach him at [email protected].
Which SQL Database AccessesCause the Highest Load?
Picking up from last month, we will look at how to use the ST05 Performance Trace Tool to solve performance issues. Rememberyou’ve ruled out deficiencies in the system setup, mishandling byusers, or the need for parallel processing, then you need to revisit yourcode and see if the source of your performance problem is hiding there in your SQL commands.
Central to any database access performance analysisis an understanding of which accesses cause the highestload. It’s only logical that this is where you concentrateyour efforts.In order to identify which tables are excessivelyaccessed, Performance Trace enables you to aggregateall accesses by the names of the tables. By summingup all the request times, you can see which accessesto which database tables are causing the highestdatabase loads.
For instance, I will use SE16 and read table MSEG with no key fields entered and use the ST05 trace to record the SQL. As you would expect this takes a minute. Her is the output of the display trace below. By following the menu path Trace List=>Summarize Trace by SQL Statement or hitting SHIFT+F8 you can see the summary.
After activating the Summarize Function, you will see a list with the informationaggregated for each table. The screen shot belowdisplays a list that is already sorted by time toidentify the database tables with the highest loads.
Obviously in our example the MSEG read was the highest. But in your trace, the high load might be caused by the number ofaccesses, the amount of read data, or by expensive SQL statements. Pick the highest loads, and focus on these first.
OK, then what?
Examine the “Execution Plan”Used by the Database
For each access, the Back-End Database determines the best wayto handle the request, this is called an Execution Plan. There are two broad options for an Execution Plan when using SQL in your ABAP objetcs.
Perform a full table-space scan where the databasesimply reads all records from the table and thenpicks the ones that meet the conditions from theWHERE clause. Obviously, the higher the numberof entries in the table, the longer this proceduretakes.
Use an index that points directly to those recordswith specific value combinations for the indexfields. This can substantially reduce the numberof records to be read.
If you are new to SQL and need a bit of level set, then here is a good way to think about this. Suppose you are reading a book on ABAP OO (Jeeezz…get a life!) and your looking for some good pointers on OPEN SQL and performance. Let’s also assume this version of the book you have you pirated and didn’t pay for so there is no index or table of contents! How would you find what you were looking for? You would have to scan each page looking for something to do with OPEN SQL and Performance, page by page, until you read every page in the book! This is what the database does when you select against a table without giving it a WHERE clause with a field in the primary, or secondary key, or worse yet NO WHERE CLAUSE. This is essentially a Table-Space Scan.
Now if had paid for your ABAP book, you would have the index and TOC (and no criminal record). Now, if you know the values you want to look up and go to the back of the book and use the index, you would get one or a few pages that are relevant to the topic saving you all that scanning! If you get one page back, this is like using the primary key of a table. Even if your search wan’t that specific like using the first 2 fields of the primary key of a table when there are 5 fields, you might get a few pages to look at. The time you need to find the right page is onlymarginally dependent on the total number of entries (i.e., the size of the database table). This is using an Index.
However, if you want to use the index but the data you have been given isn’t one of the fields in the index, this would result in doing a table-space scan because the entire table would need to scanned looking for the field. That said, you could create a secondary or Custom Index via SE11. This is normally done for very large tables and the back-end DBMS would use this index and NOT perform a full Table-Space Scan. A caveat is that you need to be careful creating too many custom indexes. Please keep in mind that while additional custom indexes will accelerate accesses, actionsthat modify the database table (e.g., INSERT, DELETE, UPDATE) will suffer performance degradation,since these actions require an update of all indexes.
Back to our regularly scheduled Blog…..
For each selection in the SQL trace, you cancheck the execution plan that is being used by thedatabase. Simply place the cursor on the linecontaining the statement in question and then selectthe button, or use the menu path Trace =>Explain SQL, or hit F9. (see below)
The look of the next screen is heavily dependent onthe specific database you use, so there are severaldifferent possibilities for the display you might see.In general, you will see the statement as it washanded over to the database and the execution plan(note that for some databases, you might have toactivate another button to see the execution plan).Then you can decide whether or not the selectedexecution plan is acceptable.
So in our example where we used SE16 wide open on MSEG, we would expect to see a Table-Space Scan or theentire table being read. And this is what we see in the Execution Plan Below.
OK, lets change this up a bit and use the full key of Material Doc#, Year and Item and trace the select again in SE16. (see below)
Lets look at the EXPLAIN of the Execution Plan below. Whatyou see is an Index Unique Scan,which means that all index fields were specified with“=” in the WHERE clause. This of course is very fast, as you see by the cost. The cost of this was 2 vs. the cost of the Table-Space Scan above which was 3,850.
What do think would happen if we used a partial key? Say just the Material Doc# without the year or item? If you guessed we would still use an Index, YOU WERE RIGHT! However, because it wasn’t the FULL KEY, we would requirethe database to read several indexentries to determine the result set. This is called an Index Range Scan and looks like the screen shot below. Please note the cost is only slightly higher than the Index Unique Scan.
The execution plan also tells you how the databasehandles a selection using a JOIN command. AJOIN is used to access combined information frommultiple tables. When using a JOIN, you must considerthe way the database reads and uses the informationfrom these tables. Usually you expect thedatabase to perform a “nested” JOIN, which meansthat the database first reads the information fromone table according to the restrictions in the WHEREclause, starting with the table with the “best” restriction. Then using the result set, and considering the JOINcondition, the next table is accessed to read furtherdata, and so on. In this way, the number of recordsto be read from the different tables should progressivelydecrease.
However the database might decide to use a “merge”JOIN, where the records from the affected tablesare read independently and only restrictions in theWHERE clause are considered. then in a second step, theresult sets are merged according to the JOIN condition.Obviously, this can cause very long runtimes,especially if large tables are affected that have nosuitable index available. In such a case, it may bebeneficial to replace the JOIN statement by two ormore selections on each table, using the FOR ALLENTRIES clause to emulate the nested JOIN. Youcan also use this trick if the database uses a nestedJOIN but starts with the wrong table.
Conclusion
In my work with SAP, I have found a number of commonperformance pitfalls. Sometimes there is a weakdesign, which sometimes makes a correction quite expensive —a widespread modification of the program would benecessary instead of only a local optimization. Other times the extended runtime is simply caused bythe way functionality was implemented — i.e., notall relevant performance aspects were considered at the start.
Whatever the cause of your program’s performancetroubles, optimizing the runtime of the applicationrequires that you first find out where the majorpart of the runtime is spent. In this effort the ST05 Performance Trace can help optimize your SQL. This in conjunction with other tools like SAT, Code Inspector, and other Static Checks will go a long way in helping you develop quality ABAP code in your career.
Don’t forget to take a look atABAP Database SQL Analysis Using The Performance Trace – Part 1 for and understanding of the SQL Trace tool look and feel .
Below are some videos that help explain SQL and Optimization
References:
[edit]. A SQL query to a modern relational DBMS does more than just selections and joins. In particular…
