澳门金沙vip 1

澳门金沙vipSQLServer中批量插入数据方式的性能对比

复制代码 代码如下: using System.Data;
using System.Diagnostics; using System.Data.SqlClient; string
connectionString = “Data Source=HG-J3EJJ9LSW5PY;Initial
Catalog=Test;User ID=sa;password=hg”; DataTable dataTable =
sql_.select_datagrid(” select a from large where 1=0 “).Tables[0];
string passportKey; for (int i = 0; i 100000; i++) { passportKey =
Guid.NewGuid().ToString(); DataRow dataRow = dataTable.NewRow();
dataRow[0] = passportKey; dataTable.Rows.Add(dataRow); } SqlConnection
sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open(); SqlTransaction sqltran =
sqlConnection.BeginTransaction(); SqlBulkCopy sqlBulkCopy = new
SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.KeepIdentity, sqltran);
sqlBulkCopy.DestinationTableName = “large”; sqlBulkCopy.BatchSize =
dataTable.Rows.Count; if (dataTable != null && dataTable.Rows.Count !=
0) { sqlBulkCopy.WriteToServer(dataTable); } sqlBulkCopy.Close();
sqltran.Rollback(); sqlConnection.Close(); 注解: 1
sqlBulkCopy.DestinationTableName = “large”; large 指的是目标表的名称 2
DataTable 的结构要和数据库中的表的结构相同
(DataTable的列不能多于数据库里面的) (DataTable的列可以少于数据库里面的
如果数据库这一列有默认值的话) 3 这里面 我使用了事务 您在使用的时候
也可以不用事务

经过测试 10万条记录 需要1.5-1.7秒

4.编写代码调用存储过程。

      <3> 这里面 我使用了事务  您在使用的时候 也可以不用事务
 
 

第三组测试,插入记录数1000000

             (DataTable的列不能多于数据库里面的)

由于是考虑到大数据量的批量插入,于是我想到了ADO.NET2.0的一个新的特性:SqlBulkCopy。有关这个的性能,很早之前我是亲自做过性能测试的,效率非常高。这也是我向公司同事推荐的技术方案。

目标数据库只能是Sqlserver

            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

       string connectionString =
“Data Source=HG-J3EJJ9LSW5PY;Initial
Catalog=Test;User ID=sa;password=hg”;
        DataTable dataTable =
sql_.select_datagrid(” select a 
from large where 1=0 “).Tables[0];
        string passportKey;
        for (int i
= 0; i
< 100000;
i++)
        {
            passportKey =
Guid.NewGuid().ToString();
            DataRow dataRow =
dataTable.NewRow();
            dataRow[0] =
passportKey;
         
            dataTable.Rows.Add(dataRow);
        }
        SqlConnection sqlConnection =
new SqlConnection(connectionString);
        sqlConnection.Open();
        SqlTransaction sqltran =
sqlConnection.BeginTransaction();
        SqlBulkCopy sqlBulkCopy =
new SqlBulkCopy(sqlConnection,
SqlBulkCopyOptions.KeepIdentity, sqltran);
        sqlBulkCopy.DestinationTableName =
“large”;
        sqlBulkCopy.BatchSize =
dataTable.Rows.Count;
       
       
        if (dataTable !=
null &&
dataTable.Rows.Count != 0)
        {
            sqlBulkCopy.WriteToServer(dataTable);
        }
        sqlBulkCopy.Close();

            long sqlBulkCopyInsertRunTime = SqlBulkCopyInsert();
            Console.WriteLine(string.Format(“使用SqlBulkCopy插入{1}条数据所用的时间是{0}毫秒”, sqlBulkCopyInsertRunTime, count));

             (DataTable的列可以少于数据库里面的
如果数据库这一列有默认值的话)

namespace ConsoleAppInsertTest
{
    class Program
    {
        static string connectionString = SqlHelper.ConnectionStringLocalTransaction;    //数据库连接字符串
        static int count = 1000000;           //插入的条数
        static void Main(string[] args)
        {
            //long commonInsertRunTime = CommonInsert();
澳门金沙vip,            //Console.WriteLine(string.Format(“普通方式插入{1}条数据所用的时间是{0}毫秒”, commonInsertRunTime, count));

        sqltran.Rollback();
        sqlConnection.Close();

            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

注解: <1> sqlBulkCopy.DestinationTableName = “large”;    large
指的是目标表的名称

Create Type PassportTableType as Table
(
PassportKey nvarchar(50)

)

来源数据库 无所谓 只要能用ado.net 将来源数据读取到Dataset或者Datareader
中就可以

接下来,为了让大家对表值参数的创建跟调用有更感性的认识,我将写的更详细些,文章可能也会稍长些,不关注细节的朋友们可以选择跳跃式的阅读方式。

       <2>  DataTable 的结构要和数据库中的表的结构相同

        private static long TVPInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;      

比较神秘的代码其实就下面这两行,该代码是将一个dataTable做为参数传给了我们的存储过程。简单吧。

   

澳门金沙vip 1

只是插入一列 4列的话 需要3秒 (我的机器是4G内存)

执行成功以后,我们打开企业管理器,按顺序依次展开下列节点–数据库、展开可编程性、类型、用户自定义表类型,就可以看到我们创建好的表值类型了如下图所示:

            long TVPInsertRunTime = TVPInsert();
            Console.WriteLine(string.Format(“使用表值方式(TVP)插入{1}条数据所用的时间是{0}毫秒”, TVPInsertRunTime, count));
        }

    }
}

3.编写存储过程

三种数据库的插入方式代码如下,由于时间比较紧,代码可能不那么易读,特别代码我加了些注释。

USE [TestInsert]

GO
/****** Object: StoredProcedure [dbo].[CreatePassportWithTVP] Script Date: 03/02/2010 00:14:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    <Kevin>
-- Create date: <2010-3-1>
-- Description:   <创建通行证>
-- =============================================
Create PROCEDURE [dbo].[CreatePassportWithTVP] 

@TVP PassportTableType readonly

AS
BEGIN
SET NOCOUNT ON;

Insert into Passport(PassportKey) select PassportKey from @TVP

END

可能在查询分析器中,智能提示会提示表值类型有问题,会出现红色下划线(见下图),不用理会,
继续运行我们的代码,完成存储过程的创建

澳门金沙vip 2澳门金沙vip 3主要部分的代码