這段時間在做抓包項目，需要把抓來的數據插入到數據庫中，特做了四種插入數據算法比較。
數據庫：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秒。
[dbo].[tbl1_insert_1]存儲過程
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’
10set @sql='insert into ‘+@tblname +’(id,[name],sex,remark) '+
11 'values(' +convert(nvarchar, @id) +','''+@name+''','''+@sex+''','''+@remark+''')'
12 exec(@sql)
13END
14
Code
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-YESHUNQUANSQLEXPRESS;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
10 Dim para(3) As System.Data.SqlClient.SqlParameter
11 para(0) = New System.Data.SqlClient.SqlParameter("@id", SqlDbType.BigInt)
12 para(1) = New System.Data.SqlClient.SqlParameter("@name", SqlDbType.NVarChar, 50)
13 para(2) = New System.Data.SqlClient.SqlParameter("@sex", SqlDbType.NVarChar, 10)
14 para(3) = New System.Data.SqlClient.SqlParameter("@remark", SqlDbType.NVarChar, 50)
15 cmd.Parameters.Add(para(0))
16 cmd.Parameters.Add(para(1))
17 cmd.Parameters.Add(para(2))
18 cmd.Parameters.Add(para(3))
19 For i = 1 To 100000
20 para(0).Value = i
21 para(1).Value = "gsdgfsdfg" & i
22 para(2).Value = "男"
23 para(3).Value = "adfasdfasdddsssssssssssssssss我愛中國"
24 cmd.ExecuteNonQuery()
25 Next
26 conn.Close()
27 sw.Stop()
28 Debug.Print(sw.ElapsedMilliseconds / 1000 & "秒")
29 End Sub
30
插入算法2：用存儲過程，只保存一張表，性能會是怎樣呢？同樣插入10萬條數據，共花了 48.896秒，比算法1快20秒。
[dbo].[tbl1_insert]存儲過程
1ALTER PROCEDURE [dbo].[tbl1_insert]
2 @id bigint, @name nvarchar(50) ,
3 @sex nvarchar(10),@remark nvarchar(50)
4AS
5BEGIN
6 SET NOCOUNT ON;
7insert into tbl1(id,[name],sex,remark) values(@id,@name,@sex,@remark)
8END
9
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-YESHUNQUANSQLEXPRESS;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
10 cmd.Connection = conn
11 Dim para(3) As System.Data.SqlClient.SqlParameter
12 para(0) = New System.Data.SqlClient.SqlParameter("@id", SqlDbType.BigInt)
13 para(1) = New System.Data.SqlClient.SqlParameter("@name", SqlDbType.NVarChar, 50)
14 para(2) = New System.Data.SqlClient.SqlParameter("@sex", SqlDbType.NVarChar, 10)
15 para(3) = New System.Data.SqlClient.SqlParameter("@remark", SqlDbType.NVarChar, 50)
16 cmd.Parameters.Add(para(0))
17 cmd.Parameters.Add(para(1))
18 cmd.Parameters.Add(para(2))
19 cmd.Parameters.Add(para(3))
20 For i = 1 To 100000
21 para(0).Value = i
22 para(1).Value = "gsdgfsdfg" & i
23 para(2).Value = "男"
24 para(3).Value = "adfasdfasdddsssssssssssssssss我愛中國"
25 cmd.ExecuteNonQuery()
26 Next
27 conn.Close()
28 sw.Stop()
29 Debug.Print(sw.ElapsedMilliseconds / 1000 & "秒")
30 End Sub
31
插入算法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-YESHUNQUANSQLEXPRESS;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
10 cmd.CommandText = "insert into tbl1(id,[name],sex,remark) values(" & i & ",'gsdgfsdfg" & i & "','男','adfasdfasdddsssssssssssssssss我愛中國')"
11 cmd.ExecuteNonQuery()
12 Next
13 conn.Close()
14 sw.Stop()
15 Debug.Print(sw.ElapsedMilliseconds / 1000 & "秒")
16 End Sub
17
插入算法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-YESHUNQUANSQLEXPRESS;Initial Catalog=test;Integrated Security=True")
7 conn.Open()
8 c = New System.Data.DataColumn("id")
9 t.Columns.Add(c)
10 c = New System.Data.DataColumn("name")
11 t.Columns.Add(c)
12 c = New System.Data.DataColumn("sex")
13 t.Columns.Add(c)
14 c = New System.Data.DataColumn("remark")
15 t.Columns.Add(c)
16 Dim bc As New System.Data.SqlClient.SqlBulkCopy(conn)
17 bc.DestinationTableName = "tbl1"
18 Dim i As Long
19 For i = 1 To 100000
20 t.Rows.Add(i, "gsdgfsdfg" & i, "男", "adfasdfasdddsssssssssssssssss我愛中國")
21 If i Mod 100 = 0 Then
22 bc.WriteToServer(t, DataRowState.Added)
23 t.Rows.Clear()
24 End If
25 Next i
26 conn.Close()
27 sw.Stop()
28 Debug.Print(sw.ElapsedMilliseconds / 1000 & "秒")
29 End Sub
30
