Multi-threaded inserts vs serial
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?
More information – 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 for each batch as each iteration returns in order to get maximum throughput?