Locking and Performance

In applications requiring high query throughput, high concurrency rates, and/or large result sets, you need to ensure good database design. However, systems with good database design can still suffer from performance problems. Before you spend money on hardware architecture improvements you should look at your approach to queries, and how queries and result sets are passed between applications and database systems. You can employ some useful techniques to reduce overhead and improve throughput.

You need to be aware of the behavior of concurrent transactions under your selected isolation level, in order to ensure query correctness. Careful selection of transaction isolation level can also produce significant performance changes.

You should also consider how result sets are transferred from database to application. By minimizing transfer sizes you promote effective queries. It also means that your application code has to deal only with the information it actually needs.