Testing SQL Alternatives in a Development Environment

This tip covers testing the SQL alternatives in a development environment when it is not possible to do extensive testing in the production environment.

When optimizing a SQL statement, you need to test the alternatives that are created to see if these alternatives actually improve the performance of your original SQL statement. One place to do this testing is to have a development database that can simulate as closely as possible the production database. Some fortunate developers have the resource to create a development database from the full backup of the production database. However, most developers have a smaller development database than that of the production database to use for testing. In this case, it is important that the data volumes in the tables in the development database be in the same proportion from one table to another as is in the production database. For example, if you have 1,000,000 records in TableA and 1,000 records in TableB in your production database, then it would be better to have 100,000 and 100 records in TableA and TableB in the development database than to have 100,000 records in both tables. Keeping the data volume proportional helps get consistent performance across production and development databases.

Once you have a development database that is "like" the production database, you can use it to do the first round of testing all alternatives and find a few potentially better SQL alternatives. Then you can execute those better SQL alternatives in a second round of testing in the production database. In this way, you would be able to reduce the number of SQL statements executed against your production database. But one thing you should remember is that a SQL alternative running in the development database with different statistics from the production database may have a different execution plan in each database. This is especially true for those SQL statements without Oracle optimization hints embedded. For those SQL statements with Oracle hints, the execution plan will be more stable across the board.