sqlserver为何事务提交反而更快？
0
悬赏园豆：5
[已解决问题]
解决于 2020-04-28 10:13
begin print CONVERT(varchar(100), GETDATE(), 21) declare @i int set @i=1000000 while(@i>0) begin begin tran insert into [dbo].[TextMessage](info)values(CAST(@i as varchar)) insert into [dbo].[Course](Name,DisplayOrder)values(CAST(@i as varchar),@i) commit set @i=@i-1 end print CONVERT(varchar(100), GETDATE(), 21) end
1W行数据提交时间
2015-07-25 11:00:17.803
2015-07-25 11:00:19.067
2
10W数据提交时间
2015-07-25 11:01:33.893
2015-07-25 11:01:45.960
12
100W数据提交时间
2015-07-25 11:05:37.653
2015-07-25 11:07:45.390
2：08
begin print CONVERT(varchar(100), GETDATE(), 21) declare @i int set @i=1000000 while(@i>0) begin insert into [dbo].[TextMessage](info)values(CAST(@i as varchar)) insert into [dbo].[Course](Name,DisplayOrder)values(CAST(@i as varchar),@i) set @i=@i-1 end print CONVERT(varchar(100), GETDATE(), 21) end
1W行数据提交时间
2015-07-25 11:01:00.263
2015-07-25 11:01:05.797
5
10W数据提交时间
2015-07-25 11:02:14.293
2015-07-25 11:02:39.437
25
100W数据提交时间
2015-07-25 11:09:33.933
2015-07-25 11:13:12.920
3：39
最佳答案
0
我说下我的理解吧。首先这两个没有数量级的差别，可以认为基本相同，为什么说相同呢。因为你这里用事务也没用事务区别不大，因为没有其它来竞争资源。反正只有一个连接在操作这两个表，没有其它的操作。
其次，在这种没有其它操作的情况下，用事务只提交一次事务，如果不用事务反正会提交两次，所以感觉应该是用一个事务还就稍微快一点。
下面是我做的测试两组数据
-- 1W 1 4450
-- 1W 2 4583
-- 有其它操作
-- 1W 1 4550 --10ms间隔
-- 1W 2 4636 --无间隔
-- 1W 3 5116 --无间隔事务
-- 1W 4 5290 --10ms间隔事务
-- 1W 1 4986 -- 一条记录（没有开其它连接，只是把操作变成一条insert，也就是事务中只有一条）
---------------------------------------上面这一组是事务操作
--1W 1 4790
--1W 2 4930
--有影响 （另外开连接操作两个表）
-- 1W 1 5193 --10ms间隔
-- 1W 2 6356 --无间隔
-- 1W 3 8733 --无间隔事务
-- 1W 4 6260 --10ms间隔事务
-- 1W 1 2863 -- 一条记录（没有开其它连接，只是把操作变成一条insert）
--------------------------------------这一组是非事务操作
1.先看前面两次测试，确实事务效果还会好一些。这里就是上面说的两条分开操作可能提交两次，会有一些处理是两次。而事务有些处理是一次完成的，所以反而更快。再结合最后一条记录看，把事务里面变成一条，两个比较可以看出事务时间更长，说明事务做了其它的一些处理，所以更慢。
2.看下面4次测试，在外部有其它操作表的时候，我是插入这两个表记录可以看出 事务的情况在有其它的操作情况下，时间是越来越长。而非事务操作的影响就不一样了，先是变长，然后变小，说明大事务对这种无事务操作的影响会越来越小。虽说总的时间事务还是要短一些，但是这里同我的测试数据有关，比如再复杂一些，主要这里是用来看一个趋势。
收获园豆：5
其他回答(1)
0
你没有加事务，可能SQL Sever自动加了事务（autocommit）。
建议通过 SET IMPLICIT_TRANSACTIONS OFF 关闭自动事务试试。
根据你的建议我重新执行了下面的代码
begin SET IMPLICIT_TRANSACTIONS OFF print CONVERT(varchar(100), GETDATE(), 21) declare @i int set @i=1000000 while(@i>0) begin insert into tb5(info)values(CAST(@i as varchar)) insert into tb6(info,DisplayOrder)values(CAST(@i as varchar),@i) set @i=@i-1 end print CONVERT(varchar(100), GETDATE(), 21) end
执行完的时间与不加SET IMPLICIT_TRANSACTIONS OFF 差别不大
为了让数据更准确一些，我重新创建了四张表tb1,tb2,tb3,tb4。 tb1=tb3,tb2=tb4。
加事务执行插入tb1，tb2操作，不加事务执行插入tb3，tb4操作
分别对1W，10W，100W的数据量进行测试，并且每个数据量执行三次，取平均值。结果如下：
1w 显示事务操作运行平均时间2秒，非事务操作运行平均时间5秒
10W 显示事务操作运行平均时间29秒，非事务操作运行平均时间50秒
100W 显示事务操作运行平均时间294秒，非事务操作运行平均时间525秒
（此次测试的时间比上面的时间要长一些，应该是我创建tb1,tb2,tb3,tb4的时候加上了聚集索引，而之前测试时用的表为堆表）
原本看到你的回复，我想有可能是因为，显示事务每两行数据提交一次，而隐式事务是100W行数据一次提交，所以导致的隐式事务比显示花费的时间更长。但是测试完的数据让我很不解。我猜一定是我安装sqlserver的方式不对？
@月下*独: 这篇文章解释了为什么显式事务插入会更快——Performance benchmarking: Explicit vs Implicit Transactions。
The reason for this difference is that for every insert in Test 1 is followed by an autocommit. This means that you need to flush the log buffer for each insert. However in Test 2, the log buffer was being flushed much lesser but at the same time more data was being written to the disk for each log flush.
虽然后来加了 SET IMPLICIT_TRANSACTIONS OFF ，可能没起作用，执行时SQL Server还是进行了autocommit。