When I’m doing performance tuning on an application in the early stages of its lifecycle (or any app that’s never had DBA attention before), I end up with a ton of recommendations within the first day of performance tuning. The resulting to-do list can seem overwhelming to project managers and developers, so I include one of the following two sentences as a part of each recommendation:
- This change will improve performance by a percentage, or
- This change will improve performance by an order of magnitude
I know, I know, that phrase doesn’t come up too often, so it helps to check out Wikipedia’s definition of order of magnitude:
“Orders of magnitude are generally used to make very approximate comparisons. If two numbers differ by one order of magnitude, one is about ten times larger than the other.”
So the two sentences translate into:
- This change will improve performance by 10-90%, or
- This change will improve performance by 10-100x
I could use those latter two sentences instead of the “percentage versus order of magnitude” sentences, but those latter sentences make me sound like I’m taking wild, uneducated guesses. In reality, sure, I am taking wild, uneducated guesses, but on an informed basis – I’m just not putting a lot of time into categorizing the improvements.
Jeff Atwood’s excellent Coding Horror blog has a two-part post about estimation that should be required reading for every DBA. Part 1 is a quiz, and Part 2 explains the answers.
So why am I leaving so much gray area in my recommendations? Why break suggestions into such widely varied categories? Is it smart to lump a 10% improvement in with an 80% improvement? In the early stages of performance tuning, yes, because the DBA can’t necessarily predict which changes will be the easiest to implement, or the order in which they’ll be implemented. When a database administrator first looks at an application, queries, stored procedures and database schema, some things pop out right away as massive opportunities for performance gains. These recommendations are so instrumental to application performance that they often have wide-ranging impacts across the entire app. After those changes are made, everything speeds up so much that the other recommendations have even less of an impact than they might have originally had.
For example, in a project I’m currently tuning, I found that the three largest tables in a database (which had ten times more records than all of the remaining tables combined) were constantly queried by a single field, the equivalent of a DivisionID integer field. All of the queries hitting those tables included the DivisionID, and the application frequently did huge update statements that affected all records with a single DivisionID number. Partitioning those three tables by DivisionID and putting each DivisionID on its own set of disks would result in a staggering performance improvement and a tremendous increase in concurrent nightly ETL processing, since more divisions could run simultaneously.
I made other performance recommendations as well, but frankly, if the developers implemented every other recommendation except the partitioning, they would still have been struggling with their nightly windows, and the implementation time would have put the project way behind on their deadlines. On the other hand, if they just implemented the partitioning, they would sail through their nightly windows and make their project delivery deadline. That’s the definition of an “order of magnitude improvement.”
