SQL SERVER数据库中存储过程使用循环语句

SQL SERVER数据库中存储过程使用循环语句

http://database.51cto.com  2010-09-06 11:05  佚名  互联网  我要评论(0)

• 摘要：SQL SERVER数据库建立存储过程时，可以使用循环语句，下面就将为您介绍这种SQL SERVER数据库中存储过程使用循环语句的方法，供您参考。
• 标签：SQL SERVER  语句

SQL SERVER数据库建立存储过程时，可以使用循环语句，下面就将为您介绍这种SQL SERVER数据库中存储过程使用循环语句的方法，供您参考。

CREAT PROCEDURE tester  
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @userId varchar(50)
    DECLARE @count int
    SET @count = 0
    SELECT @count = count(*) FROM   UserService_User WHERE Account like '%111%'
   WHILE @count > 0
    BEGIN
        SELECT @userId = Id FROM   UserService_User WHERE Account like '%111%'
        exec UserService_RemoveUserByUserId @userId
        SET @count = @count -1
    END
END

说明：

1、此存储过程在SQL SERVER 2005上测试通过，值得注意的是，循环体中，语句是使用BEGIN……END包括的，而不是网络上常说的WHILE ……END WHILE结构，其他的循环语句，如LOOP ……UNTIL……END LOOP也不能通过编译，也许是版本的问题，但在SQL SERVER2005中，循环体使用BEGIN……END就可以，而不能使用网络上常说的WHILE ……END WHILE结构。

2、循环体中 UserService_RemoveUserByUserId 是一个存储过程的名称，@userId为该存储过程的参数，如果有多个参数，使用“,”分开就可以了，这也是存储过程调用另一个存储过程的一种方法。

 

 

SQL Server中存储过程慢于SQL语句直接执行的原因

http://database.51cto.com  2010-09-06 11:24  佚名  互联网  我要评论(0)

• 摘要：SQL Server数据库中，存储过程的执行总是要比SQL语句直接执行要慢，这究竟是为什么呢？本文将带您寻找答案。
• 标签：SQL Server  语句

SQL Server数据库中，存储过程的执行总是要比SQL语句直接执行要慢，这究竟是为什么呢？本文将带您寻找答案。

在SQL Server中有一个叫做 “Parameter sniffing”的特性。SQL Server在存储过程执行之前都会制定一个执行计划。在上面的例子中，SQL在编译的时候并不知道@thedate的值是多少，所以它在执行执行计划的时候就要进行大量的猜测。假设传递给@thedate的参数大部分都是非空字符串，而FACT表中有40%的thedate字段都是null，那么SQL Server就会选择全表扫描而不是索引扫描来对参数@thedate制定执行计划。全表扫描是在参数为空或为0的时候最好的执行计划。但是全表扫描严重影响了性能。

假设你第一次使用了Exec pro_ImAnalysis_daily @thedate=’20080312’那么SQL Server就会使用20080312这个值作为下次参数@thedate的执行计划的参考值，而不会进行全表扫描了，但是如果使用@thedate=null,则下次执行计划就要根据全表扫描进行了。

有两种方式能够避免出现“Parameter sniffing”问题：
<!--(1)通过使用declare声明的变量来代替参数：使用set @variable=@thedate的方式，将出现@thedate的sql语句全部用@variable来代替。
<!--(2) 将受影响的sql语句隐藏起来，比如：
<!-- a)      将受影响的sql语句放到某个子存储过程中，比如我们在@thedate设置成为今天后再调用一个字存储过程将@thedate作为参数传入就可以了。
<!-- b)      使用sp_executesql来执行受影响的sql。执行计划不会被执行，除非sp_executesql语句执行完。
<!-- c)      使用动态sql（”EXEC(@sql)”来执行受影响的sql。
采用（1）的方法改造例子中的存储过程，如下：

代码：

ALTER PROCEDURE [dbo].[pro_ImAnalysis_daily]@var_thedate VARCHAR(30) ASBEGIN    declare @THEDATE VARCHAR(30)    IF @var_thedate IS NULL    BEGIN       SET @var_thedate=CONVERT(VARCHAR(30),GETDATE()-1,112);    END      SET @THEDATE=@var_thedate;    DELETE FROM RPT_IM_USERINFO_DAILY WHERE THEDATE=@THEDATE;    INSERT RPT_IM_USERINFO_DAILY (THEDATE,ALLUSER,NEWUSER)    SELECT AA.THEDATE,ALLUSER,NEWUSER    FROM    ( ( SELECT THEDATE,COUNT(DISTINCT USERID) ALLUSER       FROM FACT       WHERE THEDATE=@THEDATE        GROUP BY THEDATE       ) AA       LEFT JOIN       (SELECT THEDATE,COUNT(DISTINCT USERID) NEWUSER        FROM FACT T1        WHERE NOT EXISTS(                         SELECT 1                         FROM FACT T2                         WHERE T2.THEDATE<@THEDATE                             AND T1.USERID=T2.USERID)              AND T1.THEDATE=@THEDATE        GROUP BY THEDATE        ) BB       ON AA.THEDATE=BB.THEDATE);GO