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 :
    =, >, >=,
  • Do not use arithmetic operators directly on the “column name” in the “where” clause. Instead use logical workarounds to get performance benefits. eg:
    instead of
    WHERE HourlyRate * 8 <= 100

    use

    WHERE HourlyRate <= 100/8
  • Do not use functions on columns in the “where” clause. eg:
    a. Instead of
    WHERE DATEPART(YYYY,CreateDate)='2016'

    use

    WHERE CreateDate >= '01/01/2016' AND CreateDate <= '12/31/2016'
  • b. Instead of

    WHERE Left(LastName,1)='A'

    use

    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.

    You may also like

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.