0

I need to insert multiple records from ado.net. It should call a SP for updating. I have multiple records as CSV and added them in temporary table in SP. Some Validations needs to be done that. And it get inserted/updated in to main table.

Problem is using Temp table has performance bottle necks.

I have googled it. Other ways of passing multiple records can be done by using XML or table value parameter in sql 2008.

On Performance point of view, which one is better. In my case, 500 concurrent transactions may happen at a time.

Please suggest.

Regards, Mani.M

Mani.M
  • 11
  • 1

2 Answers2

1

Of the three, Table Valued Parameters usually perform better, and I find them more maintainable as well. This article discusses the performance using XML, TVP, and CSV:

http://www.adathedev.co.uk/2010/02/sql-server-2008-table-valued-parameters.html

James Johnson
  • 45,496
  • 8
  • 73
  • 110
0

Just answered a similar question, although it was focused less on concurrency and more on pure performance: Bulk/batch update/upsert in PostgreSQL.

In my answer to the other question, you would only have 1 transaction, which would address your issue.

Community
  • 1
  • 1
atrain
  • 9,139
  • 1
  • 36
  • 40