文章标签 ‘事务’
终于搞定了中继的数据采集，本以为一天搞定的事情，足足让我搞了3天有余，部分时间是被其他事情耽搁了，主要原因是以前使用的插入数据库数据的方式有问题。 之前，按照一个大哥的说法，使用存储过程操作数据表，可能会防止数据表死锁的问题，所以，所有的数据操作全部封装为存储过程来进行处理，包括：数据对象的insert操作，小数量数据的时候，也没有多大感觉，但是现在插入的中继信息居然达到了300万的数量，按照原来的方法，每秒大概只能插入200数据，总用时15000秒，我靠，半天时间玩完了，多线程插入也没用，速度差不对。 无解，只能想办法提高采集速度，先把程序处理逻辑进行调整，还是没有多大提高，毕竟数据插入太慢了，只得想办法提高数据插入的操作，经过了解发现原因是，每次存储过程会自动完成一个事务操作，而每个事物要进行日志的写入，估计还有效验等工作，简单的做法就是将多个插入SQL语句组合在一个数据中，减少对日志的操作，发现效率提高的不错，基本能达到1800条每秒。 具体做法： 1.如果不是必须，把数据库的恢复模式改为“简单” 2.使用如下的方式插入数据：最好直接作为一条SQL语句直接执行，但是不要写太多条进去，貌似有人说大于1000了，应该有慢下来的，我每次插入20性能已经相当不错，所以就先这样把任务完成先，以后再研究最好的方法。 BEGIN TRANSACTION INSERT … INSERT … INSERT … COMMIT TRANSACTION 具体实现方法比较： 将一次要提交的SQL语句，无论多少条Insert全部组合成为一条SQL语句，以BEGIN TRAN;开始，以COMMIT TRAN;结束。这样的操作速度最快可达1万/秒。 跟上面方法一致，但是每条SQL语句执行一次ExecSQL，第一次ExecSQL(‘BEGIN TRAN’),最后ExecSQL(‘Commit TRAN’),这样操作数据一般，性能提高微弱，目前我测试效率为1500条/秒左右。 附加心得： 对于Delphi程序员，有一个严重提醒，不要使用SQL.add();方法进行sql生成，如果第一种方法使用sql.add组合出来一个大的SQL语句，实际上比执行插入操作都慢，导致系统瓶颈，尤其是一条长的SQL语句，多次add，性能之差，不可想象。还是使用String来进行拼接，之后赋值给sql.text为好。 如果使用上面第二种方法，在没有“COMMIT TRAN”，插入一个存储过程的执行，不影响整体事物。（我的程序执行数据插入时候是批量调用对象的Insert方法，因为有很多对象类型不同，其Insert方法也不同，所以需要对此进行测试，发现这样做不影响数据插入） 就是这么简单。哈哈。
简单的优化方法： 1.将数据库恢复模式改为简单，基本能提高一倍速度。 自己测试获得的结果： 1.使用SET IMPLICIT_TRANSACTIONS ON可以强制进行事务提交，不执行Commit不能提交数据 2.如果使用存储过程插入数据，上面的命令无效，每秒大概1350条（20万大概2.5分钟） 3.使用delphi+ado+存储过程插入数据大概速度是每秒1350条左右，使用第一种能够提高到3000左右（20万1分14），是偶自己的测试。 4.使用查询管理器执行存储过程，大概3200条左右，比上秒稍快。 5.使用第一种方法，数据插入过程中，会造成锁表导致其他数据操作不能进行。 6.使用第一种方法，直接在Sql server查询管理器，可以达到20000条的速度 7.按照6方法执行过后，会影响之后4的速度，使4的速度提高到6低点的水平，不知道为什么。 测试不太精确，大概意思可以得到，如果强制使用事务提交，可以增加插入速度2倍多，但是事务处理过程中，会锁表，影响其他线程数据插入，对于多线程有些问题。 偶也不是DBA，所以只能做些粗略的测试，看起来偶的程序，主要瓶颈不是在与数据存储（尚未测试多线程插入是不是会更慢）。 select getdate() DECLARE @i int,@return_value int,@ReturnValue int set @i=0 BEGIN TRAN while(@i<200000) begin INSERT INTO [ZhongJiQun]([FNetWorkName],[FZhongJiQunMing],[FZhongJiQunHao],[FStatus],[FBenDuanXinLingDian],[FDuiDuanXinLingDian],[FJuXiangMing],[FJuXiangHao],[FQiShiCic],[FJieShuCic],[FZhongJiQunFangXiang],[FDianLuTuoPuLeiXing],[FDaiKuan],[FLeiXing],[FVMGW])VALUES(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null) set @i=@i+1 end commit TRAN select getdate() select count(*) from zhongjiqun EXEC @return_value = [dbo].[proc_cj_Insert_ZhongJiQun] @FNetWorkName = ‘eee’, @FZhongJiQunMing = ‘eee’, @FZhongJiQunHao = ‘eee’, [...]