whi sql server 2008插入500,000条记录太慢了！！！ [英] whay sql server 2008 inserting 500,000 records so slow!!!!

问题描述

大家好

我正在研究一个转换项目，我正在读取一个包含超过500,000条记录的表.那么我应该将字段分为3个其他表.我知道我的算法中有很多插入内容，但是看来我必须等待一个多小时才能完成转换.

有谁能帮助我提高性能.

这是我的代码

hello everyone

I''m working on a conversion project that i''m reading a table wich contains over than 500,000 records. then i should separate the fields into 3 other tables. I know i have a lot of inserts in my algorithm, but it seems that I have to wait over than an hour to complete the conversion.

does anyone can help me to improve the performance.

this is my code

Friend Function ConvertVehicles() As Int16
        Dim Lo_dtvehicles As New DataTable
        Dim Cmd As Int16
        Dim Lnum_ID As Int32
        Dim Pnum_CountShasi As Int32 = 1
        Try
            Cmd = ListVehicle(Lo_dtvehicles)
            If Cmd <> 0 Then
                Return -1
                Exit Function
            End If

            PrVehiclesList.Visible = True
            PrVehiclesList.Maximum = Lo_dtvehicles.Rows.Count
            PrVehiclesList.Value = 0

            For Each Dr As DataRow In Lo_dtvehicles.Rows
                Cmd = InsertShasi(Pnum_CountShasi, Dr.Item("F_Vehicle_Systems_ID") _
                                , IIf(Not IsDBNull(Dr.Item("Vehicle_System_Type_Name")), Dr.Item("Vehicle_System_Type_Name"), "") _
                                , IIf(Not IsDBNull(Dr.Item("Built_Year")), Dr.Item("Built_Year"), 0))
                If Cmd <> 0 Then
                    Return -1
                    Exit Function
                End If
                Pnum_CountShasi += 1

                Cmd = fetchID("Vehicles_Shasi", Lnum_ID)
                If Cmd <> 0 Then
                    Return -1
                    Exit Function
                End If

                Cmd = InsertVehicle(Dr.Item("ID"), _
                                  IIf(Not IsDBNull(Dr.Item("Card_No")), Dr.Item("Card_No"), ""),
                                  IIf(Not IsDBNull(Dr.Item("Plate_Serial_No")), Dr.Item("Plate_Serial_No"), ""),
                                  Dr.Item("Plate_No"),
                                  Dr.Item("F_Issued_City_ID"),
                                  CType(Dr.Item("Creation_Date_Time"), DateTime),
                                  CType(Dr.Item("Last_Modification_date_Time"), DateTime),
                                  IIf(Not IsDBNull(Dr.Item("amar")), Dr.Item("amar"), ""),
                                  Dr.Item("CREATION_Type"),
                                  Dr.Item("Last_Modification_Type"), Lnum_ID)
                If Cmd <> 0 Then
                    Return -1
                    Exit Function
                End If

                If Not IsDBNull(Dr.Item("Trailer_Code")) Then

                    Cmd = fetchID("Vehicles", Lnum_ID)
                    If Cmd <> 0 Then
                        Return -1
                        Exit Function
                    End If

                    Cmd = InsertVehiclesTrailerTypes(Lnum_ID, Dr.Item("Trailer_Code"))
                    If Cmd <> 0 Then
                        Return -1
                        Exit Function
                    End If
                End If

                PrVehiclesList.Value += 1
            Next
        Catch ex As Exception
            lblCurrentsStatus.Text = "Converting Vehicles Table Failed"
            MsgBox(ex.Message)
            Return -1
        Finally
            Lo_dtvehicles = Nothing
            'Lo_Cmd = Nothing
        End Try

    End Function

    Friend Function ListVehicle(ByRef Po_dt As DataTable) As Int16
        Dim Lo_Cmd As SqlClient.SqlCommand
        Dim Lo_DataAdaptor As SqlClient.SqlDataAdapter

        Lo_DataAdaptor = New SqlClient.SqlDataAdapter
        Lo_Cmd = New SqlClient.SqlCommand
        Go_Connection.SetConnection(Lo_Cmd)

        Lo_Cmd.CommandText = "Select * from Vehicle"
        Lo_DataAdaptor.SelectCommand = Lo_Cmd

        Po_dt = New DataTable
        Try
            Lo_DataAdaptor.Fill(Po_dt)
            Po_dt.TableName = "Vehicles"
            Return 0
        Catch ex As Exception
            MsgBox("error get data for vehicles")
            Return -1
        Finally
            Lo_DataAdaptor = Nothing
            Lo_Cmd = Nothing
        End Try
    End Function

    Friend Function fetchID(ByVal Pstr_TableName As String, ByRef Pnum_ID As Int32) As Int16
        Dim Lo_Cmd As SqlClient.SqlCommand
        Dim Lo_DataAdaptor As SqlClient.SqlDataAdapter
        Dim Lo_dt As New DataTable
        Lo_DataAdaptor = New SqlClient.SqlDataAdapter
        Lo_Cmd = New SqlClient.SqlCommand
        Go_Connection.SetConnection(Lo_Cmd)

        Lo_Cmd.CommandText = "Select max(ID) ID  from " & txtDBNAme.Text & ".dbo." & Pstr_TableName
        Lo_DataAdaptor.SelectCommand = Lo_Cmd

        Try
            Lo_DataAdaptor.Fill(Lo_dt)
            Lo_dt.TableName = Pstr_TableName
            Pnum_ID = Lo_dt.Rows(0).Item("ID")
            Return 0
        Catch ex As Exception
            MsgBox(ex.Message)
            Return -1
        Finally
            Lo_DataAdaptor = Nothing
            Lo_Cmd = Nothing
        End Try
    End Function

    Friend Function InsertVehicle(ByVal Pnum_ID As Int32, ByVal Pstr_CardNo As String, ByVal Pstr_PlateSerialNo As String, ByVal Pstr_PlateNo As String, _
                       ByVal Pnum_PlateCityCode As Int32, ByVal Pstr_Creation_Date_Time As DateTime, ByVal Pstr_LastModifictaiondateTime As DateTime, _
                       ByVal Pstr_Amar As String, ByVal Pnum_CreationType As Byte, ByVal Pnum_Last_Modification_Type As Int16, ByVal Pnum_ShasiID As Int32) As Int16

        Dim Lo_Cmd As SqlClient.SqlCommand

        Lo_Cmd = New SqlClient.SqlCommand
        'Lo_Cmd.Connection = Go_Connection.Connection
        Go_Connection.SetConnection(Lo_Cmd)

        'Pstr_LastModifictaiondateTime = String.Format("yyyy-mm-dd hh:mm:ss")

        If Pstr_Amar = Nothing Then
            Pstr_Amar = ""
        End If

        Lo_Cmd.CommandText = "INSERT INTO " & txtDBNAme.Text & ".[dbo].[Vehicles]" & _
           "([ID]" & _
           ",[Card_No]" & _
           ",[Plate_No]" & _
           ",[Plate_Serial_No]" & _
           ",[F_Issued_City_ID]" & _
           ",[F_Vehicles_Shasi_Shasi_No]" & _
           ",[Amar]" & _
           ",[Is_Active]" & _
           ",[Creation_Date_Time]" & _
           ",[Last_Modification_Date_Time]" & _
           ",[Creation_Type]" & _
           ",[Last_Modification_Type])" & _
        "VALUES" & _
            "(" & Pnum_ID & ",'" & Pstr_CardNo & "','" & Pstr_PlateNo & "','" & Pstr_PlateSerialNo & "'," & Pnum_PlateCityCode & _
            "," & Pnum_ShasiID & ",'" & Pstr_Amar & "',1,'" & Pstr_Creation_Date_Time.Date & "','" & Pstr_LastModifictaiondateTime.Date & _
            "'," & Pnum_CreationType & "," & Pnum_Last_Modification_Type & ")"

        Try
            Lo_Cmd.ExecuteNonQuery()
            Return 0
        Catch ex As Exception
            MsgBox(ex.Message)
            Return -1
        End Try

    End Function

    Friend Function InsertShasi(ByVal Pnum_ID As Int32, ByVal Pnum_VehiclesSystemsID As Int32, ByVal Pstr_VehiclSystemTypeName As String, ByVal Pnum_BuiltYear As Int32) As Int16

        Dim Lo_Cmd As SqlClient.SqlCommand

        Lo_Cmd = New SqlClient.SqlCommand
        'Lo_Cmd.Connection = Go_Connection.Connection
        Go_Connection.SetConnection(Lo_Cmd)


        'If Pstr_Amar = Nothing Then
        '    Pstr_Amar = ""
        'End If

        Lo_Cmd.CommandText = "INSERT INTO " & txtDBNAme.Text & ".[dbo].[Vehicles_Shasi]" & _
           "([ID]" & _
           ",[Shasi_No]" & _
           ",[F_Vehicle_Systems_ID]" & _
           ",[Vehicle_System_Type_Name]" & _
           ",[Built_Year]" & _
           ",[Last_Modification_Date_Time])" & _
        "VALUES(" & _
           Pnum_ID & ",'$#" & Pnum_ID + 100000 & "'," & Pnum_VehiclesSystemsID & ",'" & Pstr_VehiclSystemTypeName & "'," & Pnum_BuiltYear & ",getdate())"
        Try
            Lo_Cmd.ExecuteNonQuery()
            Return 0
        Catch ex As Exception
            MsgBox(ex.Message)
            Return -1
        End Try

    End Function

    Friend Function InsertVehiclesTrailerTypes(ByVal Pnum_vehiclesID As Int32, ByVal Pnum_trailerTypesID As Int32) As Int16

        Dim Lo_Cmd As SqlClient.SqlCommand

        Lo_Cmd = New SqlClient.SqlCommand
        'Lo_Cmd.Connection = Go_Connection.Connection
        Go_Connection.SetConnection(Lo_Cmd)


        'If Pstr_Amar = Nothing Then
        '    Pstr_Amar = ""
        'End If

        Lo_Cmd.CommandText = "INSERT INTO " & txtDBNAme.Text & ".[dbo].[Vehicles_Trailer_Types]" & _
           "([F_Vehicles_ID]" & _
           ",[F_Trailer_Types_ID])" & _
        "VALUES(" & _
        Pnum_vehiclesID & "," & Pnum_trailerTypesID & ")"
        Try
            Lo_Cmd.ExecuteNonQuery()
            Return 0
        Catch ex As Exception
            MsgBox(ex.Message)
            Return -1
        End Try

    End Function

推荐答案

#"& Pnum_ID + " & Pnum_VehiclesSystemsID& " & Pstr_VehiclSystemTypeName & " & Pnum_BuiltYear& " ，getdate())" 尝试 Lo_Cmd.ExecuteNonQuery() 返回 0 捕获，例如 As 异常 MsgBox(例如消息) 返回 -1 结束 尝试 结束 功能 朋友 功能 InsertVehiclesTrailerTypes( ByVal Pnum_vehiclesID As Int32 ， ByVal Pnum_trailerTypesID Int32 ) Int16 Dim Lo_Cmd As SqlClient.SqlCommand Lo_Cmd = 新建 SqlClient.SqlCommand ' Lo_Cmd.Connection = Go_Connection.Connection Go_Connection.SetConnection(Lo_Cmd) ' 如果Pstr_Amar = Nothing Then ' Pstr_Amar =" ' 如果结束 Lo_Cmd.CommandText = " & txtDBNAme.Text& " & _ " & _ " & _ " & _ Pnum_vehiclesID& " & Pnum_trailerTypesID& " 尝试 Lo_Cmd.ExecuteNonQuery() 返回 0 捕获，例如 As 异常 MsgBox(例如消息) 返回 -1 结束 尝试 结束 功能

#" & Pnum_ID + 100000 & "'," & Pnum_VehiclesSystemsID & ",'" & Pstr_VehiclSystemTypeName & "'," & Pnum_BuiltYear & ",getdate())" Try Lo_Cmd.ExecuteNonQuery() Return 0 Catch ex As Exception MsgBox(ex.Message) Return -1 End Try End Function Friend Function InsertVehiclesTrailerTypes(ByVal Pnum_vehiclesID As Int32, ByVal Pnum_trailerTypesID As Int32) As Int16 Dim Lo_Cmd As SqlClient.SqlCommand Lo_Cmd = New SqlClient.SqlCommand 'Lo_Cmd.Connection = Go_Connection.Connection Go_Connection.SetConnection(Lo_Cmd) 'If Pstr_Amar = Nothing Then ' Pstr_Amar = "" 'End If Lo_Cmd.CommandText = "INSERT INTO " & txtDBNAme.Text & ".[dbo].[Vehicles_Trailer_Types]" & _ "([F_Vehicles_ID]" & _ ",[F_Trailer_Types_ID])" & _ "VALUES(" & _ Pnum_vehiclesID & "," & Pnum_trailerTypesID & ")" Try Lo_Cmd.ExecuteNonQuery() Return 0 Catch ex As Exception MsgBox(ex.Message) Return -1 End Try End Function

1.为每个表使用一个后台线程(和一个不同的连接)(除非您有外键，在这种情况下，您必须在相应的子记录之前插入每个父记录).
2.在每次往返中插入几条记录. SQL 2008允许使用以下语法:

1. Use a background thread (and a different connection) for each table (unless you have foreign keys, in that case you have to insert each parent record before the corresponding child records).
2. Insert several records in each round trip. SQL 2008 allows the following syntax:

INSERT INTO MY_TABLE(COL1, COL2, COL3) 
VALUES
   (  a,  1, 2),
   (  b,  3, 4),
-- many rows here
   (  xyz, 45, 46); 

早期版本支持以下语法:

Earlier versions support this syntax:

INSERT INTO MY_TABLE(COL1, COL2, COL3)
SELECT a,  1, 2
UNION ALL SELECT b,  3, 4
-- many rows here
UNION ALL SELECT xyz, 45, 46;

保存往返行程帮助我很多进行了类似的项目.

希望这会有所帮助，

巴勃罗.

Saving round trips helped me a lot with a similar project.

Hope this helps,

Pablo.

这篇关于whi sql server 2008插入500,000条记录太慢了！！！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！