euse’s Blog

Tips for the optimization of Stored Procedure:

• 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.
  This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

• 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.

• 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.

   

• 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. So, when you have the stored procedure with the prefix “sp_” in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

   

• 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.

   

• 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.

   

• 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. So, if you break down a very large stored procedure into several sub-procedures, you get chance that only a single sub-procedure will be recompiled, but other sub-procedures will not.

• If possible, avoid using SQL Server cursors.                                     They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.  Here are some alternatives to using a cursor:
  • Use WHILE LOOPS
  • Use temp tables
  • Use derived tables
  • Use correlated sub-queries
  • Use the CASE statement
  • Perform multiple queries                                                                 

There are several reasons that I use temporary tables: to hold the results of a called stored procedure, to reduce the number of rows for joins, to aggregate data from different sources, or to replace cursors.

When generate Dynamic SQL that contains a large number of UNION ALL and queries generated is long, the execute generate Stack Overflow.

select pedido_id , cliente_id

from (select 1 as pedido_id union all select 2 as pedido_id union all select 3 as pedido_id union all select 4 as pedido_id … union all select 15751 as pedido_id)

Rewrite the query and use a #temp table to contain the values in the UNION ALL:

create table #tempTable

(

pedido_id int

)

insert into #tempTable

select 1 as pedido_id union all select 2 as pedido_id union all select 3 as pedido_id union all select 4 as pedido_id … union all select 15751 as pedido_id

Thus east mitigate this issue.

Other reason for that generate Stack Overflow, see:

• Install SQL Server 2000 SP4, this SP contains:
  • FIX: Query on the sysmembers virtual table may fail with a stack overflow
  • FIX: Merge replication reconciler stack overflow

http://support.microsoft.com/kb/888799/en-us

Download!

If your queries that contain a large number of arguments (thousands) inside an IN or a NOT IN clause:

http://support.microsoft.com/kb/288095/en-us