提高性能——存储过程最佳实践

原文地址：http://msdn.microsoft.com/en-us/library/ms187926.aspx 

 

• Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. That is, place it just after the AS keyword. This turns off messages that SQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. Overall performance of the database and application is improved by eliminating this unnecessary network overhead. For information, see SET NOCOUNT (Transact-SQL).

使用NOCOUNT开关提高性能。在CREATE PROCEDURE <procedure_name> AS 后加入“SET NOCOUNT ON” 语句可以减少发送额外的信息，从而减少网络传输开销。具体说来这个开关会让SQL SERVER在把SELECT, INSERT, UPDATE, MERGE, and DELETE 等语句的执行结果返回给客户端后，停止发送额外的消息。

• Use schema names when creating or referencing database objects in the procedure. It will take less processing time for the Database Engine to resolve object names if it does not have to search multiple schemas. It will also prevent permission and access problems caused by a user’s default schema being assigned when objects are created without specifying the schema. For more information, see User-Schema Separation.

当引用一个数据库对象时，指明schema name，这样可以免去DB engine解析该数据库对象时查找schema的时间。举例说，就是使用myDatabase.dbo.myTable, 而不是myDatabase..myTable （这里dbo可以按需替换成其他的schema)。指明schema的另一个好处是，当创建新的数据库对象时，不会把用户自身的schema作为默认schema赋给新对象 （很多时候，用户如果没意识到的话，可能会带来麻烦）。

• Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

不要对WHERE或者JOIN语句里的column使用函数，因为这样会使查询处理器无法使用索引来提高性能。

• Avoid using scalar functions in SELECT statements that return many rows of data. Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.

当查询返回大量结果时，避免在SELECT语句里使用标量函数 (返回单值的函数)，如MID(), LEN(), ROUND(), FORMAT()等。因为这些函数会在每一行返回的结果上执行，因此会很大程度上影响性能。

• Avoid the use of SELECT *. Instead, specify the required column names. This can prevent some Database Engine errors that stop procedure execution. For example, a SELECT * statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table will succeed until the number or order of columns in either table is changed.

避免使用SELECT *, 指明每一个需要返回的列的名字。因为如果使用SELECT*的话，在语句执行过程中，列的个数或顺序发生任何改变的话，语句执行就会出错停止。

• Avoid processing or returning too much data. Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. Also, sending just the essential data to the client application is more efficient than sending extra data across the network and forcing the client application to work through unnecessarily large result sets.

避免处理和返回过多的数据。尽量使后续操作在尽可能小的结果集上进行。同时，返回尽量小的结果集给客户端也可以大大的减少网路传输开销。

• Use explicit transactions by using BEGIN/END TRANSACTION and keep transactions as short as possible. Longer transactions mean longer record locking and a greater potential for deadlocking. For more information, see Locking and Row Versioning, Lock Compatibility (Database Engine), or Isolation Levels in the Database Engine.

使用显式事务（ 用BEGIN/END TRANSACTION声明的事务， 另参见 Implicit Transactions），并保证事务尽可能的短，以避免死锁。

• Avoid using a wildcard as the leading character in a LIKE clause, for example, LIKE ‘%a%’. Because the first character is non-deterministic, the query processor is unable to use available indexes. Use LIKE ‘a%’ instead.

不要在LIKE语句里使用通配符%开始的字串。因为如果第一个字符不确定的话，查询引擎就没法使用索引。

• Use the Transact-SQL TRY…CATCH feature for error handling inside a procedure. TRY…CATCH can encapsulate an entire block of Transact-SQL statements. This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming. For more information, see Using TRY...CATCH in Transact-SQL.

使用TRY...CATCH封装代码段来处理错误，一来可以减少performance损失，二来可以使错误报告更精确，并且相比其他方式，这种做法需要的代码量少得多。

• Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQL statements in the body of the procedure. This will prevent passing NULL to columns that do not allow null values.

当创建或更改一个表时，在所有用到的column上应用DEFAULT关键字。因为如果有默认值的话，可以避免给非空的column传递空值。

• Use NULL or NOT NULL for each column in a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Database Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.

为所有临时表里的列指明空或非空。因为，如果不指明的话，空或非空是根据ANSI_DFLT_ON和ANSI_DFLT_OFF这两个开关来制定的，而不同的连接可能有不同的上下文，所以可能出现同一个存储过程在不同的连接下，其临时表的列非空约束不一致。 (关于临时表可参见 这里 ，temporary table部分)

• Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. Be aware that in Transact-SQL, NULL is not an empty or “nothing” value. It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions. For more information, see NULL Comparison Search Conditions and Null Values.

尽量将结果里的空值转换为有意义的值。因为在T-SQL里，空值并不代表空或没有，它代表的是unknow (当跟它做比较时返回的既不是true，也不是false)，所以可能带来一些不可预期的行为，尤其当使用聚集函数时。

• Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

如果能使用UNION ALL操作就不要用UNION或者OR，因为UNION ALL可以自动剔除重复的结果，从而减少不必要的处理负担。