I'm re-writing a C# console app from single insert to do bulk inserts
based on advice here, I need to bulk insert into a staging table (with id identity seed)
Then I load into temp tables, do a little processing, and then insert into the main table
This is for a real time 24/7 so it needs to be as fast and efficient as possible
1) what is the fastest way to bulk insert data from c# console app, to staging table (every 1 min, approx 50,000-150,000 records) – I'm planning on using SqlBulkCopy
2) Does having id with identity seed on the staging table slow down things ? should i be using a heap?
3) My main question and the highest risk area is inserting to the main busy table so this needs to be as fast as possible to reduce locking: when doing the bulk insert into main table, should i be pre-ordering the data so it matches the main clustered index (its on 3 columns: sensorId, DateTime, pkId) or will the I just be sorting twice? this table is extremely busy.
Any other advice on this area appreciated.
this whole bulk insert process is just a Stored Procedure, and i was thinking of calling it every 1 min from sql agent to make sure it runs (not sure if this is best solution)
sql server 2017 ent.
Best Answer
I think that you may need to look at redesigning the clustered index on your main table. Best practices for a clustered index is to make it unique and ever increasing, but you have the first column being sensorid. Which means that (assuming I have inferred correctly) that you have a relatively stable set of sensors with lots of date/time and measurements.
This will lead to lots of page forwards and an extremely fragmented, which increases your maintenance duration as your index defragmentation process works. Without knowing your actual query load, I can only make broad suggestions.
With that in place, your inserts won't fragment the clustered index as badly. BulkInserts won't cause blocking to read workloads. Having a clustered index on the stage table that matches it's target will help.