ADO.NET Inserts

[Ken Alexander]

I'm finding it hard to believe that this is "as good as it gets" when inserting rows into a database. The code below inserts 40,000 rows in about 1.7 seconds. I have read other posts that folks are getting much quicker times. Is this as good as it gets? Thank you for any insight.

Private Sub InsertTest(ByVal Connection As SqlConnection)
            Using DBTrans As SqlTransaction = Connection.BeginTransaction
                Using cmdSQL As SqlCommand = Connection.CreateCommand()
                    cmdSQL.Transaction = DBTrans
                    cmdSQL.CommandText = "INSERT INTO TestCase(MyValue) VALUES(@Field1); INSERT INTO TestCase(MyValue) VALUES(@Field2); INSERT INTO TestCase(MyValue) VALUES(@Field3); INSERT INTO TestCase(MyValue) VALUES(@Field4)"
                    Dim Field1 As SqlParameter = cmdSQL.CreateParameter()
                    Field1.ParameterName = "@Field1"
 
                    Dim Field2 As SqlParameter = cmdSQL.CreateParameter()
                    Field2.ParameterName = "@Field2"
 
                    Dim Field3 As SqlParameter = cmdSQL.CreateParameter()
                    Field3.ParameterName = "@Field3"
 
                    Dim Field4 As SqlParameter = cmdSQL.CreateParameter()
                    Field4.ParameterName = "@Field4"
 
                    cmdSQL.Parameters.Add(Field1)
                    cmdSQL.Parameters.Add(Field2)
                    cmdSQL.Parameters.Add(Field3)
                    cmdSQL.Parameters.Add(Field4)
 
                    For i As Integer = 1 To 10000
                        Field1.Value = i
                        Field2.Value = i + 1
                        Field3.Value = i + 2
                        Field4.Value = i + 3
                        cmdSQL.ExecuteNonQuery()
                    Next
                End Using
                DBTrans.Commit()
            End Using
        End Sub
 

[debasisdas]

A lot will depend on if the server is local or a remote server and the network traffic.

[CyberSoftHari]

Try to run loops in stored procedure (No Inputs getting from user)

[Frinavale]

Have you considered using a DataAdapter?

DataAdpaters are used to fill DataSets. DataSets are loaded into memory with the data from the database by a DataAdapter. They contain a bunch of tables that exist in memory. Once you are finished editing these tables you can call the DataAdapter's Update method to update the database.

-Frinny

[Ken Alexander]

@Frinavale
Yes I have tried the DataAdapter's Update method. The results are slower using Update. Thank you though.

[Ken Alexander]

@debasisdas
I'm running these tests on a local server.

[Ken Alexander]

@CyberSoftHari
I have not tried stored procedures yet. Will using SP make that much of a difference?