数据库触发器的执行效率问题

CREATE TRIGGER dbo.HIS_SAMP_insert ON dbo.HIS_SAMP
FOR INSERT AS
--插入新的记录后，自动计算单位时段的电量
IF @@rowcount<=1
BEGIN
declare @curtime char(26)
select @curtime=convert(char(26), getdate(), 109)
print '----进入HIS_SAMP_insert-%1!-----', @curtime
    declare @dp char(2)
    select @dp=convert(char(2), @@nestlevel)
    print '-----采样点单位电量(深度%1!)-----', @dp

    declare @ID int, @TIME datetime
    select @ID=ID, @TIME=TIME from inserted

    --获得所属电表和类型
    declare @nMeterID int, @nType int
/*    select @nMeterID=idx_meter, @nType=TYPE from METER_PNT_DETAIL m,HIS_DEF h where
            h.ID=@ID and m.nRtu_meter=h.SRC_RTU and m.NPNT=h.SRC_PNT */
-----------------------------------------zhuliang---------------------------------------
----------原来的语句效率很低，拆成下面两句
declare @nRTU int,@nPNT int
select @nRTU=SRC_RTU,@nPNT=SRC_PNT from HIS_DEF where ID=@ID
select @nMeterID=idx_meter, @nType=TYPE from METER_PNT_DETAIL where
            nRtu_meter=@nRTU and NPNT=@nPNT
-----------------------------------------zhuliang结束---------------------------------------
    if @nMeterID is null or @nType is null return

    --计算本身的前后时段电量
    execute sp_Tms_Calc_Energy_Time @ID, @nMeterID, @nType, @TIME      select @curtime=convert(char(26), getdate(), 109)
print '----退出HIS_SAMP_insert-%1!-----', @curtime
    --计算代路的点的前后时段电量
/*    execute sp_Tms_Calc_Energy_Time_ByPass @ID, @nMeterID, @nType, @TIME    */--上一句已经调用这个功能，参见HIS_SAMP_update的ENERGY部分
END
CREATE PROCEDURE dbo.sp_Tms_Calc_Energy_Time
(@ID int, @nMeterID int, @nType int, @CurrTime datetime)
AS
BEGIN
    --获得单位时间间隔
declare @curtime char(26)
select @curtime=convert(char(26), getdate(), 109)
print '----进入sp_Tms_Calc_Energy_Time-%1!-----', @curtime    declare @TimeUnit int
    execute sp_Tms_Get_Time_Unit @TimeUnit output

    declare @BeginTime datetime, @EndTime datetime

    declare @dp char(2)
    select @dp=convert(char(2), @@nestlevel)

    print '---前一单位时段(深度%1!)---', @dp

    select @BeginTime=DATEADD(mi, -@TimeUnit, @CurrTime)
    select @EndTime=@CurrTime

    --如果前一个时刻的记录已有，计算电量
    declare @n int, @Energy real
    select @n=count(*) from HIS_SAMP where ID=@ID and TIME=@BeginTime
    if @n = 1
    begin
        execute sp_Tms_Calc_Energy_Unit @ID, @nMeterID, @nType, @BeginTime, @EndTime, @Energy output

        declare @bf char(128)
        select @bf='采样点'+convert(char(4), @ID)+'单位时段电量:'+convert(char(16), convert(numeric(10,2),@Energy))
        print @bf

        --更新HIS_SAMP.ENERGY
        update HIS_SAMP set ENERGY=@Energy where ID=@ID and TIME=@BeginTime
    end

print '---后一单位时段(深度%1!)---', @dp

    select @BeginTime=@CurrTime
    select @EndTime=DATEADD(mi, @TimeUnit, @CurrTime)

    --如果后一个时刻的记录已有，计算电量
    select @n=count(*) from HIS_SAMP where ID=@ID and TIME=@EndTime
    if @n = 1
    begin
        execute sp_Tms_Calc_Energy_Unit @ID, @nMeterID, @nType, @BeginTime, @EndTime, @Energy output

        --declare @bf char(128)
        select @bf='采样点'+convert(char(4), @ID)+'单位时段电量:'+convert(char(16), convert(numeric(10,2),@Energy))
        print @bf

       --更新HIS_SAMP.ENERGY
        update HIS_SAMP set ENERGY=@Energy where ID=@ID and TIME=@BeginTime
    end
select @curtime=convert(char(26), getdate(), 109)
print '----退出sp_Tms_Calc_Energy_Time-%1!-----', @curtimeEND
下面这个触发器也应该被调用了，但是并没有出现print的结果
CREATE TRIGGER dbo.HIS_SAMP_update ON dbo.HIS_SAMP
FOR UPDATE AS
BEGIN
declare @curtime char(26)
select @curtime=convert(char(26), getdate(), 109)
print '----进入HIS_SAMP_update-%1!-----', @curtime
--底码VALUE修改后自动计算单位时段的电量
IF UPDATE (VALUE) and @@rowcount<=1
BEGIN
    declare @dp char(64), @bf char(64)
    declare @ID int, @TIME datetime
    select @dp=convert(char(2),@@nestlevel)
    print '-----采样点单位电量(深度%1!)-----', @dp
    --如果数据值没有改变，直接返回
    if (select VALUE from inserted)=(select VALUE from deleted) return


    select @ID=ID, @TIME=TIME from inserted

    --获得所属电表和类型
    declare @nMeterID int, @nType int
/*    select @nMeterID=idx_meter, @nType=TYPE from METER_PNT_DETAIL m,HIS_DEF h where
            h.ID=@ID and m.nRtu_meter=h.SRC_RTU and m.NPNT=h.SRC_PNT*/
-----------------------------------------zhuliang---------------------------------------
----------原来的语句效率很低，拆成下面两句
declare @nRTU int,@nPNT int
select @nRTU=SRC_RTU,@nPNT=SRC_PNT from HIS_DEF where ID=@ID
select @nMeterID=idx_meter, @nType=TYPE from METER_PNT_DETAIL where
            nRtu_meter=@nRTU and NPNT=@nPNT
-----------------------------------------zhuliang结束---------------------------------------
    if @nMeterID is null or @nType is null return

    --计算本身的前后时段电量
    execute sp_Tms_Calc_Energy_Time @ID, @nMeterID, @nType, @TIME
    --计算代路的点的前后时段电量
/*     execute sp_Tms_Calc_Energy_Time_ByPass @ID, @nMeterID, @nType, @TIME   */--zhuliang 在本触发器后面实现这个功能
END
-------------------------------------------------------------------------------------------------------
--电量ENERGY修改后自动计算小时电量、时段电量、计算点电量
IF UPDATE (ENERGY) and @@rowcount<=1
BEGIN
    set self_recursion on

    select @dp=convert(char(2),@@nestlevel)
    print '-----采样点统计电量(深度%1!)-----', @dp

    --如果数据值没有改变，直接返回
    declare @old real, @new real --, @ID numeric(9,0), @TIME datetime
    select @old=ENERGY from deleted
    select @new=ENERGY from inserted

    if @old=@new return

    select @ID=ID, @TIME=TIME from inserted

    ------ 处理小时电量 ------

    --所在的小时整点时刻
    declare @HourTime datetime --, @bf char(128)
    select @HourTime=DATEADD(mi, -DATEPART(mi, @TIME), @TIME)

    --所在小时的电量值
    declare @HourEnergy numeric(16,5)
    select @HourEnergy=ENERGY from HIS_HOUR_ENERGY where ID=@ID and TIME=@HourTime
    --小时电量还没有生成
    if @HourEnergy is null
    begin
        select @bf='采样点'+convert(char(4),@ID)+'小时电量:'+convert(char(16), @new)
      print @bf

        insert into HIS_HOUR_ENERGY(ID,TIME,ENERGY) VALUES(@ID,@HourTime,@new)
    end
    --小时电量已经生成
    else
    begin
        if @old is null select @old=0
        select @HourEnergy=@HourEnergy-@old+@new

        select @bf='采样点'+convert(char(4),@ID)+'小时电量:'+convert(char(16), @HourEnergy)
        print @bf

        update HIS_HOUR_ENERGY set ENERGY=@HourEnergy where @ID=ID and TIME=@HourTime
    end

    ------ 处理时段电量 ------

    --所在的时段，可能处在多个时段中
    declare @PeriodType int, @PMinute int
    select @PMinute = 60*DATEPART(hh,@TIME)+DATEPART(mi,@TIME)

    --当天的日期
    declare @PeriodTime datetime
    select @PeriodTime=DATEADD(mi, -DATEPART(mi, @TIME), @TIME)
    select @PeriodTime=DATEADD(hh, -DATEPART(hh, @TIME), @PeriodTime)
/*select @bf='PeriodTime:'+convert(char(16),@PeriodTime)
print @bf
select @bf='PMinute:'+convert(char(16),@PMinute)
print @bf */
    declare @PeriodEnergy numeric(16,5)

/*    declare curPrd cursor for
        select d.idx from TIME_SEG_DETAIL d, TIME_SEG s where
        (d.nHourB*60+d.nMinuteB)<=@PMinute and (d.nHourE*60+d.nMinuteE)>@PMinute and
        d.idx=s.idx and s.DayBeginUse<=@TIME and (s.DayEndUse>@TIME or s.DayEndUse is null) */
-----------朱良 03-06-04-------------------------------
      declare curPrd cursor for
        select d.type from TIME_SEG_DETAIL d, TIME_SEG s where
        (d.nHourB*60+d.nMinuteB)<=@PMinute and (d.nHourE*60+d.nMinuteE)>@PMinute and
        d.idx=s.idx and s.DayBeginUse<=@TIME and (s.DayEndUse>@TIME or s.DayEndUse is null)
-----------朱良 03-06-04结束-------------------------------

    open curPrd
    fetch curPrd into @PeriodType
    while @@sqlstatus=0
  begin
/*  select @bf='PeriodType:'+convert(char(16),@PeriodType)
print @bf */
        select @PeriodEnergy=null
        --所在时段的电量值
        select @PeriodEnergy=ENERGY from HIS_PERIOD_ENERGY where ID=@ID and TIME=@PeriodTime and TYPE=@PeriodType
        --时段电量还没有生成,insert
        if @PeriodEnergy is null
        begin
            select @bf='采样点'+convert(char(4),@ID)+'时段'+convert(char(2),@PeriodType)+'电量:'+convert(char(16), @new)
            print @bf
            insert into HIS_PERIOD_ENERGY(ID,TIME,TYPE,ENERGY) VALUES(@ID,@PeriodTime,@PeriodType,@new)
        end
        --时段电量已经生成,update
        else
        begin
            if @old is null select @old=0
            select @PeriodEnergy=@PeriodEnergy-@old+@new

            select @bf='采样点'+convert(char(4),@ID)+'时段'+convert(char(2),@PeriodType)+'电量:'+convert(char(16), @PeriodEnergy)
            print @bf

            update HIS_PERIOD_ENERGY set ENERGY=@PeriodEnergy where @ID=ID and TIME=@PeriodTime and TYPE=@PeriodType
        end
        fetch curPrd into @PeriodType
    end
    close curPrd
    deallocate cursor curPrd

    print '-----计算点电量(深度%1!)------', @dp
    declare @CalID numeric(9,0), @SrcSign int, @CalEnergy numeric(16,5)
    --包括当前点作为变量的计算点的游标集合
    declare curVar cursor for
        select ID, SRC_SIGN from CAL_DEF_DETAIL where SRC_TYPE=0 and SRC_ID=@ID --这里只处理type=0的情况
    open curVar
    fetch curVar into @CalID, @SrcSign
    while @@sqlstatus = 0
    begin
        select @bf='------计算点'+convert(char(4),@CalID)+'(深度'+convert(char(2),@@nestlevel)+')'
        print @bf

        --处理符号
        select @new=@new*@SrcSign
        select @old=@old*@SrcSign

     --计算点的单位时段电量
        select @CalEnergy=ENERGY from CAL_ENERGY where ID=@CalID and TIME=@TIME
        --计算点电量还未生成
        if @CalEnergy is null
        begin
      select @bf='计算点'+convert(char(4),@CalID)+'单位电量:'+convert(char(16), @new)
            print @bf

            insert into CAL_ENERGY(ID,TIME,ENERGY) VALUES(@CalID,@TIME,@new)
        end
        --计算点电量已经生成
        else
        begin
            if @old is null select @old=0
            select @CalEnergy=@CalEnergy-@old+@new

            select @bf='计算点'+convert(char(4),@CalID)+'单位电量:'+convert(char(16), @CalEnergy)
            print @bf

            update CAL_ENERGY set ENERGY=@CalEnergy where ID=@CalID and TIME=@TIME
        end

        --下一个计算点
        fetch curVar into @CalID, @SrcSign
    end
    close curVar
    deallocate cursor curVar
-----------------------------------------zhuliang---------------------------------------
/*以下语句是从原来的sp_Tms_Calc_Energy_Time_ByPass存储过程考过来，原过程有4个参数
@ID int, @nMeterID int, @nType int, @CurrTime datetime
其中 @ID上文有定义，可以直接使用，其余3个变量要重新定义并赋值，如下面几条语句      */
declare @CurrTime datetime
select @CurrTime=@TIME --@time在上文有定义与赋值
/*    select @nMeterID=idx_meter, @nType=TYPE from METER_PNT_DETAIL m,HIS_DEF h where
            h.ID=@ID and m.nRtu_meter=h.SRC_RTU and m.NPNT=h.SRC_PNT*/
-----------------------------------------zhuliang---------------------------------------
----------原来的语句效率很低，拆成下面两句
--declare @nRTU int,@nPNT int
select @nRTU=SRC_RTU,@nPNT=SRC_PNT from HIS_DEF where ID=@ID
select @nMeterID=idx_meter, @nType=TYPE from METER_PNT_DETAIL where
            nRtu_meter=@nRTU and NPNT=@nPNT
-----------------------------------------zhuliang结束---------------------------------------
    if @nMeterID is null or @nType is null return
    --是否是旁路代电表
    if (select nType_meter from METER where idx=@nMeterID)!=1 return

    --当前时刻是否旁路代其他点
    declare @nBypassedMeterID int,@n int, @ByPassID int
    select @nBypassedMeterID=nMeterID from ByPassResult where
        nByPassMeterID=@nMeterID and
        tBeginTime<=@CurrTime and (tEndTime>=@CurrTime or tEndTime is null)
    if @nBypassedMeterID is null return

    --被旁路代的点号
/*    select @ByPassID=ID from HIS_DEF h, METER_PNT_DETAIL m  where
                m.idx_meter=@nBypassedMeterID and   m.TYPE=@nType and
                h.SRC_RTU=m.nRtu_meter and h.SRC_PNT=m.NPNT */
-----------------------------------------zhuliang---------------------------------------
--declare @nRTU int,@nPNT int
select @nRTU=nRtu_meter,@nPNT=NPNT from METER_PNT_DETAIL where idx_meter=@nBypassedMeterID and TYPE=@nType
select @ByPassID=ID from HIS_DEF where SRC_RTU=@nRTU and SRC_PNT=@nPNT
-----------------------------------------zhuliang结束---------------------------------------
    if @ByPassID is null return

    --被旁路代的点当前时刻的在HIS_SAMP表中的记录是否存在
    select @n=count(*) from HIS_SAMP where ID=@ByPassID and TIME=@CurrTime
    if @n=0 return

    --被代路的点的记录已经存在，重新计算被代路的点的电量
   -- declare @dp char(2)
    select @dp=convert(char(2), @@nestlevel)

    print '---重新计算被旁路代点电量(深度%1!)---', @dp
    update HIS_SAMP set ENERGY=ENERGY-@old+@new where ID=@ByPassID and TIME=@CurrTime
-----------------------------------------zhuliang结束---------------------------------------
END
select @curtime=convert(char(26), getdate(), 109)
print '----退出HIS_SAMP_update-%1!-----', @curtime
END

调试易

数据库触发器的执行效率问题

解决方案 »