I have a C# system that reads thousands of messages per minute from a Kafka topic. The messages are filtered and I need to insert the filtered messages into a MSSQL table.
A normal insert statement is a too slow and seems to hammer the database with thousands of inserts running at the same time.
(Currently using Dapper and SqlBulkCopy in the system - so I have access to those tools by default)
I've solved the issue by buffering the records and then performing a BulkCopy. That works, but it does mean that there is some latency while I buffer enough records to make the BulkCopy worthwhile. The users really want access to the record as soon as possible. We are in a high volume, near-real-time data processing environment.
Any idea what other solutions I could try?
Would creating smaller buffers and then performing an insert like the below provide significant benefit?
Insert into TableA( ID ) Values (1), (2), (3)
Are there any standard patterns for handling this kind of situation?
Would transactions at all be useful in this scenario? Maybe multiple single inserts with a single commit for a small batch?
At this point I'm just guessing on what to try next :)