1. 增加 SET NOCOUNT ON 语句,较少不必要的还回数据件数:
CREATE
PROC dbo.ProcName
AS
SET
NOCOUNT
ON
;
--Procedure code here
SELECT
column1
FROM
dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET
NOCOUNT
OFF
;
GO
2.用 [数据库名]dbo.[表] 代替 表。 DBServer查找数据表的时间
SELECT
*
FROM
dbo.MyTable
-- Preferred method
-- Instead of
SELECT
*
FROM
MyTable
-- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC
dbo.MyProc
-- Preferred method
--Instead of
EXEC
MyProc
-- Avoid this method
3. 在Procedure Name不使用 ”sp_”, 以sp_开头的StoredProcedure通常
会master database 中去寻找。加重代码执行。
4.多用IF EXISTS(SELECT * FROM TableA), 而不是直接用SELECT * FROM TableA
极大的加快代码执行效率
IF EXISTS (
SELECT
1
FROM
sysobjects
WHERE
name
=
'MyTable'
AND
type =
'U'
)
5.尽可能用sp_executesql Procedure,而不是用单纯的Execute语句(尤其是复杂执行语句)
Execute 示例
DECLARE
@Query
VARCHAR
(100)
DECLARE
@Age
INT
SET
@Age = 25
SET
@Query =
'SELECT * FROM dbo.tblPerson WHERE Age = '
+
CONVERT
(
VARCHAR
(3),@Age)
EXEC
(@Query)
sp_executesql Procedure示例
DECLARE
@Query NVARCHAR(100)
SET
@Query = N
'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE
sp_executesql @Query, N
'@Age int'
, @Age = 25
6.尽可能避免使用Cursor(占用较多的资源)
7.尽可能保持Transaction 越短越好
8.多用 TRY-Catch 进行错误判断
BEGIN
TRY
--Your t-sql code goes here
END
TRY
BEGIN
CATCH
--Your error handling code goes here
END
CATCH
详细请参考
http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/