To demonstrate the power of Centerfield's toolset, an industrial-strength query benchmark was run at IBM’s Teraplex Center. The results showed that Insure/INDEX can greatly improve query performance in an automated fashion thus overcoming one of the biggest obstacles to good query performance on the AS/400 – building the right indexes.

The Setting

Rochester, Minnesota – birthplace of the AS/400 and home of the gurus who build DB2 and its query engine. Inside the walls of the development lab, is the Teraplex Center – where the biggest AS/400’s are challenged to perform unheard of feats and prove that "Every day is scalability day." insure/INDEX was put through its paces in the Teraplex Center with a 20-query workload and an 8-gigabyte star schema database.

One of the more popular data warehouse architectures is based on a "star schema" table organization. At the heart of a star schema is a fact table. As you might expect from the name the fact table stores discrete data elements about an event. Typically the data in a fact table is encoded to conserve space since years of data might be stored for analysis. Surrounding the fact table are many dimension tables. The dimension tables supply additional information necessary for the analysis such as the text descriptions of the encoded data in the fact table. They also support a hierarchy of information that enables the drill up and drill down functions in a query tool.

A star schema enables query tools to "slice and dice" data. This paradigm allows end-users to do quick, thorough, and intuitive analysis based on business metrics. For example, the marketing department might want to look at sales across multiple regions and product groups. At the highest level, the total sales for a region would be summarized. To analyze the success of product by region, the analyst would drill down into each region and look at the sales of each product category. Further drill down might be done on particular territories within a region or single products within a group. The idea is to allow a knowledge worker in a business to look at data from a business perspective without having to understand table structures or relationships.

While very easy for an end-user to understand and navigate, database queries can be difficult to tune since the database organization does not lend itself to simple optimization. The challenge – minimize the execution time of the 20 queries.

The benchmark used a star schema created in conjunction with a workload that simulates real-world business analysis. The benchmark was run interactively using SQL queries driven from a script containing the 20 statements.

The environment included:

Machine	Model 650
Processors	12 processors
Pool size	8 gig
Database parallelism	*MAX
Pool size	Yes

As a baseline, the benchmark was run with a star schema that did not have any database indexes. Insure/INDEX was used to profile a second run. The built-in index analyzer feature was used against the profile to recommend indexes that were then created. The benchmark was then run again and measurements taken.

The results were dramatic. Before any tuning was done, the 20 queries ran in 36 minutes. When the recommendations from insure/INDEX were taken, the total time dropped in half. After using the analysis features built into insure/ANALYSIS, to further enhance the recommendations, the overall time dropped by 65% (see the following chart).

Summary

As can be seen by these results, Centerfield's tools provide a straightforward method to tune the performance of a query intensive environment. With minimal skill or expertise, the toolset was able to cut the elapse time of these queries by two-thirds.