事务块会降低 SQL Server 的性能吗？

Question

现在我和一位同事正在争论非重要的 BEGIN TRAN....COMMIT TRAN 块的影响。 我已经为简单的插入-更新-删除操作编写了大约 140 个存储过程，并且由于我们稍后可能需要在其中执行一些额外的操作，因此我已经包含了可能需要的 BEGIN TRAN 和 COMMIT TRAN 块，如下所示：

CREATE PROCEDURE [Users].[Login_Insert]

        @Username           nvarchar (50) OUTPUT,
        @Password           char (40),
        @FullName           nvarchar (150),
        @LoginTypeId        int

AS

SET NOCOUNT ON;

BEGIN TRY
BEGIN TRAN

INSERT [Users].[Login]
(
        [Username],
        [Password],
        [FullName],
        [LoginTypeId]
)
VALUES
(
        @Username,
        @Password,
        @FullName,
        @LoginTypeId
)

COMMIT TRAN
RETURN 1
END TRY

BEGIN CATCH
ROLLBACK TRAN

RETURN -1
END CATCH

GO

现在，其中许多交易可能永远没有必要。这些无关的块是否会显着影响性能？ 提前致谢。

Answer 1

还不足以引起注意。

也就是说，每个 TXN 将在 BEGIN TRAN 和 INSERT 之间打开额外的 OhNoSecond。如果有人能测量它，我会印象深刻。

然而，如果你做了 BEGIN TRAN 然后提示用户输入，你的腿需要折断...

不过好主意：我这样做是为了让我所有的写入过程都是 100% 一致，具有相同的错误处理，可以嵌套等

编辑：在 Remus 之后' 答案，我发现我没有链接到我的嵌套 TXN 模板：包含 TRY CATCH ROLLBACK 模式的嵌套存储过程？与 Remus 的不同之处在于它总是回滚并且没有保存点

编辑，快速而肮脏的测试表明它更快事务的 2/3 时间

SET NOCOUNT ON
SET STATISTICS IO OFF

DECLARE @date DATETIME2
DECLARE @noTran INT
DECLARE @withTran INT

SET @noTran = 0
SET @withTran = 0

DECLARE @t TABLE (ColA INT)
INSERT @t VALUES (1)

DECLARE 
  @count INT,
  @value INT

SET @count = 1

WHILE @count < 100
BEGIN

  SET @date = GETDATE()
  UPDATE smalltable SET smalltablename = CASE smalltablename WHEN 'test1' THEN 'test' ELSE 'test2' END WHERE smalltableid = 1
  SET @noTran = @noTran + DATEDIFF(MICROSECOND, @date, GETDATE())

  SET @date = GETDATE()
  BEGIN TRAN
  UPDATE smalltable SET smalltablename = CASE smalltablename WHEN 'test1' THEN 'test' ELSE 'test2' END WHERE smalltableid = 1
  COMMIT TRAN
  SET @withTran = @withTran + DATEDIFF(MICROSECOND, @date, GETDATE())

  SET @count = @count + 1
END

SELECT 
  @noTran / 1000000. AS Seconds_NoTransaction, 
  @withTran / 1000000. AS Seconds_WithTransaction

Seconds_NoTransaction    Seconds_WithTransaction
2.63200000               2.70400000
2.16700000               2.12300000

颠倒更新顺序保持相同的行为

Answer 2

在您发布的代码中，不会有可测量的影响，但事务确实会对性能产生影响，它们可以由于日志刷新提交分组而显着提高性能，或者由于管理不正确的争用问题而显着降低性能。但最重要的是，当需要交易来保证正确性时，你不能跳过它们。话虽如此，相对于事务和 try-catch 块，您的模板实际上非常糟糕。 catch 块中的事务必须对 XACT_STATE< 进行三态逻辑检查/code>返回值 (-1, 0, 1) 并正确处理注定失败的交易。有关示例，请参阅异常处理和嵌套事务。

另外，您永远不应该将 try-catch 错误处理与返回代码错误处理混合在一起。选择一个并坚持下去，最好是尝试捕捉。换句话说，您的存储过程应该RAISE，不返回-1。将异常与错误代码混合在一起会使您的代码维护和正确调用成为一场噩梦。

Answer 3

TL;DR - 包含两个处理 3300 万条记录的选择查询的存储过程在没有事务的情况下花了 45 秒执行，在有事务的情况下花了 48 秒。

免责声明：我写了一个存储过程大约 4 个小时，并找到了这个问题的一个可衡量的答案（注意：这并不是那么重要！）由于我正在处理的数据的敏感性，查询逻辑中的间隙被故意省略。

方法：此过程是使用两个查询开发的 - 一个执行大部分繁重的工作，另一个自行计算一个附加字段，因此它不会尝试计算超出需要的字段。我将其分为两个步骤：

1）我将 2 个公用表表达式和 1 个 SQL SELECT 写入临时表，然后再次查询。我必须这样做，因为要求我实现几个标量值函数，否则这些函数将尝试在超过 3300 万条记录而不是 355 条记录上运行该函数。2

）我在第一个查询之后附加了一个标量值函数，因此它没有尝试查找 3000 万条记录（如果你关心的话，这会产生巨大的差异）。

查询：出于读者的目的，我删除了大部分查询（案例语句）。

CREATE PROC GET_PAYMENT_SUMS_BY_CLIENT
AS

--Required for reporting in a specific Business Intelligence later; Optional
SET FMTONLY OFF; 

BEGIN TRANSACTION

--Query 1
--This CTE checks over 30 million records
WITH CTE1 AS(
SELECT CASE VARIABLE
--170 case conditions go here
END AS TheType, 
Amount,
PK1 FROM TABLE1),

--THIS CTE Pivots the sums to get the data in the style I want it in
CTE2 AS(
SELECT PK1, [PIVOT1], [PIVOT2], [PIVOT3]
FROM
  (SELECT * FROM CTE1) AS BaseTable --Alias was just to get it to execute
)
PIVOT(
  SUM(Amount)
FOR TheType IN ([PIVOT1], [PIVOT2], [PIVOT3])
        ) AS PivotTable
    )
 )

SELECT TABLE2.NAME, CTE2.* INTO #TEMPORARY_TABLE 
FROM CTE2 
JOIN TABLE2 ON CTE2.PK1 = TABLE2.PK2

--Query 2
--Written to force the function to look at 355 records instead of 33 million
SELECT *, dbo.SCALAR_VALUED_FUNCTION(PK2) FROM #TEMPORARY_TABLE

COMMIT TRANSACTION

调查结果：
使用事务 - 如果使用事务逻辑，则该集合的结果查询需要 48 秒来处理包含 170 行的 case 语句中的超过 3300 万条记录，按总和旋转数据，将数据放入临时表中，然后附加第一个查询运行后的标量值函数。

没有事务 - 如果代码中的注释行保留注释，则上述所有步骤都将在 45 秒内完成。这比使用事务块快大约 7%：3/45 = 0.0666.... 快 7%。

结论：
虽然我的努力无法告诉您对 10 条记录执行相同的查询是否会产生相同比例的差异，但它可以告诉您，当您开始处理更大的数据集和/或更复杂的查询时，它开始变得更加重要。

我知道这些信息对那里的某人有用！