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.
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.