Updating a huge number of records

Thanks for your reply Mike.

I have tried the updatebatchsize, but the problem maybe in the way i try to update the database. Do you have any other way of doing this?

Cheers,

Martin

Dim connect As New SqlConnection(ConnectionString)
connect.Open()

Dim command As String = "SELECT * from tblpingRTT"
Dim DS As New DataSet()
Dim adaptor As SqlDataAdapter = New SqlDataAdapter(command, connect)

adaptor.UpdateBatchSize = 0

Dim cmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(adaptor)adaptor.MissingSchemaAction = MissingSchemaAction.AddWithKey
adaptor.Fill(DS, "tblPingRTT")
Dim dt As DataTable = DS.Tables("tblpingrtt")

Dim en As IEnumerator = UpdatePingRTT.GetEnumerator

While en.MoveNext
Dim RTT As New PingRTT
RTT = en.Current

Dim sql As String = "DeviceID=" & RTT.DeviceID & " and timeinterval=" & GetTimer()
Dim mydatarow = dt.Select(sql)(0)
mydatarow("TimeInterval") = GetTimer()
mydatarow("Value") = RTT.RTT
mydatarow("DeviceID") = RTT.DeviceID

End While

UpdatePingRTT.Clear()

adaptor.UpdateCommand = cmdBuilder.GetUpdateCommand()
adaptor.Update(DS.Tables(0))

There is a way to mass batch the records.

Check this tip for SQL Server


In ADO.NET 2.0  You can also use the DataAdapter and set an UpdateBatchSize to set the number of commands that will be sent to the database with each request to optimize your update call.

In your adapter:

adapter.UpdateBatchSize = 2;
command.UpdatedRowSource = UpdateRowSource.None;

 Execute the update process:

 

adapter.Update(ds);