Top 20 Proven Tips to Optimize Database Design and SQL Performance

1. Database Design

1. Optimize queries by avoiding full table scans; create indexes on columns used in where and order by.

2. Avoid null checks in where clauses; set default values to prevent index loss.

3. Indexes are ineffective when the indexed column has many duplicate values (e.g., gender).

4. Too many indexes degrade insert and update performance; keep index count ≤6 per table.

5. Minimize updates to indexed columns because they affect physical row order.

6. Prefer numeric fields over character fields for better comparison speed.

7. Use varchar / nvarchar instead of char / nchar to save space and improve search.

8. Prefer table variables over temporary tables; note limited indexing.

9. Reduce creation/deletion of temporary tables to save system resources.

10. Use temporary tables judiciously; for large repeated data sets they can be helpful.

11. For bulk inserts, use select into instead of create table + insert to lower logging.

12. Explicitly drop temporary tables at the end of stored procedures using truncate table then drop table.

2. SQL Statements

1. Avoid != or <> in where clauses; they prevent index usage.

2. Avoid or in where; rewrite as UNION ALL to keep indexes.

3. Prefer between over in for continuous numeric ranges.

4. Patterns like like '%abc%' cause full scans.

5. Parameterized where clauses can lead to scans; force index usage with with(index(IndexName)).

6. Do not apply arithmetic expressions to indexed columns in where; rewrite calculations to the constant side.

7. Avoid functions on indexed columns (e.g., substring, datediff); use range conditions instead.

8. Do not place expressions on the left side of “=”.

9. Replace empty‑result queries like select col1,col2 into #t from t where 1=0 with proper create table.

10. Prefer exists over in for subqueries.

11. Never use select *; list needed columns explicitly.

12. Avoid cursors for large data sets (>10,000 rows).

13. Limit large result sets sent to clients; assess necessity.

14. Reduce large transactions to improve concurrency.

3. Java Practices

1. Minimize object creation.

2. Separate bulk data operations from small ones; bulk work should not rely on ORM.

3. Use JDBC for direct database access.

4. Stream data instead of loading everything into memory.

5. Cache data wisely when appropriate.

4. Database Performance Optimization

1. Hardware: increase disk/network throughput, expand virtual memory, disable unnecessary services, separate DB server from domain controller, maximize SQL server throughput, and use multi‑processor machines.

2. Indexing: create indexes on frequently queried columns, use a single clustered index on integer keys, add non‑clustered indexes to cover queries, but keep index count reasonable to avoid overhead on DML.

3. Stored procedures: (note that some guidelines forbid them) can encapsulate logic, reduce network traffic, and improve modularity.

4. Application design: proper indexing, query rewriting, and algorithm choices dramatically affect performance, especially for large‑scale OLTP or DSS workloads.