In the recent times, I got a chance to work on improving the slow performance stored procedure, which is used to generate the data for the reports in ASP.NET Web forms project.
Based on the experience and the recent queries on improving the performance of the stored procedures, I thought to compile all the guidelines at one place, which pushed me to write this post.
The guidelines given below will help to improve the performance of the stored procedures.
- The simplest guidance is do not create the stored procedure with prefix "sp_". I prefer to create the procedures with a convention like <projectabbreviation_spProcedureName>.
- Include the SET NOCOUNT ON statement as the first statement of the procedure.
- Do not write "Select count(*) from Table" statement to get the count of the records. Alternatively, use "Select count (PrimaryKeyColumn) from Table".
- Try to avoid dynamic SQL queries as much as possible.
- Prefer to have the table variables instead of temp tables. Keep minimal use of temp tables.
- Avoid the use of the cursors to loop through the records. Instead keep the records in the table variable or temp tables.
- Use schema name with an object name.
- Prefer table joins over the use of subqueries in the where conditions.
- Transfer the relevant records from the primary tables to the table variable and do further processing with the table variable, thereby disconnecting the primary tables reference.
- Try to avoid using NOT LIKE operator.
- Create Non-clustered indexes, wherever it is necessary.
- Use the sp_executesql stored procedure instead of the EXECUTE statement.
- Create views for repeatedly used select queries.
- Keep only the required columns as part of the final SELECT query instead of saying "SELECT * FROM..".
- Instead of keeping a very large stored procedure, try to break it into smaller sub-procedures, wherever possible.
- When you fetch the records from the stored procedure and display in the grid format on the front-end, prefer to have custom paging logic when the underlying results set contains many records.
I hope, this helps in resolving the performance issues associated with the stored procedures.