From the Archives: Seven Deadly Sins of Database Application Performance

[JasonHinsperger]

In this post, originally written by Glenn Paulley and posted to sybase.com in February of 2011, Glenn introduces a list of application architecture components that, if poorly designed/handled, can have significant negative impact on the performance of the application.

Inevitably, at some point performance becomes an issue for many database applications. Performance analysis is often problematic simply because there are so many variables, which include the characteristics of the hardware, the workload, physical database design, and application design, and because these considerations have tradeoffs and side-effects - there are usually no right answers.

Some time ago SQL Anywhere consultant Breck Carter wrote an article entitled How to Make SQL Anywhere Slow, possibly one of my all-time favourite posts. Breck's article enumerates 38 different database design, application design, and server configuration settings that can lead to poor performance. In a forthcoming series of articles, which I've somewhat brashly called the Seven Deadly Sins of Database Application Performance, I'll write at length about seven specific issues that I believe are deserving of additional explanation.

The Seven Deadly Sins of Database Application Performance are:

1. Poor physical database design decisions: schema design issues, table column order, indexing, database page size.
2. Lock contention: due to hot rows or running at higher ANSI isolation levels.
3. Iterative, nested-iteration execution, including but not limited to the use of nested queries, user-defined functions, and client-side joins.
4. Performing more work than necessary in a query, either due to the amount of data accessed or retrieved, or to overly-complex queries that are difficult to optimize.
5. Inefficient client-server interactions, possibly involving prefetch settings, wide inserts and/or fetches, the use of prepared statements, and the re-fetching of the same information over and over from the server.
6. Choice of optimization goal for a SELECTstatement.
7. Choice of transaction model - particularly the use of auto COMMIT.