The following ideas, actions and hints contribute to increased SQL query performance and concurrency:
-
Run UPDATE STATISTICS and UPDATE INDEX STATISTICS regularly against the database.
-
Tune the database indexing periodically to ensure it is aligned with the applications' requirements.
-
Remove unused indexes or add new ones to meet the applications' requirements.
-
Know the data and the number of records in each table to help write the query in its optimum form.
-
Write various forms of the query and examine the Query Plan of each variation of the query.
-
Try to get the query to start its data scanning from the smallest to the largest tables.
-
Use the query that yields optimum performance during testing in production.
-
Use statement caching where possible
-
Use dedicated SQL brokers to handle SQL queries.
-
Use connection pooling.
-
Use the READ UNCOMMITTED transaction isolation level where possible.
-
Use optimistic locking strategy. That is, do not lock any record before its time.
-
Make the SQL transactions as short as possible to improve both performance and concurrency.
