Index Advisor, Part 1
April 4, 2012 Paul Tuohy
|
The use of SQL within our applications is on the increase. After a shaky start in the early days of the AS/400, SQL has gone from strength to strength with every release of the operating system. The use of embedded SQL (in RPG) stabilized with V5R1 and really came into its own when free-form SQL was introduced in V5R4 and PTFed back to V5R3. But with SQL, and embedded SQL especially, there is always that lingering doubt about SQL performance when accessing data. We have all heard horror stories about the lights in the building dimming when a certain program was run. But the solution to most of these “nightmare programs” was usually a very simple one: make sure that the query engine has the right indexes to work from. Simply put, most of the performance problems came from the query engine having to build temporary access paths in order to run the requested SQL statement. Those of us who are developing applications (with embedded SQL) make use of Run SQL Scripts and Visual Explain to determine what indexes may be required to fine tune our statements. My colleague Skip Marchesani talked about this in his article Visual Explain for Run SQL Scripts. But what about all our existing programs? What about those programs we had written before we knew that indexes were good things to have? What about those programs we had written 10 years ago that are suddenly starting to go slow because of the amount of data that has accumulated in the tables? What about those programs that are vendor supplied that we don’t have the sources for, so we don’t know what the SQL statements are? Help Is At Hand While the query engine is busily running SQL statements, it is also “logging” information about all of these required access paths in a table named SYSIXADV (in QSYS2). You can knock yourself out by running a SELECT against this table and working your way through the mountains of information available (which is something you may want to do at a later stage). But, initially, there is a much simpler way to get to grips with the information in SYSIXADV and to quickly get required indexes in place. Both System i Navigator and the browser based Systems Director Navigator for i offer a very friendly interface to the information in SYSIXADV. Let’s have a look at the basics of using the Index Advisor function in System i Navigator. In my next article, part two of this series, we will look at a few of the other options available with Index Advisor and we will have a quick look at the same functionality in Systems Director Navigator for i. Index Advisor To access the Index Advisor in System i Navigator, expand the Databases option, right click on the name of the database and select Index Advisor>Index Advisor from the context menu, to be presented with a window similar to the one shown in Figure 1.
When you first open Index Advisor, the columns you see will be in a different sequence from those shown in Figure 1. I changed the sequence by pressing F12 (or you could select View>Customize this View>Columns from the menu) and specifying the sequence I required. But what about the information displayed? The Index Advisor is displaying a list of recommended indexes along with a lot of information about why they would be useful. The list is in a descending sequence based on the number of times an index was advised (the Times Advised for Query Use column in Figure 1). In part two, we will take a closer look at some of the information presented in the Index Advisor but, for the moment, let’s say we want to create one of the recommended indexes. First of all, check the Estimated Index Creation Time column. You might want to pick a different time depending on how long it is going to take. Simply right click on the required line in the list and select Create Index from the context menu. You will be presented with the standard New Index window (as shown in Figure 2), all you have to do is enter the name of the index and click OK to create it.
Word of Warning The SYSIXADV table can contain a lot of information. When you open the Index Advisor window the full contents of SYSIXADV are not retrieved. So, when you page down, there can be a delay while the next group of rows are retrieved. Part Two In part two, we will have a look at some more of the information available in Index Advisor and at some of the other options available. We will also take a peek at the Index Advisor functionality available in Systems Director Navigator for i. Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page. RELATED STORY Visual Explain for Run SQL Scripts
|
