程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> Visual Basic語言 >> VB.NET >> 大數據量拔出(vb.net 2008)

大數據量拔出(vb.net 2008)

編輯:VB.NET

大數據量拔出(vb.net 2008)。本站提示廣大學習愛好者:(大數據量拔出(vb.net 2008))文章只能為提供參考,不一定能成為您想要的結果。以下是大數據量拔出(vb.net 2008)正文


這段時間在做抓包項目,需求把抓來的數據拔出到數據庫中,特做了四種拔出數據算法比擬。

數據庫: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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved