As we all know, there are many factors that influence the performance of your application. Proper memory allocations, physical design, how the SQL is written and workload all affect the way your queries perform. One of most overlooked parts of testing an application is testing queries under production workload conditions.  A seemingly simple query runs fine in your test environment, but how will it perform when 10, 25 or 50 end users are running the query concurrently?

One of the neater features in Toad^® for DB2 is the integration with SQL Optimizer and Benchmark Factory^® to do automated index analysis and scalability testing. Once you have a query built the optimizer can recommend indexes and simulate real world user loads. 

In this example below a query containing a 3 table join is being run in a DB2 V8 Windows environment. The SLA for this application is to maintain a sub-second response time during peak user time. The query is initially test run with production data volume with an average elapsed time around 230 milliseconds; well below my SLA requirement. I then put the query through a scalability test escalating users from 10 to 25, to 50.  As you can see from the graph, the average response time jumped to over 4 seconds, which is completely unacceptable for this application.

After running the query through Toad’s EXPLAIN, you can see that all 3 tables are doing tablespace scans. 

The query was then launched into SQL optimizer and the Index Expert option was run.  It determined the indexes to be created to optimize this query. 

The Indexes were created and the scalability test was run again. This time the query maintained its sub second response time even with 50 concurrent users.

This simple demonstration highlights the importance of doing realistic testing of SQL prior to putting it into production.