大数据量插入

这段时间在做抓包项目，需要把抓来的数据插入到数据库中，特做了四种插入数据算法比较。

数据库：SQL Server Management Studio Express

数据库名：test

Test中的表：TBL1(id bigint,name nvarchar(50),sex nvarchar(10),remark nvarchar(50)) 这张表就是要插入的数据

开发语言：vb.net 2008

插入算法1：考虑到插入的数据非常多，我有可能会保存在多个表中。该算法使用存储过程中执行动态sql。使用该算法插入10万条数据，共花了68.684秒。

 1ALTER PROCEDURE [dbo].[tbl1_insert_1]
 2    @id bigint,@name nvarchar(50)  ,
 3    @sex nvarchar(10),@remark nvarchar(50)
 4AS
 5BEGIN
 6SET NOCOUNT ON;
 7declare @sql nvarchar(1000)
 8declare @tblname varchar(100)
 9set @tblname=’tbl1’
set @sql='insert into ‘+@tblname +’(id,[name],sex,remark) '+
        'values(' +convert(nvarchar, @id) +','''+@name+''','''+@sex+''','''+@remark+''')'
 exec(@sql)
END

 1 Private Sub Button4_Click()Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
 2        Dim sw As New System.Diagnostics.Stopwatch
 3        sw.Start()
 4        Dim conn As New System.Data.SqlClient.SqlConnection("Data Source=JXBW-YESHUNQUAN\SQLEXPRESS;Initial Catalog=test;Integrated Security=True")
 5        conn.Open()
 6        Dim cmd As New System.Data.SqlClient.SqlCommand
 7        cmd.CommandText = "tbl1_insert_1"
 8        cmd.CommandType = CommandType.StoredProcedure
 9        cmd.Connection = conn
        Dim para(3) As System.Data.SqlClient.SqlParameter
        para(0) = New System.Data.SqlClient.SqlParameter("@id", SqlDbType.BigInt)
        para(1) = New System.Data.SqlClient.SqlParameter("@name", SqlDbType.NVarChar, 50)
        para(2) = New System.Data.SqlClient.SqlParameter("@sex", SqlDbType.NVarChar, 10)
        para(3) = New System.Data.SqlClient.SqlParameter("@remark", SqlDbType.NVarChar, 50)
        cmd.Parameters.Add(para(0))
        cmd.Parameters.Add(para(1))
        cmd.Parameters.Add(para(2))
        cmd.Parameters.Add(para(3))
        For i = 1 To 100000
            para(0).Value = i
            para(1).Value = "gsdgfsdfg" & i
            para(2).Value = "男"
            para(3).Value = "adfasdfasdddsssssssssssssssss我爱中国"
            cmd.ExecuteNonQuery()
        Next
        conn.Close()
        sw.Stop()
        Debug.Print(sw.ElapsedMilliseconds / 1000 & "秒")
    End Sub

插入算法2：用存储过程，只保存一张表，性能会是怎样呢？同样插入10万条数据，共花了 48.896秒，比算法1快20秒。

ALTER PROCEDURE [dbo].[tbl1_insert]
    @id bigint, @name nvarchar(50)  ,
    @sex nvarchar(10),@remark nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;
insert into tbl1(id,[name],sex,remark) values(@id,@name,@sex,@remark)
END

 

 1Private Sub Button2_Click()Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
 2        'tbl1_insert
 3        Dim sw As New System.Diagnostics.Stopwatch
 4        sw.Start()
 5        Dim conn As New System.Data.SqlClient.SqlConnection("Data Source=JXBW-YESHUNQUAN\SQLEXPRESS;Initial Catalog=test;Integrated Security=True")
 6        conn.Open()
 7        Dim cmd As New System.Data.SqlClient.SqlCommand
 8        cmd.CommandText = "tbl1_insert"
 9        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = conn
        Dim para(3) As System.Data.SqlClient.SqlParameter
        para(0) = New System.Data.SqlClient.SqlParameter("@id", SqlDbType.BigInt)
        para(1) = New System.Data.SqlClient.SqlParameter("@name", SqlDbType.NVarChar, 50)
        para(2) = New System.Data.SqlClient.SqlParameter("@sex", SqlDbType.NVarChar, 10)
        para(3) = New System.Data.SqlClient.SqlParameter("@remark", SqlDbType.NVarChar, 50)
        cmd.Parameters.Add(para(0))
        cmd.Parameters.Add(para(1))
        cmd.Parameters.Add(para(2))
        cmd.Parameters.Add(para(3))
        For i = 1 To 100000
            para(0).Value = i
            para(1).Value = "gsdgfsdfg" & i
            para(2).Value = "男"
            para(3).Value = "adfasdfasdddsssssssssssssssss我爱中国"
            cmd.ExecuteNonQuery()
        Next
        conn.Close()
        sw.Stop()
        Debug.Print(sw.ElapsedMilliseconds / 1000 & "秒")
    End Sub

插入算法3：不使用存储过程，直接使用command插入会怎样呢？同样插入10万条数据，共花了61.486秒，比算法1快，比算法2慢。看来在存储过程中尽量不要做比较复杂的业务。

 1Private Sub Button3_Click()Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
 2        Dim sw As New System.Diagnostics.Stopwatch
 3        sw.Start()
 4        Dim conn As New System.Data.SqlClient.SqlConnection("Data Source=JXBW-YESHUNQUAN\SQLEXPRESS;Initial Catalog=test;Integrated Security=True")
 5        conn.Open()
 6        Dim cmd As New System.Data.SqlClient.SqlCommand
 7        cmd.CommandType = CommandType.Text
 8        cmd.Connection = conn
 9        For i = 1 To 100000
            cmd.CommandText = "insert into tbl1(id,[name],sex,remark) values(" & i & ",'gsdgfsdfg" & i & "','男','adfasdfasdddsssssssssssssssss我爱中国')"
            cmd.ExecuteNonQuery()
        Next
        conn.Close()
        sw.Stop()
        Debug.Print(sw.ElapsedMilliseconds / 1000 & "秒")
    End Sub

插入算法4：.net用了好久，却一直没有注意System.Data.SqlClient下还有一个SqlBulkCopy类，使用这个类批量插入数据，共花了23.954秒，是这四个算法最好的一个，比算法2快1倍。

 1 Private Sub Button1_Click()Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 2        Dim t As New System.Data.DataTable
 3        Dim c As System.Data.DataColumn
 4        Dim sw As New System.Diagnostics.Stopwatch
 5        sw.Start()
 6        Dim conn As New System.Data.SqlClient.SqlConnection("Data Source=JXBW-YESHUNQUAN\SQLEXPRESS;Initial Catalog=test;Integrated Security=True")
 7        conn.Open()
 8        c = New System.Data.DataColumn("id")
 9        t.Columns.Add(c)
        c = New System.Data.DataColumn("name")
        t.Columns.Add(c)
        c = New System.Data.DataColumn("sex")
        t.Columns.Add(c)
        c = New System.Data.DataColumn("remark")
        t.Columns.Add(c)
        Dim bc As New System.Data.SqlClient.SqlBulkCopy(conn)
        bc.DestinationTableName = "tbl1"
        Dim i As Long
        For i = 1 To 100000
            t.Rows.Add(i, "gsdgfsdfg" & i, "男", "adfasdfasdddsssssssssssssssss我爱中国")
            If i Mod 100 = 0 Then
                bc.WriteToServer(t, DataRowState.Added)
                t.Rows.Clear()
            End If
        Next i
        conn.Close()
        sw.Stop()
        Debug.Print(sw.ElapsedMilliseconds / 1000 & "秒") 
    End Sub