Implementing These Solutions

Now that your first paycheck from High-Hat has cleared and you've decided how to address these problems, what's the safest course of action to implement your solutions? You reviewed this topic in general during Chapter 1, "Setting Up an Optimization Test Environment," exploration of setting up a performance optimization environment.

In the context of the problems identified in this chapter, it's wise to execute changes in the following order:

1. Package status query correction Correcting the application to force entry of an area code when looking up packages by recipient fax number correctly employs the relevant index and eliminates the costly table scans currently plaguing users. This is a low-risk, high-reward alteration.

2. Roll up tables for business intelligence query users These users are wrecking performance at unpredictable intervals. Because you don't have the authority to lock them out of the system, it's a good idea to aggregate data for them in rollup tables. This is lower risk, and requires less work than the next step, replication.

3. Replicate information for business intelligence query users This is the ideal solution for the problem of end-user query writers, but it does require some work on your part (and introduces some minimal risk of "collateral damage") to implement.

4. Replicate the shipping_prices table to a dedicated server This change goes a long way toward reducing resource contention on the primary server. Just like its counterpart for business intelligence users, this type of activity comes with its own setup costs and risks. In this case, you must change application logic to point at the right server, which entails work as well as establishes some hazards if you miss a reference in your software.