Seven methods for SQL Server to optimize stored procedures

There are many methods to optimize the stored procedure. The seven most commonly used methods are described below.

1. Use the set nocount on Option

When we use the SELECT statement, in addition to returning the corresponding result set, the corresponding number of affected rows will also be returned. After set nocount on is used, no additional information is returned except for the dataset, reducing network traffic.

2. Use the specified schema.

When using tables, stored procedures, functions, and so on, it is best to add a definite schema. In this way, SQL Server can directly find the target to avoid searching in the Plan cache. In addition, searching will cause compilation locks and ultimately affect performance. For example, select * From DBO. testtable is better than select * From testtable. From testtable will be searched under the current schema. If not, it will be searched under DBO, affecting performance. If your table is csdn. testtable, select * From testtable will directly report the error where the table cannot be found. Therefore, writing a specific schema is also a good habit.

3. Do not start a custom stored procedure with SP _

Because the stored procedures starting with SP _ are system stored procedures by default, they are first searched in the master database and then in the current database. We recommend that you start with USP _ or another identifier.

4. Use sp_executesql instead of Exec

The reason is detailed in chapter 4 dynamic SQL in the inside Microsoft SQL Server 2005 T-SQL programming book. Here is a brief description: sp_executesql can use parameterization to reuse execution plans. Exec is a pure SQL statement.

5. less use of cursors

For details, see Chapter 3 cursors in the T-SQL programming book inside Microsoft SQL Server 2005. In general, SQL is a set language with High Performance for set operations, while cursors is a process operation. For example, if you want to query 1 million rows of data, the cursor needs to read the table 1 million times, instead of using the cursor only needs to read a few times.

6. The shorter the transaction, the better.

SQL Server supports concurrent operations. If the transaction is too long or the isolation level is too high, it will cause blocking and deadlock of concurrent operations. The query speed is extremely slow, and the CPU usage is extremely low.

7. Use try-catch to handle error exceptions

SQL Server 2005 and later versions support try-catch. Syntax:

Begin try
---- Your code
End try
Begin catch
-- Error dispose
End catch

Generally, try-catch can be used together with transactions.

Begin try
Begin tran
-- Select
-- Update
-- Delete
--............
Commit
End try
Begin catch
-- If error
Rollback
End catch