触发器和存储过程哪个更快？

Question

我目前在每个表上都有一个触发器来处理历史日志。触发器在每个表上都是完全相同的。见下文。

如果我把它移到一个存储过程中，它会更快吗？

另外，如果我使用存储过程，触发器是否会释放，让用户继续？

create trigger ' + @TABLE_NAME + '_ChangeTracking on ' + @TABLE_NAME + ' for 
insert, update, delete
as
    declare @bit int ,
            @field int ,
            @maxfield int ,
            @char int ,
            @fieldname varchar(128) ,
            @TableName varchar(128) ,
            @PKCols varchar(1000) ,
            @sql nvarchar(max), 
            @Type nvarchar(1) ,
            @PKValueSelect varchar(1000),
            @MasterId nvarchar(max) = ''0''

    select @TableName = ''' + @TABLE_NAME + '''

    if exists(select * from CNF_HIL_Tables where referencetable = @TableName and Active = 1)
    begin
        if exists (select * from inserted)
            if exists (select * from deleted)
                select @Type = ''2''
            else
                select @Type = ''3''
        else
            select @Type = ''1''

        select * into #ins from inserted
        select * into #del from deleted

        select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk 
        inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c on c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME and c.TABLE_NAME = pk.TABLE_NAME
        where pk.TABLE_NAME = @TableName
          and CONSTRAINT_TYPE = ''PRIMARY KEY''

        select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk    
        inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c on c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME and c.TABLE_NAME = pk.TABLE_NAME  
        where pk.TABLE_NAME = @TableName   
          and CONSTRAINT_TYPE = ''PRIMARY KEY'' 

        select @field = 0, 
               @maxfield = max(ORDINAL_POSITION) 
        from INFORMATION_SCHEMA.COLUMNS 
        where TABLE_NAME = @TableName

        while @field < @maxfield
        begin
            select @field = min(ORDINAL_POSITION) 
            from INFORMATION_SCHEMA.COLUMNS 
            where TABLE_NAME = @TableName 
              and ORDINAL_POSITION > @field

            select @bit = (@field - 1 )% 8 + 1
            select @bit = power(2,@bit - 1)
            select @char = ((@field - 1) / 8) + 1

            if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''1'',''3'')
        begin
            select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field

            if exists(select * from CNF_Hil_Columns INNER JOIN CNF_HIL_Tables    ON CNF_HIL_Tables.TablesId = CNF_Hil_Columns.TablesId
                                where CNF_HIL_Tables.referencetable = @TableName  and CNF_Hil_Columns.ColumnName = @fieldname
                                    and CNF_Hil_Columns.Active = 1
                        )
            begin

                if @MasterId = 0
                begin
                    select @sql = ''insert DATA_HIL_Master (OperationType, ReferenceTable, ReferenceId, UserId, WorkstationId, InsDateTime)''
                    select @sql = @sql + '' select '''''' + @Type + ''''''''                                                
                    select @sql = @sql + '', '''''' + @TableName + ''''''''     
                    select @sql = @sql + '','' + @PKValueSelect     
                    select @sql = @sql + '',convert(varchar(1000),i.Last_UserId_Log)''
                    select @sql = @sql + '',convert(varchar(1000),i.Last_WorkstationId_Log)''
                    select @sql = @sql + '',convert(varchar(1000),i.Last_DateTime_Log)''
                    select @sql = @sql + '' from #ins i full outer join #del d''
                    select @sql = @sql + @PKCols 
                    select @sql = @sql + '' SELECT @MasterId = SCOPE_IDENTITY() ''
                    EXECUTE sp_executesql @sql, N''@MasterId nvarchar(max) OUTPUT'', @MasterId OUTPUT
                end

                select @sql = ''insert data_HIL_Detail (MasterId, ColumnName, OriginalValue, ModifiedValue)''
                select @sql = @sql + '' select convert(varchar(1000),'' + @MasterId + '')''
                select @sql = @sql + '','''''' + @fieldname + ''''''''
                select @sql = @sql + '', convert(varchar(1000),d.'' + @fieldname + '')''
                select @sql = @sql + '', convert(varchar(1000),i.'' + @fieldname + '')''
                select @sql = @sql + '' from #ins i full outer join #del d''
                select @sql = @sql + @PKCols

                EXECUTE sp_executesql @sql
            END
        END
    END 
END

Answer 1

通常，不管是触发器还是存储过程，您都会得到相同的代码。在触发器中，你不能直接调用它，而在存储过程中，你直接调用它。因此，无论您使用触发器还是存储过程，在执行方面都是一样的。第一次调用它时，将缓存执行计划。

在您的例子中，由于您专门使用插入的、删除的表，因此您应该使用不同的存储过程代码来实现审计。或者，您可以考虑使用SQL Server temporal tables、Change Data Capture或SQL Server auditing

但是，使用触发器的缺点很少。

它可以使事务调试longer

• Difficult to

Answer 2

我实际上是在寻找这个问题的答案，结果在这里跌跌撞撞。

我已经找到了很多不同的答案，但作为一名学生，我现在被告知“存储过程比单个SQL语句运行得更快；这提高了性能。”所以，答案似乎是肯定的。

然而，不同的人可能会对“性能”有不同的解释。我还不是很有经验，所以我还不能真正理解所有的细微差别。我看到一些评论将这种差异归因于“缓存”，而另一些评论建议使用存储过程只是因为更好的“控制”安全性和维护性，而不是任何与性能相关的东西。

在阅读我的课程材料时，我也发现了一些可能相关的东西。这是从PostgreSQL数据库开始:从新手到专业人员(Stones and Matthew，2005)：

存储过程驻留在服务器端，而不是客户端，增加了访问控制。从客户端调用，只将结果传递给调用者，这减少了网络流量。多个应用程序可以使用单个存储过程，从而标准化处理规则。

所以，也许这就是所谓的“性能”。

存储过程看起来也更类似于函数本身，函数是存储在数据库中并由所有其他数据库对象使用的对象。而触发器是与运行函数的表相关联的对象。