Top 10 Database Performance Problems

1. Numerous database round-trips from middleware to perform single database
Solution: Do not make everything object-oriented. Put several actions in a single 'wrapper' stored procedure. Object-oriented design increases maintainability, but reduces performance.

2. Lack of indexes or improper indexes
Solution: If you are not comfortable, ask your development or production DBA for help. This even more critical for tables which will have many records or which have wide composite keys. Indexes improve performance of queries, but unfortunately reduce performance of transactions.

3. Joining many tables - Do you have queries that are joining 8, 12, 15 or even 20 tables? Layout with numerous normalized tables is optimized for updating of information but it has tragic implications on performance of querying (reading).
Solution: Is it possible and justified to denormalize tables? Is it possible to create summary that will be periodically updated?

4. Returning large resultset to the middleware
No user will browse through thousands of records. Typically they will browse through a few hundred records. We should not increase the database traffic this way.
Solution: If there is potential to return hundreds or thousands of records, break the resultset in batches (on the database side).

5. Use of ad-hock queries
You should not simply assemble your SQL statements in application (or middleware components).  That type of solution is slower and more difficult to maintain.
Solution: Stored procedures are compiled objects. Use them instead.

6. Stored procedure returns single value or record using resultset
Solution: Use return value or output parameters.

7. Joining tables in the middleware.
SQL is 4^th generation language specifically design for data manipulation and retrieval. By joining tables in the middleware, you are:

• wasting time to move unnecessary data (complete tables) from database server to application server
• middleware components are typically not optimized as good as relational engine to perform set operations
• increasing  memory requirements on application server
• decreasing performance of other components and applications on application server

Solution: Learn how to do inner join and outer joins in SQL statements. There are examples that processing time was reduced for one, two or three orders of magnitude (i.e. load process duration was reduced from 1.5h to 18s, query that was timing out after 30s was reduced to under 50ms).

8. Use of TSQL cursors
TSQL cursors are terribly expensive.
Solution: It is often possible to rewrite procedural algorithm into a set oriented algorithm (ask development or production DBA for help if needed). If it is not possible, use looping with Min/Max or temporary tables (again, ask your DBA or buy this book).

9. Criteria on optimizable (SARG-able) fields
It is important to write queries that will not scan complete table to get to a small subset of fields. Typical example would be a field that has small number of distinct values (such as a flag field that tracks was the record read or updated or not).
Solution: It is often possible to find alternative solution (read flag can be replaced with pointer that shows the last record that was processed.)

10. Large tables
Ask yourself: Can we do vertical or horizontal partitioning of tables? Is the table sparsely populated?
Solution: Horizontal partitioning - if you store multiple entities in the same table maybe it make sense to store each type (or group) of objects in a separate table. Vertical partitioning - if some fields of the table are frequently not used or not even populated, you could create two tables instead. The first table would store frequently used fields and the second the rest. 

11. Wrong data types
You can sometimes improve the performance of the system for order of magnitude if you use varchar instead of char or nvarchar; float or real instead of decimal, int for primary key instead of varchar or guid, etc.