SQLServer批量插入数据的三种方式及性能对比

SQLServer批量插入数据的三种方法及性能对比和两位同事交流，才有了这篇文章。公司技术背景：数据库访问类（xxx.DataBase.Dll）调用存储过程实现数据库访问。技术方案一：程序员在压缩时间下编写的第一个版本只是为了完成任务，程序中没有做任何优化。实现方法是使用数据库访问类调用存储过程，使用循环逐条插入。显然，这种方式效率不高，所以之前的两位同事讨论了效率低下的问题。技术方案二：考虑到批量插入大量数据，想到了ADO.NET2.0的一个新特性：SqlBulkCopy。关于这个的性能，我很早就亲自做过性能测试，效率很高。这也是我推荐给公司同事的技术方案。技术方案三：利用SQLServer2008的新特性——表值参数（Table-ValuedParameter）。表值参数是SQLServer2008的一个新特性。利用这个新特性，我们可以将表类型作为参数传递给函数或存储过程。但是，它也有一个特点：表值参数在插入少于1000行时表现良好。技术方案四：对于单列字段，可以将待插入的数据拼接成字符串，最后在存储过程中拆分成数组，然后逐一插入。在存储过程中查看参数字符串的最大长度，然后除以字段长度计算出一个值，显然满足要求，但是这种方法和第一种方法相比似乎并没有提高，因为原理是一样的。技术方案五：考虑异步创建、消息队列等。该方案在设计和开发上都存在一定难度。技术方案1肯定会被否决，剩下的就是在技术方案2和技术方案3中做出选择。鉴于公司目前的情况，技术方案4和技术方案5将不再考虑。接下来为了让大家对表值参数的创建和调用有一个更感性的认识，我会写的比较详细，文章可能会稍微长一些。不注重细节的朋友可以选择跳读式阅读。再来说说测试计划。测试共分为三组。一组是插入次数小于1000，另外两组是插入次数大于1000（这里我们分别取10000和1000000）。每组测试分为10次。平均值。我明白怎么做了，走吧！1.创建一个表。为了简单起见，表中只有一个字段，如下图所示：2.创建一个表值参数类型我们打开查询分析器，然后在查询分析器中执行如下代码：CreateTypePassportTableTypeasTable(PassportKeynvarchar(50))执行成功后，我们打开企业管理器依次展开以下节点-数据库，展开可编程性，类型，用户自定义表类型，可以看到我们创建的表值类型，如图如下图：说明我们成功创建了值类型的表。3、编写存储过程存储过程的代码为：USE[TestInsert]GO/******Object:StoredProcedure[dbo].[CreatePassportWithTVP]ScriptDate:03/02/201000:14:45******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGO——==============================================--作者：--创建日期：<2010-3-1>--描述：--==============================================创建过程[dbo].[CreatePassportWithTVP]@TVPPassportTableTypereadonlyASBEGINSETNOCOUNTON;InsertintoPassport(PassportKey)selectPassportKeyfrom@TVPEND可能在查询分析器中，智能提示会提示表值类型有问题，会出现红色下划线（见下图），无视，继续运行我们的代码，完成存储过程的创建4.编写调用存储过程的代码。三种数据库插入方式的代码如下。由于时间紧迫，代码可能不是那么好读。我在特殊代码中添加了一些注释。使用系统；使用System.Diagnostics；使用System.Data；使用System.Data.SqlClient；使用com.DataAccess；命名空间ConsoleAppInsertTest{classProgram{staticstringconnectionString=SqlHelper.ConnectionStringLocalTransaction;//数据库连接字符串staticintcount=1000000;//条目数staticvoidMain(string[]args){//longcommonInsertRunTime=CommonInsert();//Console.WriteLine(string.Format("正常插入{1}条数据耗时{0}毫秒",commonInsertRunTime,count));长sqlBulkCopyInsertRunTime=SqlBulkCopyInsert();Console.WriteLine(string.Format("使用SqlBulkCopy插入{1}条数据需要{0}毫秒",sqlBulkCopyInsertRunTime,count));长TVPInsertRunTime=TVPInsert();Console.WriteLine(string.Format("使用表值方式插入{1}条数据需要{0}毫秒(TVP),TVPInsertRunTime,count));}///