如何优化存储过程？

Question

CREATE PROCEDURE p_THMX_DayLog_dispense
(
  @iMaxTableID  NUMERIC(28) = NULL
)
AS
  DECLARE @TonghuaBH        BIGINT      
  DECLARE @ZhujiaoHM        VARCHAR(26)      
  DECLARE @BeijiaoHM        VARCHAR(26)    
  DECLARE @KaishiShijian    DATETIME     
  DECLARE @GuaJiShijian     DATETIME         
  DECLARE @Tonghuasc        BIGINT         
  DECLARE @ZhuanRF          TINYINT 
  DECLARE @Mods             INT 
  DECLARE @rctimes          INT
  DECLARE @tel_mins         INT
  DECLARE @MaxTableID       NUMERIC(28,0)    
  DECLARE @MinTableID       NUMERIC(28,0)  
  DECLARE @month            VARCHAR(26) 
  DECLARE @ErrCode          NUMERIC(6,0)
  DECLARE @ErrMsg           VARCHAR(200)
  DECLARE @JiaxiaoTfs       TINYINT
  DECLARE @Area             VARCHAR(26) 
  DECLARE @BJ_Area          VARCHAR(26)--0110
  DECLARE @In_Out           TINYINT
  DECLARE @past             TINYINT
BEGIN
  -- 如果iMaxTableID为空,取表tCurrentBillLog当前最大的TableID
  IF @iMaxTableID IS NULL
    BEGIN
      SELECT @MaxTableID = MAX(TonghuaBH) FROM TonghuaMX_t

      -- 如果tMaxTableID为空,直接返回
      IF @MaxTableID IS NULL
        RETURN      -- 自动日结先判断表中的最大和最小的TableID,控制一次日结只结10000条数据
      SELECT @MinTableID = MIN(TonghuaBH) FROM TonghuaMX_t
      IF @MaxTableID - @MinTableID > 10000
        SELECT @MaxTableID = @MinTableID + 10000
    END
  ELSE -- 手工日结
    BEGIN
      SELECT @MaxTableID = @iMaxTableID
      SELECT @MinTableID = MIN(TonghuaBH) FROM TonghuaMX_t
    END  -- 为避免事务过大，将事务进行分割，一次做2000条话单
  SELECT @MinTableID = @MinTableID + 2000  -- 如果tMinTableID+2000大于tMaxTableID,则将tMaxTableID值赋给tMinTableID
  IF @MinTableID >= @MaxTableID 
    SELECT @MinTableID = @MaxTableID  WHILE @MinTableID <= @MaxTableID
    BEGIN
      BEGIN TRAN DAYLOG
      -- 定义取话单游标
      DECLARE c_DayLog_Bill CURSOR FOR
       SELECT TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian,GuaJiShijian,Tonghuasc,ZhuanRF
       FROM TonghuaMX_t
      WHERE TonghuaBH <= @MinTableID ORDER BY TonghuaBH FOR READ ONLY      -- 开始以2000条数据为一个事务进行循环
      OPEN c_DayLog_Bill
        FETCH c_DayLog_Bill INTO
          @TonghuaBH,@ZhujiaoHM,@BeijiaoHM,@KaishiShijian,@GuaJiShijian,@Tonghuasc,@ZhuanRF
      WHILE @@FETCH_STATUS = 0
        BEGIN           --将数据插入到月TonghuaMX_t表中         
          IF (@ZhuanRF=1 and @ZhujiaoHM<>'96008')
             SELECT @month= DATENAME(mm,@KaishiShijian)  
             IF @month=01
             BEGIN
               INSERT INTO        
               表一(TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian)
               VALUES(@TonghuaBH,@ZhujiaoHM,@BeijiaoHM,@KaishiShijian)
             END 
             ELSE 
             IF @month=02
             BEGIN
               INSERT INTO 表2(TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian)
               VALUES(@TonghuaBH,@ZhujiaoHM,@BeijiaoHM,@KaishiShijian)
             END
  
          END          FETCH c_DayLog_Bill INTO
            @TonghuaBH,@ZhujiaoHM,@BeijiaoHM,@KaishiShijian,@GuaJiShijian,@Tonghuasc,@ZhuanRF        END      CLOSE c_DayLog_Bill
      DEALLOCATE c_DayLog_Bill      -- 删除已日结的话单
      DELETE FROM TonghuaMX_t
       WHERE TonghuaBH <= @MinTableID      COMMIT TRAN DAYLOG      SELECT  @MinTableID = @MinTableID + 2000
    END     RETURN
GO

Answer 1

WHILE @MinTableID <= @MaxTableID 
    BEGIN 
      BEGIN TRAN DAYLOG       -- 定义取话单游标 
      DECLARE c_DayLog_Bill CURSOR FOR 
      SELECT TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian,GuaJiShijian,Tonghuasc,ZhuanRF 
      FROM TonghuaMX_t 
      WHERE TonghuaBH <= @MinTableID ORDER BY TonghuaBH FOR READ ONLY       -- 开始以2000条数据为一个事务进行循环 
      OPEN c_DayLog_Bill 
        FETCH c_DayLog_Bill INTO 
          @TonghuaBH,@ZhujiaoHM,@BeijiaoHM,@KaishiShijian,@GuaJiShijian,@Tonghuasc,@ZhuanRF 
      WHILE @@FETCH_STATUS = 0 
        BEGIN           --将数据插入到月TonghuaMX_t表中        
          IF (@ZhuanRF=1 and @ZhujiaoHM <>'96008') 
            SELECT @month= DATENAME(mm,@KaishiShijian)  
            IF @month=01 
            BEGIN 
              INSERT INTO        
              表一(TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian) 
              VALUES(@TonghuaBH,@ZhujiaoHM,@BeijiaoHM,@KaishiShijian) 
            END 
            ELSE 
            IF @month=02 
            BEGIN 
              INSERT INTO 表2(TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian) 
              VALUES(@TonghuaBH,@ZhujiaoHM,@BeijiaoHM,@KaishiShijian) 
            END 
  
          END           FETCH c_DayLog_Bill INTO 
            @TonghuaBH,@ZhujiaoHM,@BeijiaoHM,@KaishiShijian,@GuaJiShijian,@Tonghuasc,@ZhuanRF         END       CLOSE c_DayLog_Bill 
      DEALLOCATE c_DayLog_Bill       -- 删除已日结的话单 
      DELETE FROM TonghuaMX_t  WHERE TonghuaBH <= @MinTableID 
      COMMIT TRAN DAYLOG 
      SELECT  @MinTableID = @MinTableID + 2000 
    END 
----------------
这一段完全没有必要。可以直接批量修改，如：
 INSERT INTO 表一(TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian) select TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian FROM TonghuaMX_t 
  WHERE ZhuanRF=1 and ZhujiaoHM <>'96008' and  DATENAME(mm,KaishiShijian)='01'  INSERT INTO 表2(TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian) select TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian FROM TonghuaMX_t 
  WHERE ZhuanRF=1 and ZhujiaoHM <>'96008' and  DATENAME(mm,KaishiShijian)='02'

Answer 2

--不知道这样跟你的原意有没有变。一次更新所有。
CREATE PROCEDURE p_THMX_DayLog_dispense 
AS 
 INSERT INTO 表一(TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian) select TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian FROM TonghuaMX_t 
  WHERE ZhuanRF=1 and ZhujiaoHM <>'96008' and  DATENAME(mm,KaishiShijian)='01'  INSERT INTO 表2(TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian) select TonghuaBH,ZhujiaoHM,BeijiaoHM,KaishiShijian FROM TonghuaMX_t 
  WHERE ZhuanRF=1 and ZhujiaoHM <>'96008' and  DATENAME(mm,KaishiShijian)='02'GO