CODEWITHSUNDEEP
sqlasp.netsql-servervb.net
Ali
Ali

Reputation: 55

Is it the fastest way for storing data into SQL Server?

I would like to know that is the following source code, the best solution for fast storing data into SQL Server?

The application may store multiple records into a SQL Server table each second (as an example, 50 records in about 5 seconds). I don't want to loose any inserts.

Dim connection As SqlConnection = New SqlConnection("CONNECTION STRING")
Dim command As New SqlCommand("INSERT STATEMENT", connection)
connection.Open()
command.Parameters.Add(New SqlParameter("@Param1", Param1))
command.Parameters.Add(New SqlParameter("@Param2", Param2))
command.ExecuteNonQuery()
command.Dispose()
connection.Close()

Upvotes: 0

Views: 930

Answers (5)

Ahmad Gozin
Ahmad Gozin

Reputation: 23

For better performance I encourage you to create indexes for your tables in SQL Server. Indexes improve your operations.

Upvotes: -1

benjamin moskovits
benjamin moskovits

Reputation: 5458

If you want to load multiple records really fast you want to use Bulk Insert. I have loaded 1-2000 rows per second using Bulk Insert.

Please see this in MSDN: https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017

Upvotes: 1

Mary
Mary

Reputation: 15091

Not giving the parameters datatypes and sizes can slow things down. Check http://www.dbdelta.com/addwithvalue-is-evil/ and https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ and another one https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications

I passed in a List(Of Employee) which assumes a Class called Employee with Properties call FirstName and LastName but any enumerable containing the values you want to enter should work.

Add the parameters once outside the loop. Open the connection once, just before the loop and change the values of the parameters inside the loop.

The Using blocks will close and dispose your database objects.

Private Sub AddEmplyees(empList As List(Of Employee))
    Using connection As SqlConnection = New SqlConnection("CONNECTION STRING")
        Using command As New SqlCommand("Insert Into Employess (FirstName, LastName) Values (@FirstName, @LastName);", connection)
            command.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)
            command.Parameters.Add("@LastName", SqlDbType.VarChar, 100)
            connection.Open()
            For Each e In empList
                command.Parameters("@FirstName").Value = e.FirstName
                command.Parameters("@LastName").Value = e.LastName
                command.ExecuteNonQuery()
            Next
        End Using
    End Using
End Sub

Upvotes: 2

JIKEN
JIKEN

Reputation: 337

You can use XML data type as a parameter and you can pass multiple records using root, parent and child nodes.

Example.

Upvotes: 0

Dai
Dai

Reputation: 155250

  1. No, it isn't - faster INSERT operations can be had using BULK INSERT or bcp - however those are for high-throughput operations (e.g. when you want to insert millions of records) with a long set-up time.
  2. While connection-pooling is enabled by default, you might as well loop over your records within an existing connection.
  3. Your code doesn't wrap the SqlConnection and SqlCommand objects in a using block (or whatever VB's equivalent is) - while this won't affect performance, it will make your program better able to handle database-related exceptions.
  4. If you want to be even faster, insert from a table-valued parameter.

Upvotes: 2

Related Questions