• The SQL syntax is checked for any errors.
• The best plan is selected to execute the SQL (choice to use clustered index, non-clustered etc.).
• Finally the SQL is executed.
As in one of the article Mr. Shivprasad koirala says that Stored Procedures Do Not increase performance. Performance of inline parameterized SQL is the same as that of Stored Procedures. They had also proved why Stored procedure to be used.
The above statement states that when you run a stored procedure for the first time it will go through all the above steps and the plan will be cached in-memory. So the next time when the stored procedure is executed it just takes the plan from the cache and executes the same. This increases performance as the first two steps are completely eliminated.
The above statement also says / implies that for inline queries all the above steps are repeated again and again which brings down the performance considerably.
Following things are used to increase performance of Stored Procedure.
1. Use SET NOCOUNT ON By default,- every time a stored procedure is executed, a message is sent from the server to the client indicating the number of rows that were affected by the stored procedure. By turning off this default behaviour, you can reduce network traffic between the server and the client.
2. Use fully qualified name while invoking stored procedures from application and inside SPs,- This allows SQL Server to access the stored procedures execution plan more directly, and in turn, speeding up the performance of the stored procedure.
3. Use this, EXEC dbo.yourProcedure Instead of EXEC yourProcedure,- While executing stored procedure append prefix dbo. to the name of sp.
4. Select only the required columns,- Usage of SELECT * can be the cause of NOT using the indexes available on the table. Also, selecting more data means more IO resources.
5. Minimize the use of not equal operations,<> or != SQL Server has to scan a table or index to find all values to see if they are not equal to the value given in the expression. Use this (example), SELECT Name, Description, OrderDate FROM Orders WHERE OrderDate < '2005-01-01' OR OrderDate > '2011-01-01'instead of SELECT Name, Description, OrderDate FROM Orders WHERE OrderDate <> '2011-01-11' .
6. Avoid Mixing-up DML and DDL statement on a temp table inside sp,- When you create a temp table (#table) and ALTER the same temptable in the same stored procedure later, it causes the stored procedure to get recompiled.
7. Avoid using DISTINCT when not required,- .Sort operation is performed for a distinct calculation, which is costly and hits performance.
8. Ensure columns with similar datatype are used when using JOINS,- For best performance, the columns used in joins should be of the same data types. And if possible, they should be numeric data types rather than character types.
9. Use EXISTS clause instead of Count(*),- for checking existence Use EXISTS instead of COUNT(*) when looking for the existence of one or more rows in a sub query. EXISTS cancels the sub query once the first existence of a record is found, while COUNT(*) forces processing of the entire query.
10. Remove PRINT statements from stored procedures,- Normally print statements are used for debugging the SPs. Print statements adds an additional overhead to performance because SQL server sends the output to client.
11. Do not use Dynamic SQL inside stored procedures,- Every query you run in SQL Server requires a query plan. When you run a query the first time, SQL Server builds a query plan for it . SQL Server saves the plan in cache, and next time you run the query, the plan is reused. While using dynamic SQL, query execution plan is not cached for the dynamic SQL and it will affect the performance badly.
12. Keep all Transactions short as possible,- This helps to reduce the number of locks (of all types), helping to speed up the overall performance of SQL Server.
13. Use SP_EXECUTESQL rather than EXEC(),- it has better performance and improved security Sp_executesql offers two major advantages over EXECUTE,
1. First, it supports parameter substitution, parameterised statements gives no risk to SQL injection and increases readability.
2. Second, it creates query execution plans that are more likely to be reused by SQL Server, which in turn reduces overhead on the server, boosting performance.
14. Always put the DECLARE statements in the starting of the stored procedure,- This enables query optimizer to reuse query plans and also increases readability.
15. Use UNION and UNION ALL properly based on requirement,- A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.
16. Do not use Cursors when they are not required,- Cursors of any kind slow SQL Server's performance. While in some cases they cannot be avoided, in many cases they can. If you need to perform row-by-row operations, consider using one or more of these options instead of using a cursor:
a) Use temp tables b) Use WHILE loops c) Use derived tables
d) Use correlated sub-queries e) Use CASE statements f) Use multiple queries
17. Don't do SELECT MAX(ID) from MasterTable when inserting in a details table,- This will fail when concurrent users are inserting data at the same instance. Use one of SCOPE_IDENTITY or IDENT_CURRENT.
SCOPE_IDENTITY would give you the identity value from the current context in perspective.
18. All stored procedures should contain the prescribed header. 19. Code that has been "commented out" should be explained or removed,- Before you are done with your stored procedure code, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were making changes, and remove them. Unused code just adds unnecessary bloat to your stored procedures.
20. Write SQL keyword in capital letters for better readability and ensure the code is formatted properly,a) SQL Keywords should start in a new line and all the keywords should be aligned together.
b) Is the code readable?
UPDATE dbo.Orders
SET OrderStatus = @ORDER_PENDING
WHERE OrdDate < '2001/10/25'
21. Do not use NON ANSI joins,- The use of the JOIN clause separates the relationship logic from the filter logic (the WHERE) and is thus cleaner and easier to understand.
ANSI SELECT * FROM a JOIN b ON a.id = b.id
Non ANSI SELECT * FROM a, b WHERE a.id = b.id
Conclusion :
I hope that this article would have helped you in understanding to increase performance of stored procedures. Please share your knowledge if you know more about this attribute. Your feedback and constructive contributions are always welcome.
No comments:
Post a Comment