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

by

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.

More Related Contents:

  1. database access VS lan access [closed]
  2. How can I determine whether a 2D Point is within a Polygon?
  3. Why is MATLAB so fast in matrix multiplication?
  4. INC instruction vs ADD 1: Does it matter?
  5. DataFrame / Dataset groupBy behaviour/optimization
  6. Excel VBA Performance – 1 million rows – Delete rows containing a value, in less than 1 min
  7. Is there a penalty when base+offset is in a different page than the base?
  8. What’s the most efficient way to test if two ranges overlap?
  9. What happens after a L2 TLB miss?
  10. What does gcc’s ffast-math actually do?
  11. Are there any modern CPUs where a cached byte store is actually slower than a word store?
  12. Execute Hive Query with IN clause parameters in parallel
  13. Non-temporal loads and the hardware prefetcher, do they work together?
  14. What is the overhead of Rust’s Option type?
  15. Why is reshape so fast? (Spoiler: Copy-on-Write)
  16. JMeter : How to record HTTPS traffic?
  17. What is the most ridiculous pessimization you’ve seen? [closed]
  18. What are the major performance hitters in AS3 aside from rendering vectors?
  19. Find number in sorted matrix (Rows n Columns) in O(log n) [duplicate]
  20. bsxfun implementation in matrix multiplication
  21. Why is the final reduce step extremely slow in this MapReduce? (HiveQL, HDFS MapReduce)
  22. Unexpectedly poor and weirdly bimodal performance for store loop on Intel Skylake
  23. Why can’t my ultraportable laptop CPU maintain peak performance in HPC
  24. How should I calculate Ramp-up time in Jmeter
  25. How slow (how many cycles) is calculating a square root?
  26. Performance optimisations of x86-64 assembly – Alignment and branch prediction
  27. How are cache memories shared in multicore Intel CPUs?
  28. Ways to improve git status performance
  29. why is data structure alignment important for performance?
  30. Network throttling with chrome and selenium

Leave a Comment