Testing 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.

Faster, easier and more memorable than reading.

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.

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
277
views

Updating multiple rows using a subquery in SQL

Jan 08 at 09:52 Jan 26
subquery update sql
1
vote
1
answer
210
views

Should 'id' (the primary key) be included in an index

Jan 03 at 15:24 Jan 26
index include
0
votes
1
answer
227
views

Best index for a multiple join-tables and filter

Jan 03 at 14:31 Markus Winand ♦♦ 216
index join where