Is SqlBulkCopy the wrong choice for efficient single row inserts?

c# sqlbulkcopy sql-server windows-services

Question

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.

Expert Answer

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.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why