Tips to increase database performance
Here are some of the points that you should consider when you write queries:
Limit number of rows and columns
Use “Search argument able” conditions in “where” clause. They help query optimizers to use the index defined on column(s) effectively and have a higher chance of meeting index seek than index or table scan.
“Search argument able” operators are :
=, >, >=, <, <=, BETWEEN, LIKE (only those LIKE conditions that have a wildcard character as a suffix, eg: FirstName LIKE 'A%'
Do not use arithmetic operators directly on the “column name” in the “where” clause. Instead use logical workarounds to get performance benefits. eg:
WHERE HourlyRate * 8 <= 100
WHERE HourlyRate <= 100/8
Do not use functions on columns in the “where” clause. eg:
a. Instead of
WHERE CreateDate >= '01/01/2016' AND CreateDate <= '12/31/2016'
b. Instead of
WHERE LastName LIKE 'A%'
Use proper primary and Foreign Key constraints. They help the query optimizer select the best-suited execution plan for the query.
Here are some of the points that you should consider when designing indexes.
Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.
Try to create indexes on columns that have integer values rather than character values.
If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.