0

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.

2
  • When you say "busy" what kind of workload are you talking about? Read only? Updates? Other insert activity? – Jonathan Fite Mar 19 '20 at 12:15
  • The main ingest process is Inserting 50,000 + a min, many users are doing read only queries (load majority is inserts) no updates on this large table. and maybe 150 inserts per day from another process on this large table – Quade Mar 20 '20 at 4:26
0

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.

  • Change your clustered index to be Date/Time, SensorID, pkID
  • Add a non-clustered index on SensorID, Date/Time

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.

6
  • The table won't be badly fragmented. Each SensorId will have its own "insert point", and will fill its pages and extents completely. But a Clustered Columstore might be better than a Clustered Index for this. – David Browne - Microsoft Mar 20 '20 at 14:03
  • Thanks, I ran some tests with that clustered index and they are significantly slower on reads, i'll look into clustered columnstore, there are about 20 columns and most of these are returned for most queries so not sure if this is best solution – Quade Mar 26 '20 at 4:23
  • "you have a relatively stable set of sensors with lots of date/time and measurements." - 25k and 4k added each month – Quade Mar 26 '20 at 12:45
  • I'm very curious about having what looks like duplicate indexes, 1 clustered and 1 non, but with specificity order differences, isn't this just using more disk space? – Quade Mar 26 '20 at 12:49
  • Columnstore index wasn't suitable for my workloads, its an OLTP environment, I did run the tests and queries went from 2 secs to 9 secs...thanks for the suggestion though – Quade Mar 26 '20 at 12:54

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.