Tips to write better SQL

There are different ways of doing optimization

  1. Change the logic to get the output faster
  2. Write or re-write the code in better way.

Let me start with the second point first i.e. “Write or re-write the code in better way”.
There are many tricks to improve the performance of application but it purely depends on business logic of the application.

  • Make sure the dataset (table) you use for querying is always smaller in size.The reason behind this is, smaller the table size lesser the physical I/O operation.
  • Create INDEX when it is required. Because index creation itself will take time. So before you create an index, compare the time difference.
  • Use the index properly. Eg; If you create index on col1, col2, col3. Try to use all the three columns in the WHERE clause in the same order.
  • Don’t use User defined functions (UDF) in the select query.
  • Have the practice of checking the execution plan of the any query you write.
  • If the data in the table is unique then don’t issue the DISTINCT as this will reduce the sorting operation. “Still It depends on your business need”
  • Joining the same table multiple times may become worse and that depends on the table volume
  • When you create index use WITH SORT_IN_TEMPDB
You can find more SQL Server topics in this blog. continue reading…

2 comments:

  1. Very Informative!! Thanks for sharing..

    ReplyDelete
  2. Can u please share more about WITH SORT_IN_TEMPDB during index creation.

    ReplyDelete