How to Make SQL Fast

SQL is the backbone of many applications, and its performance can make or break an application. Many developers who are not experienced with sql treat it like other procedural languages (see SQL is different for more info), and do not write sql code that performs well.

The fundamental aspect in sql performance is that sql performs operations on sets of data, and the query must allow sql to quickly identify the subset of data that will be modified. For example, in poor code, we have seen developers update each record one by one in a large loop. While that may be an acceptable algorithm in C++ or Java, it is not good sql. How can you tell if your sql is working well? See if it has the following characteristics:

• It scales well with data. If you double the amount of data, the performance should not become twice as slow. If the db has the appropriate indexes, etc, then it should be efficient, and will scale much better than 1:1 between data size and speed.
• Transaction times will be low. If any given transaction (a single atomic query/update/delete/etc in the db) takes an abnormally long time, then the db may suffer from congestion when under load.
• It scales well with load. If the load (number of transactions in a unit of time) doubles, the individual time to complete an operation should not also double. This issue often shows up in a stress test (your devs are running stress tests, right?). Often we will ramp up the number of simulated users using an application. We might increase them in orders of magnitude to see when the db will reach its limit. If we go from 1000 users to 2000 users, we would normally expect the performance to decrease by a 10-20% and not by 50%. If the performance did decrease by 50% then we would find out what part of the load wasn't scaling well and fix it.
• Deadlocks occur rarely. A deadlock is when two separate transactions in sql both "own" a piece of data, and are trying to access the other transaction's piece of data. They both can't succeed, so sql chooses one to die and lets the other acquire the second piece of data. Depending upon the application, it may be impossible to eliminate deadlocks, but in most cases, deadlocks occur because a transaction is slow, and holds locks (a reference to a piece of data) for too long so that it collides with other transactions. If all transactions complete quickly, then the chances for a deadlock are reduced.
• You don't have too many indexes. Since indexes improve sql's ability to find data, it is very tempting to add a new index to improve every performance issue. But indexes are not free, and every index increases the time to make an update or delete. If a table has dozens of indexes, that suggests that the developer is just adding in a new index (perhaps suggested by some automated tool) without looking holistically at the table. An index can serve more than a single query, at least if it is designed well. Very often indexes can be combined and reduce the overhead for update and delete operations.
• Performance is routinely measured. There are many tools to measure the performance of a database, and your developers should be doing that. If your developers have no idea what queries take a long time, or what execution plans (the way that sql will run a query) are being used, then they are not systematically measuring performance. The performance of the db must be routinely measured, and any anomalies corrected.