CODEWITHSUNDEEP
c#sql-serverwindows-servicessqlbulkcopy
Alex
Alex

Reputation: 11

Is SqlBulkCopy the wrong choice for efficient single row inserts?

Good Morning.

I have an a service I am writing in C# that performs and insert of a single row every 15 seconds or so. This interval is user-definable but at the moment 15 seconds is the targeted interval.

I have read this article: SqlBulkCopy on a single record? which does explain clearly the throughput benefits of using a regular insert over performing a single row bulk insert. But it also mentions (or implies) that the regular insert is a bit a heavier on CPU than the bulk insert.

As the service I Am writing is a support service, and as the insert is artificially restricted to only 1 in a given interval I am wondering if whether the implication above is justification for using SqlBulkCopy over a regular insert. Obviously I want my service to be as lightweight as possible.

Upvotes: 0

Views: 318

Answers (1)

Jonathan Magnan
Jonathan Magnan

Reputation: 11337

The answer in your article says the SqlBulkCopy is 4.4x slower and take double the CPU of a simple SQL Command. You misread the answer (or I did), it states SqlBulkCopy is heavier on CPU than a regular insert which makes more sense.

The answer is simple: Stick with SQL Command if you are 100% sure only one record need to be inserted.

Even my library C# Bulk Operations do not use SqlBulkCopy until it reaches a specific amount of row which is normally ten because SqlBulkCopy is too heavy.

Upvotes: 2

Related Questions