2011-02-26Testing SQL Performance and Scalability

This chapter explains the much feared effect of queries running fast during development but slow in production.

Diverging optimizer statistics are the most common source of trouble. But that can easily mitigated by copying the production statistics into the test environment. This can even fool the Oracle database to believe tiny tables are huge.

The second difference is the hardware—obviously—but that’s also not what I’d like to highlight here. Still, it must be mentioned that your development environment can outperform a multi-million dollar production environment because of its centralized character. Large scale enterprise environments are often distributed so that latencies accumulate. If you like to read more about that, have a look at my “Latency: Security vs. Performance” article.

What I’d like to highlight here is scalability.

In that context, I am defining scalability as “The property of a system which indicates its ability to handle growing amounts of work in a graceful manner” (Wikipedia: http://en.wikipedia.org/wiki/Scalability).

The following sections demonstrates the effects of proper indexing for scalability and tries to answer the question if having a close look at indexing is worth the effort.