Best Answer · By JanBask MS SQL Server Expert
Answered on Jan 18, 2020
Below are few steps which can be followed in the procedure to optimize the query:
1. To count number of rows use “select count(1) from the table_name” or “select count(Primary_key) from the table_name” avoid using “select count(*) from the table_name”
2. Always select limited number of column for which data is required avoid “select * from the table_name”
3. Always use filter where, having clauses to restrict the data as much/early as you can.
4. Join tables which are required in the query avoid unnecessary joins in tables
5. Use Exists and Not exists for sub- queries, it will get the data faster
6. Use of distinct / minus keyword should be minimized
7. Use table variables for short term and small data store while executing a procedure
8. Avoid using mix data types as this will increase conversion overhead. Use the same data types which are there in the table. Avoid using cast/ convert functions as these make slower execution of procedure
9. While declaring a variable uses the appropriate length of variable which is required, it can waste the memory/ truncate the string at the end.
10. Avoid using Cursors in SQL as the SQL Server optimizer works in a better way while executing set based dataset.
