How can I increase the speed of many insert statements?

Question

I have a .NET project that is receiving data, processing it, and then writing it to a SQLServer database. Let's say I have the following code.

Private Sub InsertRecord(val1 As Integer, val2 As Integer, ByRef dbConnection As      Data.SqlClient.SqlConnection)
    Dim cmd As SqlCommand = Nothing
    If dbConnection.State = ConnectionState.Open Then
        Dim strSql As String = "Insert Into MyTable(Value1, Value2) Values(@Value1, @Value2)"
        cmd = New SqlCommand(strSql, dbConnection)
        cmd.Parameters.AddWithValue("@Value1", val1)
        cmd.Parameters.AddWithValue("@Value2", val2)
        Dim returnVal = cmd.ExecuteNonQuery()
        If Not returnVal Then
            'do other stuff
        End If
        cmd.Dispose()
    End If
End Sub

Sometimes I need to insert records quickly - say 500 per second. As is, it can only handle around 200 inserts per second. Is there some way to combine multiple commands to be processed at once? I need to know the results of each insert statement.

score 0 · Answer 1 · answered Sep 24 '13 at 18:34

You need SQL Server bulk insert as made available through the SqlBulkCopy class. Use one transaction for all rows. It will be blindingly fast.

Indexing also plays into this, as well as many other things. Not enough information here to comment specifically.

How can I increase the speed of many insert statements?

1 Answers1