Premature optimization - SQL Antipattern #008

Premature optimization is the root of all evil – Sir Tony Hoare (popularized by Donald Knuth) is a heavily discussed topic amongst engineers.  If no resource limits such as CPU, memory or IO are used for acceptance testing of software code Premature Optimization is viewed as an Antipattern because there is only cost but no benefit

This statement has been twisted by engineers to mean “There is no requirement to optimize”. Any DBA will tell you that optimization is essential, particularly when users and application owners start complaining.

The real meaning is that you should get the basics completed first , such as design, architecture and algorithms. Then focus on optimization.

From a practical DBA perspective the main issue with premature optimization is it is difficult to know , before any load is run, where bottlenecks exist. Another disadvantage of premature optimization is you may modify the code in such a way that it’s a complicated procedure to adjust later , particularly if other bottlenecks are identified.  One common feature of database management is the phenomena of increased data sets .

The initial data set sizes may have one set of optimizations , but then data grows , so other tactics may be required such as partitioining and other SQL Server scaling techniques . Designing and maintaining code sets with these principles in mind will allow greater flexibility .

The counter argument to all this theorising is that any absolute statement – is really the root of all evil. Every optimisation decision whether premature or later has a cost and benefit. We should be striving for balance at every point in the design process. Knowing that you can never have the absolutely perfect set of circumstances, and you need to focus on continuously improving the system.

The common method to avoid premature optimization and ensure the original design principles are maintained is summed up by:

1. Make it work.
2. Make it right
3. Make everything work.
4. Make everything right.
5. Use the system with some realistic data loads and find performance bottlenecks.
6. Use a profiler or other tracing facility in those bottlenecks to determine what needs to be optimized.
7. Make it fast. You can either re execute the unit tests or capture real workloads. Make whatever adjustments are necessary.Read more on SQL Server test data generation testing tools

Read more

Predictability is the key to scalability - SQL Server DBA

SQL Server – Optimize delete from table - delete in sql - SQL Server ...