EXPLAIN Facility and Predictive Analysis › Predicting Database Performance › Simulating Access with Tests

Simulating Access with Tests

Consider writing a simple COBOL program to access a table to understand the following:

• What is the proper clustering?
• How effective compression will be for performance?
• Whether or not one index performs better than another?
• If adding an index adversely affects INSERTs and DELETEs?

Example:

A database team debated whether they would gain the greatest flexibility for their database by having an entire application interface use large joins between parent and child tables, or whether all access should be through individual table access (programmatic joins). Coding for both types of access meant extra programming effort, but they had to understand the cost of the programmatic joins for the application.

They wrote two simple COBOL programs against a test database—one with a two-table programmatic join and the other with the equivalent SQL join. As a result, the team determined that the SQL join consumed 30 percent less CPU than the programmatic join.