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

Question

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()

50 records in about 5 seconds is [as close to nothing](https://stackoverflow.com/q/385506/11683) as it gets. — GSerg, Aug 31 '19 at 19:59
+1 @GSerg The code you have posted is simple, correct (once you add a `using` block for the SqlConnection), and entirely appropriate for the level of throughput required. — David Browne - Microsoft, Aug 31 '19 at 20:51

score 2 · Answer 1 · answered Aug 31 '19 at 19:50

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.
While connection-pooling is enabled by default, you might as well loop over your records within an existing connection.
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.
If you want to be even faster, insert from a table-valued parameter.

When I click on the first 2 links from the link you provided, it says "The product, service, or technology mentioned in this content is no longer supported." — Mary, Sep 01 '19 at 17:13

score 2 · Accepted Answer · answered Sep 01 '19 at 00:39

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

score 1 · Answer 3 · answered Sep 01 '19 at 01:21

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

score 0 · Answer 4 · edited Aug 31 '19 at 20:42

0

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

Example.

edited Aug 31 '19 at 20:42

Uwe Keim

39,551
56
175
291

answered Aug 31 '19 at 20:38

JIKEN

337
2
7

3

Personally, I'd use a table-valued parameter instead of XML if the client API supports it. – Dan Guzman Aug 31 '19 at 20:59
3

If you're going to include an example, do so in your answer, not as an off site resource – Thom A Aug 31 '19 at 21:01
https://learn.microsoft.com/en-us/sql/connect/jdbc/using-table-valued-parameters?view=sql-server-2017#limitations-of-table-valued-parameters – JIKEN Aug 31 '19 at 21:10

score -1 · Answer 5 · edited Sep 01 '19 at 07:29

-1

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

edited Sep 01 '19 at 07:29

Dale K

25,246
15
42
71

answered Sep 01 '19 at 05:55

Ahmad Gozin

23
3

1

Please explain how an *index* improves the performance of an `insert` – Dale K Sep 01 '19 at 07:29
@Ali - again how does that improve insert speed? The typical use of an index is to improve lookup speed. – Dale K Sep 02 '19 at 20:17
I am no DBA but I think indexes actual slow down inserts because the index must be updated along with the data. – Mary Sep 05 '19 at 16:23

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

5 Answers5