Blame the SQL.

Performance problems are often caused by poorly formed SQL statements.

Learning to use SQL properly takes practice. Unfortunately, people who do not fully understand SQL often create queries that are much slower than they need to be. Unfortunately, with the power of SQL, the speed difference between an optimized query and a poorly written query can be several orders of magnitude. In extreme cases, an inefficient query might take minutes, hours, or days to complete.

Because database queries result in a high number of I/O operations, both logical and physical, they can be the most time-consuming part of an application’s operation. If you think about it, you have a CPU that can perform over a billion operations a second, memory that operates at GHz speeds, and a bus that tosses over a GB of data around each second. And then you have a disk drive that is physically spinning around and moving a robotic arm to control magnetic fields. Compared to what the rest of the computer can do, waiting on disk I/O is like driving a Maserati into a pool of molasses.

Because of this, developers must optimize their database queries. For example, if a query is 1000 times slower than it should be, users will suffer the pain. On the other hand, if your CPU calculates the addition of two numbers 1000 times slower than it should, it will still finish in less than a millisecond; you won’t notice.

So when you find your application is performing slowly, the problem is almost always related to poorly written SQL statements.

Sometimes this is a result of improperly using an ORM. The ORM is a powerful tool, but it is easy to do things wrong and end up with N+1 queries when you meant to have one query. It’s also easy to end up with table scans. The ORM hides the details of the SQL from the user and thus hides the performance problem. So that’s why it is often the culprit when problems exist.

So that’s where you should look first.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: