I have a large database application on SQL Server 2012 Standard Edition which receives data from hardware in chunks of 500-1000 (100 byte) rows every few seconds through a stored procedure. Would a multi-threaded application with each thread using a database connection, inserting the block of rows work faster than a single thread connection serially calling the stored procedure multiple times? What is the best way to insert the data for the highest performance? Are there clustered index designs which would work better in this scenario?

Thanks, Tony

  • It is one write head on the other end (unless you have something fancy like partitioning).
    – paparazzo
    Jul 25 '15 at 17:14
  • This is the wrong question to ask. You have a performance problem, analyze it, find the bottleneck and address it. How to analyse SQL Server performance Jul 25 '15 at 19:11

Consider using a table-valued parameter to pass many rows of data as a single proc call, or alternatively, bulk copy directly into the table. These techniques will improve insert throughput by orders of magnitude compared to singleton inserts, even if those are multi-threaded.

An incremental clustered index will provide the best insert performance against spinning media.

  • I guess I was not as clear as I could have been. I am using a TVP to insert multiple rows with a stored procedure. The question is do I start up multiple connections to call the SPROC multiple times simultaneously or use just 1 connection and call the SPROC 1 at a time as each iteration returns in order to get maximum throughput?
    – runamuk0
    Jul 28 '15 at 14:26
  • @runamuk0, is the single-threaded TVP unable to keep up? I'd expect sub-second response time with an incremental clustered index key or solid state storage. As Remus mentioned, I'd be leery of throwing additional threads at the problem without understanding where the problem lies and knowing more about your existing indexes, data, and code. You might introduce deadlocking or blocking that mitigates performance improvement.
    – Dan Guzman
    Jul 29 '15 at 2:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.