SQL Server Mentalist

Hi friends

Today just off the track I m posting some Stored Procedure Optimization Tips

Following is Summery of Stored Procedure optimization

• Include SET NOCOUNT ON
• Use of sp_executesql instead of EXECUTE
• Naming Convention should be Proper
• Break Down Large Stored Procedure into Small
• Avoid Temporary Table
• Avoid HAVING clause
• User Profiler for Optimization
• Calling Always Stored Procedure with Fully Qualified Name
• Use of Proper Return Values
• More Use of Views
• Restrict Query Result with WHERE clause
• Use of UNION ALL
• Addition of WITH RECOMPILE
• Avoid Cursors
• Search Alternative for Time Consuming Query
• More Use of Variable
• Avoid DISTINCT clause
• Use of sp_executesql instead of temporary stored procedures

The reason for giving summery before explain the topic is saving time of my reader those who do not have time to research this stuff again

Lets study on by on see how in detail we can understand this

Include SET NOCOUNT ON

Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement. Also it can reduce network traffic, because client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

Use of sp_executesql instead of EXECUTE

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

Naming Convention should be Proper

Don’t use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend to use the prefix "sp_" in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified

Break Down Large Stored Procedure into Small

If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.The stored procedure will be recompiled when any structural changes were made to a table or view referenced by the stored procedure (for example, ALTER TABLE statement), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure.

Avoid Temporary Table

Try to avoid using temporary tables inside your stored procedure.
Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

Avoid HAVING clause

Try to avoid the HAVING clause, whenever possible.The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.

User Profiler for Optimization

Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
To check the stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

Calling Always Stored Procedure with Fully Qualified Name

Call stored procedure using its fully qualified name.The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

Use of Proper Return Values

Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.

More Use of Views

Use views and stored procedures instead of heavy-duty queries.This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.

Avoid DDL Statement

Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.

Restrict Query Result with WHERE clause

Try to restrict the queries result set by using the WHERE clause.This can results in good performance benefits, because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.Try to restrict the queries result set by returning only the particular columns from the table, not all table’s columns.This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table’s columns. This can reduce network traffic and boost the overall performance of the query.

Use of UNION ALL

Try to use UNION ALL statement instead of UNION, whenever possible.The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.

Addition of WITH RECOMPILE

Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.

Avoid Cursors

Try to avoid using SQL Server cursors, whenever possible.SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

Search Alternative for Time Consuming Query

If you need to return the total table’s row count, you can use alternative way instead of SELECT COUNT(*) statement.Because SELECT COUNT(*) statement make a full table scan to return the total table’s row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘table_name’) AND indid < 2 So, you can improve the speed of such queries in several times.

More Use of Variable

Use table variables instead of temporary tables.Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.

Use stored procedures instead of heavy-duty queries.

This can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.

Avoid DISTINCT clause

Try to avoid using the DISTINCT clause, whenever possible.Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.Use the FAST number_rows table hint if you need to quickly return ‘number_rows’ rows.You can quickly get the n rows and can work with them, when the query continues execution and produces its full result set.

Use of sp_executesql instead of temporary stored procedures

Use sp_executesql stored procedure instead of temporary stored procedures.
Microsoft recommends to use the temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures to have a better chance to reuse the execution plans.

Hope this helps !!

Hope you like my post on optimization and will be use this in your practice soon

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Dactylonomy of Web Resource >> Link Resource # 25: Sept 12–Sept 24