搞定大量数据插入Sql Server,测试最快可达1万(日志设为简单)

终于搞定了中继的数据采集,本以为一天搞定的事情,足足让我搞了3天有余,部分时间是被其他事情耽搁了,主要原因是以前使用的插入数据库数据的方式有问题。

之前,按照一个大哥的说法,使用存储过程操作数据表,可能会防止数据表死锁的问题,所以,所有的数据操作全部封装为存储过程来进行处理,包括:数据对象的insert操作,小数量数据的时候,也没有多大感觉,但是现在插入的中继信息居然达到了300万的数量,按照原来的方法,每秒大概只能插入200数据,总用时15000秒,我靠,半天时间玩完了,多线程插入也没用,速度差不对。

无解,只能想办法提高采集速度,先把程序处理逻辑进行调整,还是没有多大提高,毕竟数据插入太慢了,只得想办法提高数据插入的操作,经过了解发现原因是,每次存储过程会自动完成一个事务操作,而每个事物要进行日志的写入,估计还有效验等工作,简单的做法就是将多个插入SQL语句组合在一个数据中,减少对日志的操作,发现效率提高的不错,基本能达到1800条每秒。

具体做法:

1.如果不是必须,把数据库的恢复模式改为“简单”

2.使用如下的方式插入数据:最好直接作为一条SQL语句直接执行,但是不要写太多条进去,貌似有人说大于1000了,应该有慢下来的,我每次插入20性能已经相当不错,所以就先这样把任务完成先,以后再研究最好的方法。

BEGIN TRANSACTION INSERT ... INSERT ... INSERT ... COMMIT TRANSACTION

具体实现方法比较:

  1. 将一次要提交的SQL语句,无论多少条Insert全部组合成为一条SQL语句,以BEGIN TRAN;开始,以COMMIT TRAN;结束。这样的操作速度最快可达1万/秒。
  2. 跟上面方法一致,但是每条SQL语句执行一次ExecSQL,第一次ExecSQL('BEGIN TRAN'),最后ExecSQL('Commit TRAN'),这样操作数据一般,性能提高微弱,目前我测试效率为1500条/秒左右。 附加心得:

  3. 对于Delphi程序员,有一个严重提醒,不要使用SQL.add();方法进行sql生成,如果第一种方法使用sql.add组合出来一个大的SQL语句,实际上比执行插入操作都慢,导致系统瓶颈,尤其是一条长的SQL语句,多次add,性能之差,不可想象。还是使用String来进行拼接,之后赋值给sql.text为好。

  4. 如果使用上面第二种方法,在没有“COMMIT TRAN”,插入一个存储过程的执行,不影响整体事物。(我的程序执行数据插入时候是批量调用对象的Insert方法,因为有很多对象类型不同,其Insert方法也不同,所以需要对此进行测试,发现这样做不影响数据插入) 就是这么简单。哈哈。