SQL Server 2008插入后，当我使用审计表插入或删除表时，更新触发器非常慢

Question

我item_audit table有 100 万行。 下面是我在项目表上的触发器。 为什么这么慢？

create trigger [dbo].[item_audit_trigger] on [dbo].[item]  after insert, update as  
begin 
    declare @type char(1), @begindate varchar(16), @enddate varchar(16),      @hostname   
          nvarchar(128), @programname nvarchar(128), @loginname nvarchar(128),      
    @logintime varchar(16), @ocuserid varchar(12), @ocquefromid varchar(10), @lcontextinfo
          varchar(128)  

    set @begindate = dbo.DateToD16(getdate()) 
    set @enddate   = '9999123100000000' 
    select @hostname     = host_name from sys.dm_exec_sessions where session_id = @@SPID 
    select @programname  = program_name from sys.dm_exec_sessions where session_id = @@SPID 
    select @loginname    = login_name from sys.dm_exec_sessions where session_id = @@SPID  
    select @logintime    = dbo.DateToD16(login_time) from sys.dm_exec_sessions where 
          session_id = @@SPID  
    select @lcontextinfo = cast(context_info as varchar(128)) from sys.dm_exec_sessions where  
          session_id = @@spid  
    set @ocuserid    = substring(@lcontextinfo, 1, 12)  
    set @ocquefromid = substring(@lcontextinfo, 13, 10) 
...
        set @type = 'I'  
---------------  Below update takes 2 minutes
        update medorder_audit set audit_end = @begindate  
        from medorder_audit a inner join inserted d 
        on  a.MO_ID = d.MO_ID and  a.PAT_ID = d.PAT_ID  
        where audit_end = @enddate 
---------------------------------------------------------------
        insert into medorder_audit (mo_id, mo_stat, pat_id, item_id, physn_id, physn_name, pharm_id, start_dati, stop_dati, frequency, dose, route, site, prn, sol_time, 
        sol_amt, sol_units, adm_times, adm_instr, add_dati, lu_dati, lu_from, er_wnd, lt_wnd, mo_qty, item_name, dos_str, str_units, dos_units, dos_amt, tvol_amt, tvol_units, 
        dos_form, mo_pstatus, mo_doseunt, xact_dati, user_id, user_name, omni_stid, qty, dose_text, mo_alert, give_amt, give_units, dose_max, rx_suffix, rx_name, rx_disp, 
        duration, interval, stat, only_days, wnd_type, sch_done, use_def, mocf_stat, mocf_user, mocf_dati, admin_dati, adm_dur, adm_site, comp_ct, comp_dati, mocf_inits, 
        mocf_title, ftdue_dati, is_iv, mix_id, mix_type, mo_disp, lu_user, modisp_db, psb_fill, audit_begn, audit_end, audit_type, audit_host, audit_prgm, audit_logn, 
        audit_date) select mo_id, mo_stat, pat_id, item_id, physn_id, physn_name, pharm_id, start_dati, stop_dati, frequency, dose, route, site, prn, sol_time, sol_amt, 
        sol_units, adm_times, adm_instr, add_dati, lu_dati, lu_from, er_wnd, lt_wnd, mo_qty, item_name, dos_str, str_units, dos_units, dos_amt, tvol_amt, tvol_units, dos_form, 
        mo_pstatus, mo_doseunt, xact_dati, user_id, user_name, omni_stid, qty, dose_text, mo_alert, give_amt, give_units, dose_max, rx_suffix, rx_name, rx_disp, duration, 
        interval, stat, only_days, wnd_type, sch_done, use_def, mocf_stat, mocf_user, mocf_dati, admin_dati, adm_dur, adm_site, comp_ct, comp_dati, mocf_inits, mocf_title, 
        ftdue_dati, is_iv, mix_id, mix_type, mo_disp, lu_user, modisp_db, psb_fill, @begindate, @enddate, @type, @hostname, @programname, @loginname, @logintime  
        from inserted 

Answer 1

而不是在同一触发器上进行插入，更新，删除，而应该使用3个不同的触发器对表进行操作并检查表的索引