存储过程编写与优化

存储过程编写与优化_数据库开发论坛 文章来源：IT人才网(http://www.ad0.cn)

编写存储过程有如下好处：

1.  使用存储过程可以对所执行的SQL语句进行封装，在接口保持不变的情况下不影响调用程序。

2.  最大限度的重用已缓存的执行计划。

3.  减少网络流量。

4.  提供更好的数据库安全控制，防止直接对表的操作。

存储过程的编译占用CPU，因此我们应该防止存储过程不必要的重新编译。


防止存储过程不必要的重新编译，达到优化存储过程的 IT人才网(http://it.ad0.cn) 目的。
存储过程编写经验和优化措施

存储过程正常的编译发生于：

1.  所引用的表中大部分的数据发生了的更改，导致统计信息变化过大。

2.  所引用的表的架构被修改，包括添加或取消约束、默认值或规则。www.ad0.cn

3.  明确使用WITH RECOMPILE强制每次执行过程时重新编译或sp_recompile使用过程缓存无效。

4.  由于服务器内存不足或长期不使用，使缓存过程被清除。


在以下情况下编写的存储过程会被不必要的重新编译：

1.    在调用过程时，不指定架构所有者。
这时为了找到正确的缓存计划，SQLServer会按照如下顺序查找过程所属的架构：
①sys
②调用此过程所属于的架构，如果是被其它过程所包含，则首先查询包含过程的架构
③dbo
为了能重新编译此过程，必须要对过程施加编译锁，因此在很多用户并行访问时可能会带来额外的等待时间。可通过sys.dm_exec_requests动态视图或master.dbo.sysprocesses系统表进行观察，如果lastwaittypee出现LCK_M_X，则表示出现了编译锁。

2.    存储过程在临时表上执行了特定操作。
在存储过程中经常会用到临时表与表变量，一直有种误解是表变量只会存储于内存中。其实如果两者都足够小的话，是不会保存于磁盘中的，但是两者的架构是都会存在于tempdb定义中的。只有在内存不足时，才会把数据存储于磁盘中。以下示例可查看表变量也是存储于tempdb中：
复制内容到剪贴板代码:
DECLARE @employee TABLE(employeeId INT);

INSERT INTO @employee VALUES(1);

SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME LIKE '%#%';
这时会看到TABLE_NAME有一#开头的记录

选择这两者的主要依据如下：
①对于小数据量的中间表优先使用表变量，反之如果数据量大且被用于连接，则使用临时表。因为在表变量中只能定义主键和约束，如果在进行联结时，必须在要联结的字段上建立索引，从而防止出现哈稀联结而占用过多的资源。联结的三种算法嵌套循环、合并与哈稀所占用的资源依次增加，而前两者的前提是在联结的字段上存在索引时，根据数据量多少而决定的。

②中间表对事务的影响，临时表的回滚会影响上层事务，而表变量不会。

③可见性问题。临时表对下次均可见，而表变量只在所声明的层次可见。

另一个重要的选择依据就是在对临时表会创建相应的统计信息，因此在过程中引用临时表是可能会迫使语句因为引用的表的统计变化而被重新编译多次。以下示例演示了此过程，为了能看到被重新编译的事件，请在跟踪事件中选择SP:Starting、SP:StmtStarting、SP:Recompile 和 SP:Completed，注意SP:StmtStarting与SP:StmtCompleted 事件，最好不要同时包含这两个事件，因为这样会将需要查询的信息量加倍。
复制内容到剪贴板代码:
USE AdventureWorks;

GO

CREATE PROCEDURE GetCustomerOrder

AS

CREATE TABLE #t (SalesOrderID int, CustomerID int)

SELECT * FROM #t

INSERT #t

SELECT SalesOrderID, CustomerID

FROM Sales.SalesOrderHeader

SELECT COUNT(*) FROM #t

WHERE CustomerID = 40

GO

EXEC GetCustomerOrder
通过上面的结果我们看到，每次在对临时表进行操作时，都会引起一次过程的重新编译。在查询CustomerID = 40时，可以看到有一SELECT语句正是为了能获得所需的统计信息而发生的。可以使用sp_executesql

来防止这种额外的编译，修改过程如下，再次跟踪事件：
复制内容到剪贴板代码:
USE AdventureWorks;

GO

ALTER PROCEDURE GetCustomerOrder

AS

CREATE TABLE #t (SalesOrderID int, CustomerID int)

SELECT * FROM #t

EXEC sp_executesql N'

INSERT #t

SELECT SalesOrderID, CustomerID

FROM Sales.SalesOrderHeader'

EXEC sp_executesql N'SELECT COUNT(*) FROM #t WHERE CustomerID = @CustomerID',

                   N'@CustomerID int', @CustomerID = 40

GO

EXEC GetCustomerOrder
此时我们发现只要是使用sp_executesql执行的语句都没有引起语句的重新编译。虽然此处演示的是使用临时表，但对永久表而言同样也存在这种问题。另一种做法是使用KEEP PLAN，不推荐使用。因此，我们应该优先使用sp_executesql，使用它所执行的语句在缓存中只会存在一条语句，而使用EXEC会根据具体的参数为每条不同的语句生成一个缓存计划。从而占用过多的缓存。

3.   过程定义中出现DDL和DML的交错。以下示例演示引起重新编译：
复制内容到剪贴板代码:
USE AdventureWorks;

GO

CREATE PROCEDURE GetOrderInfo

AS

-- DDL

CREATE TABLE #CompleteOrder(SalesOrderID int, CustomerID int)

-- DML

SELECT * FROM #CompleteOrder

-- DDL

CREATE INDEX idx_#CompleteOrder ON #CompleteOrder(SalesOrderID)

-- DML

SELECT * FROM #CompleteOrder

-- DDL

CREATE TABLE #ProcOrder (a int)

-- DML

SELECT * FROM #ProcOrder

GO

EXEC GetOrderInfo
SQL2005引入了新的语句级重新编译技术，有效的防止了因为部分语句引起的整个过程或批的重新编译。因此在SQL2000中跟踪到的内容会与图示所示存在不同之处。
另一个对临时表的命名问题，请慎记不要随便起一个#T1作为临时表的名称。这在过程不调用其它过程时不会造成问题，但在调用的另一过程中也存在一个#T1的临时表时，就会造成问题。以上示例演示：
复制内容到剪贴板代码:
SET NOCOUNT ON;

USE tempdb;

GO

IF OBJECT_ID('dbo.proc1') IS NOT NULL

  DROP PROC dbo.proc1;

GO

IF OBJECT_ID('dbo.proc2') IS NOT NULL

  DROP PROC dbo.proc2;

GO

CREATE PROC dbo.proc1

AS

CREATE TABLE #T1(col1 INT NOT NULL);

INSERT INTO #T1 VALUES(1);

SELECT * FROM #T1;

EXEC dbo.proc2;

GO

CREATE PROC dbo.proc2

AS

CREATE TABLE #T1(col1 INT NULL, col2 INT NOT NULL);

INSERT INTO #T1 VALUES(2, 2);

SELECT * FROM #T1;

GO

EXEC dbo.proc1;
因此，请在对临时表命名时使用过程名称与临时表名称的组，如#proc_T1。这样会有效的防止此类问题的出现。

4.   另一个值得注意的问题是，我们不应该使用sp_作为自定义存储过程的前缀。这是微软用作系统存储过程的前缘。在调用以sp_开头的过程时，会首先在master数据库中进行查找，如果找不到才会在用户数据库中查找。如果要使过程真正成为系统过程要使用sp_MS_marksystemobject过程，如下所示：
复制内容到剪贴板代码:
USE master;

GO

EXEC sp_MS_marksystemobject 'dbo.sp_Proc1';
同样对表或其它对象的命名时也不要以sp_作为前缀。

5.   另一个问题是存储过程编写的安全方面，因为传入的参数可能会引起脚本注入的危险，因此应该对每个参数进行一些判断，以下是一种通用的判断语句，可应用于每个参数：
复制内容到剪贴板代码:
IF UPPER(@cols) LIKE UPPER(N'%0x%')

  OR UPPER(@cols) LIKE UPPER(N'%;%')

  OR UPPER(@cols) LIKE UPPER(N'%''%')

  OR UPPER(@cols) LIKE UPPER(N'%--%')

  OR UPPER(@cols) LIKE UPPER(N'%/*%*/%')

  OR UPPER(@cols) LIKE UPPER(N'%EXEC%')

  OR UPPER(@cols) LIKE UPPER(N'%xp_%')

  OR UPPER(@cols) LIKE UPPER(N'%sp_%')

  OR UPPER(@cols) LIKE UPPER(N'%SELECT%')

  OR UPPER(@cols) LIKE UPPER(N'%INSERT%')

  OR UPPER(@cols) LIKE UPPER(N'%UPDATE%')

  OR UPPER(@cols) LIKE UPPER(N'%DELETE%')

  OR UPPER(@cols) LIKE UPPER(N'%TRUNCATE%')

  OR UPPER(@cols) LIKE UPPER(N'%CREATE%')

  OR UPPER(@cols) LIKE UPPER(N'%ALTER%')

  OR UPPER(@cols) LIKE UPPER(N'%DROP%')

BEGIN

  SET @msg = N'Possible SQL injection attempt.';

  RAISERROR(@msg, 16, 1);

RETURN;

END


本文来自: IT先锋论坛(http://bbs.ad0.cn) 原文出处：http://bbs.ad0.cn/redirect.php?fid=8&tid=3968&goto=nextnewset