一些插入记录的方法比较

一些插入记录的方法比较

有同事写了个工具，对C#中，两种插入记录到SQL SERVER数据库的方法进行了比较，发现二者的性能相差天与地：

1、SqlBulkCopy，批量插入方式，5万条记录，仅花4秒

2、拼凑出一条条SQL语句，单条插入，5万条记录，耗时7分钟

//方式一，批量插入

using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection)) { sqlBulkCopy.DestinationTableName = dataTable.TableName; sqlBulkCopy.BatchSize = 100000; // 一次批量的插入的数据量 sqlBulkCopy.BulkCopyTimeout = 360; // 自定义的DataTable和数据库的字段进行对应 foreach (DataColumn column in dataTable.Columns) { sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName); } sqlBulkCopy.WriteToServer(dataTable); }

//方式二，单条插入

sqlString = "INSERT INTO Table2 (Int,Long,Float,Real,Date,NText,String1,String2,String3,String4) VALUES (@Int,@Long,@Float,@Real,@Date,@NText,@String1,@String2,@String3,@String4)"; IDataParameter[] parameters = new IDataParameter[10]; int count = Convert.ToInt32(txtCount.Text); for (int i = 0; i < count; i++) { parameters[0] = DBHelper.CreateParameter("Int", i); parameters[1] = DBHelper.CreateParameter("Long", i * 10); parameters[2] = DBHelper.CreateParameter("Float", i * 100 + 0.33); parameters[3] = DBHelper.CreateParameter("Real", i * 1000 + 0.999); parameters[4] = DBHelper.CreateParameter("Date", DateTime.Now); parameters[5] = DBHelper.CreateParameter("NText", DateTime.Now.ToLongTimeString()); parameters[6] = DBHelper.CreateParameter("String1", "String1_" + i.ToString()); parameters[7] = DBHelper.CreateParameter("String2", "String2_" + (i * 100 + 0.33).ToString()); parameters[8] = DBHelper.CreateParameter("String3", "String3_" + (i * 1000 + 0.999).ToString()); parameters[9] = DBHelper.CreateParameter("String4", "String4_" + DateTime.Now.ToShortDateString() + " _ " + DateTime.Now.ToLongDateString()); DBHelper.ExecuteNonQuery(connection, CommandType.Text, sqlString, parameters); } connection.Close();

为什么二者相差会这么大呢？

类似方式二的方式其实我也经常使用，数据库连接串在循环开始前已经打开，且只打开一次，循环结束再关闭，看起来无可挑剔。

不过我一直怀疑用代码来操作数据库，速度比不上直接在数据库里操作快。所以今天特地在数据库运行以下语句

3、方式三，直接在数据库运行循环插入

use [test]goTRUNCATE TABLE table2;declare @i intset @i = 0;while @i < 50000BEGININSERT INTO [dbo].[Table2] ([Int] ,[Long] ,[Float] ,[Real] ,[Date] ,[NText] ,[String1] ,[String2] ,[String3] ,[String4]) VALUES (0 ,0 ,0.33 ,0.999 ,'2012-10-10 15:23:01.433' ,'15:23:01' ,'String1_0 ' ,'String2_0.33 ' ,'String3_0.999 ' ,'String4_2012-10-10 _ 2012年10月10日 ' ); SET @i = @i + 1;end;

结果时间也是7分多，跟在C#里操作不相上下！

恰好同事这样告诉我：

今天查网上的文章，为啥微软提供批量写入和单条插入性能差异这么大

结果是，批量是直接复制文件方式进行写入，因此各服务器差不多

单条是直接反映数据库读写性能表现，每插一条需要遍历整个表然后才插入

因此快慢是真实反映数据库读写性能数据的速度。

插一条记录需要遍历整个表？这种说法我是第一次听到，将信将疑。

方式三这里，是每插入一条记录就commit一次事务的，是这里耗了时间吧？但是SQL SERVER写数据时，是不直接写硬盘的，而是修改内存中的数据，然后定期check in，将改动写到磁盘，或者有什么操作触发了会用到这些脏数据（dirty data），也不得不写回到硬盘。既然如此，平常的commit数据会这么消耗资源吗？

不清楚。但可以试一下。

将方式三稍加修改，循环外显示加上事务，这样子应该会取消了循环体内，原先默认的每插入一条就提交一次事务的设置：

use [test]goTRUNCATE TABLE table2;declare @i intset @i = 0;BEGIN TRAN;while @i < 50000BEGININSERT INTO [dbo].[Table2] ([Int] ,[Long] ,[Float] ,[Real] ,[Date] ,[NText] ,[String1] ,[String2] ,[String3] ,[String4]) VALUES (0 ,0 ,0.33 ,0.999 ,'2012-10-10 15:23:01.433' ,'15:23:01' ,'String1_0 ' ,'String2_0.33 ' ,'String3_0.999 ' ,'String4_2012-10-10 _ 2012年10月10日 ' ); SET @i = @i + 1;end;COMMIT TRAN;

结果性能获得飞跃，5万条记录只有4秒！

其实这也可以通过方式4来加以印证

4、方式4

建立一个与table2相同结构的表table1，然后直接INSERT ... SELECT...

TRUNCATE TABLE table1;INSERT INTO [test].[dbo].[Table1] ([Int] ,[Long] ,[Float] ,[Real] ,[Date] ,[NText] ,[String1] ,[String2] ,[String3] ,[String4])SELECT [Int] ,[Long] ,[Float] ,[Real] ,[Date] ,[NText] ,[String1] ,[String2] ,[String3] ,[String4] FROM [test].[dbo].[Table2]

结果也是4秒，不相伯仲。

方式一里面，SqlBulkCopy，是将一个DataTable对象导入到数据库中，导入前，直接编辑这个DataTable就行了，比较方便。

另外，这个方法类似于SQL SERVER里面的BULK INSERT，而BULK INSERT在数据库为简单模式的情况下，是不产生日志的，所以这个SqlBulkCopy，也就是方式一应该是最快的。

SqlBulkCopy这个方法我觉得有点类似使用SqlDataAdapter，以往使用经验中，这个操作也比单条插入要快，但那时不知道是什么原因。

其实现在也不清楚是什么原因啊！

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。