CODEWITHSUNDEEP
sqlxmlperformanceado.nettable-valued-parameters
Mani.M
Mani.M

Reputation: 11

Multiple Inserts in ADO.Net & SP

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

Upvotes: 0

Views: 392

Answers (2)

James Johnson
James Johnson

Reputation: 46047

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

Upvotes: 1

atrain
atrain

Reputation: 9255

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.

Upvotes: 0

Related Questions