CODEWITHSUNDEEP
c#sql-server
amazing me
amazing me

Reputation: 49

difference between sending multiple insert statement and piling up insert statement in single string and executing that string for more data

From C# end: Sending multiple insert statement for inserting 20 or more rows in database or Piling up insert statement in single string and executing that sql string. Which is more efficient? What are advantages and disadvantages?

E.g:

for(int i=0;i<21;i++)
{
      //Insert command here  
}

or

string qry="Insert into table1 () values "
for(int i=0;i<21;i++)
{
  qry+="(values)";
}

Upvotes: 0

Views: 189

Answers (3)

user1717926
user1717926

Reputation: 323

I use 2 methods (outside of entity framework). As stated if you have a ton of records > 5000 use the SqlBulkCopy. Nothing will be faster than that. It uses datatables so its not the greatest when working with objects.

Another option which we use with our business objects is XML. Our business object has a serialize method which produces xml...then we have a stored procedure such as the following. It has a higher sub cost than just passing in strings, but you will have sql server verifying the syntax is correct, the column names are correct etc. Plus sql server will cache the execution plan. Passing in strings will be prone to typo's and problems when making changes.

@xDoc XML

INSERT INTO dbo.Test(Id,Txt)
SELECT Data.Id,Data.Txt
FROM
(SELECT
    X.Data.value('Id[1]', 'int') AS Id,
    X.Data.value('Txt[1]', 'tinyint') AS Txt
FROM @xDoc.nodes('MyRootNode/MyRecord') AS X(Data)) AS Data;

Upvotes: -1

Jake H
Jake H

Reputation: 1740

If you are trying to perform a bulk insert operation I would recommend you look at the SqlBulkCopy class, which is optimized for this specific operation. If, instead, you are just curious about the relative performance of the various methods of data insertsion - from my experiments using table valued parameters had very high performance when compared to large amounts of ad-hoc insert statements- many of them 'batch inserts', like you mentioned.

Long story short - you're going to have to measure whats best for you using your data and schema. But I do recommend TVP's and the sqlbulkcopy class.

Upvotes: 2

bizzehdee
bizzehdee

Reputation: 21003

if you are sending a bunch of inserts, you should use a transaction as that VASTLY increases the efficiency.

BEGIN;
INSERT .... ;
INSERT .... ;
INSERT .... ;
INSERT .... ;
INSERT .... ;
COMMIT;

is far more efficient and noticeably faster than

INSERT .... ;
INSERT .... ;
INSERT .... ;
INSERT .... ;
INSERT .... ;

Upvotes: 0

Related Questions