Poorly designed database queries are the leading cause of performance problems.
From time to time, you will come across an application that appears to operate slower than expected. The cause of the slowness may come from many different sources. The server may be CPU-bound. The network bandwidth may be tapped out. The log server may have slow disk drives. It can be a lot of things. Computers are really fast and powerful, and networks are high-speed, so most of the time, a performance problem comes down to the database.
Poorly written SQL can cause a lot of problems. For example, a simple query to fetch a few rows of data can turn into a table scan if the query does not use an index. In a large table, the difference in performance can be dramatic. Imagine in one case the index is looked up and the database server returns a few rows, while in the other case the database server has to look at every row in a 100-million row table to determine if it matches and it should return them. Table scans are brutal when running on large tables.
As bad as table scans are, cartesian joins can be even worse. In that scenario, for every row in a table scan, the server may have to run another table scan on another table. In those cases, with large tables, the query may take so long that the database may appear to be hung. Obviously, one should try to avoid these problems as much as possible.
One thing to do is to avoid running SQL queries inside a loop. When you do this, you’re basically performing the equivalent of a cartesian join. You need to rewrite the query so that instead of a loop calling a query thousands of times, the query should only run once and return all of the correct data.
Be careful when joining tables via an ORM. You may get N+1 queries if done incorrectly. Using a query builder correctly takes practice, and you should review the generated SQL to ensure that it’s not missing indexes and sending your performance down the drain.
Always keep a sharp eye on queries that work with the database.
Today's Card 