Past week I have been in discussions with different people over performance bottlenecks in database layer. These discussions were more related to common mistakes that are made in the database layer which cause performance issues when load increases. So I thought it would be a good idea to share the discussion here as well.
I have worked on database performance issues a lot. Here is my list of most common problems I encountered:
- No proper Connection Pooling.
Some people argue that databases have become so advanced these days that getting a connection is not much of a bottle neck but that is not true. Connection Pooling has several other advantages as well which I will be discussing later.
- Improper use of Statements and Prepared Statements.
Many a times I have seen code using prepared statements where statements should have been used and vice versa. Also keep in mind that prepared statements are cached per connection so having a connection pool is very necessary.
- Poorly written queries especially the where clauses.
Make sure you have the most restricting criteria first in the where clause. We have been using MS SQL server in our product line and although their documents suggest that this is not necessary as the plan will be optimized accordingly but I have seen many a times that just changing the order of conditions in the where clause can make huge difference. Selecting unnecessary data, unnecessary joins are also one of the most common issues.
- Not using Indexes.
The reasoning behind this is usually that this makes writes slow. This is somewhat true but there are certain conditions for it to be true (most of the applications usually don’t end up meeting that case)
- Improper usage of Transaction Isolation Levels or mostly the defaults are applied as is.
I have rarely seen anyone having a look at this side.
- Long Transactions.
This is also one of the most common case. Keep the transactions as short as possible and group similar things in a transaction instead of having every other database operation in a single transaction. This is easier said than done as proper care should be taken in demarcating the Transaction boundaries.
- No Caching.
This is self explanatory.
- Denormalizing the database schema w/o any strong reasons for doing so.
I would take this is as a premature optimization that causes more issues than it resolves.