CODEWITHSUNDEEP
sql-serverperformancesql-server-2008t-sqlinsert
Anri
Anri

Reputation: 6265

Optimal insert/update batch for SQL Server

I'm making frequent inserts and updates in large batches from c# code and I need to do it as fast as possible, please help me find all ways to speed up this process.

  1. Build command text using StringBuilder, separate statements with ;
  2. Don't use String.Format or StringBuilder.AppendFormat, it's slower then multiple StringBuilder.Append calls
  3. Reuse SqlCommand and SqlConnection
  4. Don't use SqlParameters (limits batch size)
  5. Use insert into table values(..),values(..),values(..) syntax (1000 rows per statement)
  6. Use as few indexes and constraints as possible
  7. Use simple recovery model if possible
  8. ?

Here are questions to help update the list above

  1. What is optimal number of statements per command (per one ExecuteNonQuery() call)?
  2. Is it good to have inserts and updates in the same batch, or it is better to execute them separately?

My data is being received by tcp, so please don't suggest any Bulk Insert commands that involve reading data from file or external table.

Insert/Update statements rate is about 10/3.

Upvotes: 1

Views: 5108

Answers (3)

IvoTops
IvoTops

Reputation: 3531

As of SQLServer2008 TableParameters are the way to go. See this article (step four)

http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/

I combined this with parallelizing the insertion process. Think that helped also, but would have to check ;-)

Upvotes: 1

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47058

Use SqlBulkCopy into a temp table and then use the MERGE SQL command to merge the data.

Upvotes: 0

Matt Whitfield
Matt Whitfield

Reputation: 6574

Use table-valued parameters. They can scale really well when using large numbers of rows, and you can get performance that approaches BCP level. I blogged about a way of making that process pretty simple from the C# side here. Everything else you need to know is on the MSDN site here. You will get far better performance doing things this way rather than making little tweaks around normal SQL batches.

Upvotes: 2

Related Questions