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
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
Go_Connection.SetConnection(Lo_Cmd)
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
Go_Connection.SetConnection(Lo_Cmd)
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
Go_Connection.SetConnection(Lo_Cmd)
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
Submit an article or tip