一些插入记录的方法比较

有同事写了个工具，对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，以往使用经验中，这个操作也比单条插入要快，但那时不知道是什么原因。

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