Database is the core part of any application. Application performance mainly depends on the database. Stored Procedures are main objects used to fetch data from database to application. Below is the checklist/best practices to improve SP performance, which will enhance overall application performance.
1. Keep database object name short ,meaning ful and easy to remember ( useful for easy maintenance).
2. Normalize data atleast upto 3rd form but not at the cost of query performance. Denormalization up to small extent can boost query performance.
(e.g. Suppose you have 3 columns ItemID, Quantity, Rate in a table (let us call it as ItemStock table). While generating report with total we need to multiply Quantity with Rate. Instead we can add a total column in the table and store the total over there.
3. Do not use those column in the select statement which are not required. Never user select * statement.
4. Use SP to fetch data and try to avoid program logic in SP. Logic has to be written at application level and not at the database level. Always use 'set-based approach' instead of a 'procedural approach' for accessing and manipulating data.
5. Use primary key in the table to filter the data in where clause. Use execution plan to analyze the query. Make sure your queries do an "Index seek" instead of an "Index scan" or a "Table scan." A table scan or an index scan costs more and takes more time for execution. Choose the right indexes on the right columns.
6. Use SET NOCOUNT ON at the beginning of your stored procedures, as this suppresses messages like '(1 row(s) affected)' after executing INSERT, UPDATE, DELETE and SELECT statements. This improves the performance of stored procedures by reducing network traffic.
7. Also avoid searching using not equals operators (<> and NOT) as they result in table and index scans.
8. Use table variables instead of temporary tables.
9. 'Derived tables' as they perform better than subqueries
Select Max(Quantity)
From ItemStock
Where ItemID in
(
Select
Top 2 ItemID
From ItemStock
Order By Quantity Desc
)
Same can be written using derived table
Select Max(Qunatity)
From
(
Select
Top 2 Quantity
From ItemStock
Order by Quantity Desc
) as A
Note that your results might differ in different scenarios depending on the database design, indexes, volume of data, etc. So, test all the possible ways a query could be written and go with the most efficient one.