Blame the SQL.

Performance problems are often caused by poorly formed SQL statements.

Learning to use SQL properly takes practice. People who don’t 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 bad query can be several orders of magnitude. In extreme cases it might take minutes, hours, or days for a bad query 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 a GHz speeds, and a bus that toss over a GB of data around each second. And then you have a disk drive that 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, database queries must be optimized. If a query is 1000 times slower than it should be, you definitely feel the affect. 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:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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: