Performance of bcp/BULK INSERT vs. Table-Valued Parameters

I don’t really have experience with TVP yet, however there is an nice performance comparison chart vs. BULK INSERT in MSDN here.

They say that BULK INSERT has higher startup cost, but is faster thereafter. In a remote client scenario they draw the line at around 1000 rows (for “simple” server logic). Judging from their description I would say you should be fine with using TVP’s. The performance hit – if any – is probably negligible and the architectural benefits seem very good.

Edit: On a side note you can avoid the server-local file and still use bulk copy by using the SqlBulkCopy object. Just populate a DataTable, and feed it into the “WriteToServer”-Method of an SqlBulkCopy instance. Easy to use, and very fast.

Leave a Comment

tech