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