There are different ways of doing optimization
- Change the logic to get the output faster
- 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