title: Fastest Way to Update table in SQL Server
body: <p>I have a Vb.net application that is updating a table in a SQL Server database very frequently. The table has 143 columns and about 10,000 rows. The same procedure is required to update the table for several different modules so the data updated is different all the time, sometimes it could be just a few cells in a few rows other times it may be several hundred rows and several columns. </p>
  
  <p>At times it's taking 15 to 30 seconds to update the information. That seams really long given that the table can be totally re-written with a bulk import in a second or 2 (I realise that that is beside the point). The database is set to simple recovery, the table has only one index. I have tried playing around with the update batch size to no noticeable improvement.</p>
  
  <p>I'm using the below code to do the update. Is there anything that I can do to improve the speed? </p>
  
  <pre><code> Dim oMainQueryR As String
  
      If DBSelectionsDS.HasChanges Then
  
          Try
                  oMainQueryR = "SELECT * FROM DBSelections"
  
                  Using connection As New SqlConnection(RacingConStr)
                      Using oDataSQL As New SqlDataAdapter(oMainQueryR, connection)
                          oDataSQL.UpdateBatchSize = 100
                          Using cbT As SqlCommandBuilder = New SqlCommandBuilder(oDataSQL)
                              connection.Open()
                              oDataSQL.Update(DBSelectionsDS, "DBSelectionsDetails")
                              connection.Close()
                          End Using
                      End Using
                  End Using
  
  
                  DBSelectionsDS.Tables("DBSelectionsDetails").AcceptChanges()
  
          Catch ex As Exception
  
              ErrMess = "ERROR - occured  " &amp; ex.ToString
              Call WriteError()
              Call ViewError()
  
          End Try
  
      End If
  </code></pre>