If you have deployed a few systems of scale, you know that some design problems are worse than others. It’s one thing to write tight code, and another thing to avoid introducing performance-crushing design flaws into the system.
Here are nine common problems – poor design choices, really – that will cause your system to spin its wheels, or even turn against itself. Unlike many bad decisions, these can be reversed.
1. N+1 queries
If you select all of a customer’s orders in one query then loop through selecting each order’s line items in a query per order, that’s n trips to the database plus one. One big query with an outer join would be more efficient. If you need to pull back fewer at a time you can use a form of paging. Developers using caches that fill themselves often write n+1 problems by accident. You can find these situations with database monitoring tools such as Oracle Enterprise Monitor (OEM) or APM tools such as Wily Introscope or just plain query logging. There are worse versions of this problem such as people who try and crawl a tree stored in flat tables instead of using CTEs. There are also equivalent versions of these problems in NoSQL databases, so no one is safe.