When Optimization Breaks Software

LightningIt’s well known that most of the code we write is not what the computer actually runs. When writing a program, the compiler or runtime will take the code and perform a process called “optimization,” where various methods are applied to make the code more efficient so that it will execute faster.

Optimization is one of the Holy Grails of software development. It enables beginner programmers to write software that will often run as efficiently, for most simple applications, as that written by professionals. For instance, if a constant is declared and used in a loop, the compiler can recognize that and move it out of the loop, saving significant time while running the program.

Of course, there is no free lunch, and optimization can also bring up as many problems as it fixes. In the simplest case, it can create lazy programmers who don’t learn the proper way to code. As the software gets more complex, poor programmers will create problems that the optimizer can’t fix, and the more difficult problems will become increasingly difficult to solve than if they had learned the proper foundation from the beginning.

Learning the fundamentals of software development, however, is a long and arduous process. For good code efficiency, there is no better teacher than programming in assembler. By manipulating the lowest-level processor instructions, the developer will learn how the computer thinks, and how their own code is translated into action. These priceless lessons will translate into good coding throughout any language.

However, there are also times when the optimizer causes unexpected bugs. One example is in SQL Server software development, where the optimizer runs on every query that is executed. One interesting caveat is that the optimizer actually unrolls every view on every call, and creates a new query-path from scratch. This can lead to the situation, where the view might execute properly for the majority of instances, however when the fifth or sixth table is joined, the query optimizer could take a new path and create errors or unexpected results.

For example, let’s take the following vertical table SQL statement:

select cast(value as int) from foo where property='age'

Normally, this will execute properly. Even joining with several tables would produce proper results. However, as the query size increases and the number of tables outgrows the capability for efficient optimization, SQL Server can take a wrong turn and produce a type conversion error when it tries to cast other value rows to int besides the age rows.

As we put our faith in optimizers, we also lose control over the actual execution of the code. Although the optimizers help beginner programmers to write efficient code, they can sometimes cause unexpected bugs and errors in more advanced software projects.

Written by Andrew Palczewski

About the Author
Andrew Palczewski is CEO of apHarmony, a Chicago software development company. He holds a Master's degree in Computer Engineering from the University of Illinois at Urbana-Champaign and has over ten years' experience in managing development of software projects.

RSS Twitter LinkedIn Facebook Email

One thought on “When Optimization Breaks Software”

Leave a Reply

Your email address will not be published. Required fields are marked *