SQL and query performance can be a challenge in many AS/400 and iSeries shops. Many newer applications use SQL as the standard database access method. While I/O performance can be an issue with RPG and COBOL applications, SQL based applications seem to more easily impact your AS/400 and iSeries servers. Many SQL developers don't even have authority to build new logical views on production systems, in an effort to improve the performance of their applications.

Ensuring that the operating system finds and utilizes efficient access paths is critical in the effort of providing high-performance applications to your end users. Increases AS/400 and iSeries hardware costs are directly affected by your server's ability to fulfill end-user requests in a timely manner. If your database server reads 1,000,000 records to find and select only 1,000 records, response time won't be at expectations and hardware costs will be greatly elevated.

The Query Optimizer is the tool within the Workload Performance Series toolset where you can identify which database files and SQL requests that would most benefit from some database tuning. Whether you change SQL requests to use existing logical files or whether you build new permanent logical files, the cost savings can be enormous.

In this example, 439 table scans occurred over the same file during the data collection sample:

Over 200,000 records were read by these SQL requests to select 4 records. It takes a lot of hardware to get 200,000 I/Os to perform as well as 4 I/Os. Some simple database tuning or SQL changes could dramatically reduce the resource requirements of each of these SQL requests. Let's not just give them more CPU, memory and faster disk drives. Let's get there to be 4 I/Os and then tune your hardware around an appropriately performing application.

'Table Scan Analysis' is an important process to go through with the Query Optimizer tool. Many SQL requests could benefit from some simple changes. You will get to a point though where you could be building to many new logical files. Know when to stop! Only build a new logical file if necessary to solve a critical business issue. Don't just build it because of the 'Skipped Records'. If the SQL requests are not impacting your system or your business in a negative way, leave it alone.

The Query Optimizer tool has 'Elapsed Time Tracking' features that might be of great help in your environment. It might be that there are very few table scans occurring, yet web or client/sever users are still experiencing response time issues. The end-user doesn't care how much I/O is occurring, they care how long the requests take.

The following data shows that 1 database is responsible for 46% of the elapsed time of all SQL requests during the data collection sample:

Over 13 billion records were selected via SQL fetches. The I/O and elapsed time resulting from these SQL fetches is just a critical as the unnecessary resulting from 'skipped records'. There were 37,698 SQL requests that were not performing ideally. In this case, some SQL changes to further subset the data with better selection criteria would be a great enhancement.

A browser or client/server application pre-loading 10,000 records into a drop-down list box can cause this kind of issue. Providing the end-user with some additional functionality for further defining their selection criteria, could dramatically improving the performance of this application. Hardware requirements and costs would also be dramatically decreased.