2

How many InsertOnSubmit should I call before calling SubmitChanges? I'm adding data from a web service that can return tens of thousands of records one record at a time. The wrapper class around the web service exposes the records a an IEnumberable collection to hide an elaborate chunking mechanism.

Are there guidelines on how many inserts I should accumulate before submitting them?

Ed Power
  • 8,310
  • 3
  • 36
  • 42
  • I had the same problem, and spent ages searching for an answer. In the end, this was my lifesaver... enjoy. http://mikesknowledgebase.com/pages/LINQ/InsertAndDeletes.htm – Mike Gledhill Jul 12 '12 at 12:33

4 Answers4

4

For making changes to one field for 1000 records in batch vs 1 at a time: 1 record at a time takes 0.1989 sec/record vs batch which takes 0.0711 sec/record. So Batch is about 3x as fast in my tests. Note that the batch sizes vary in efficiency.

submitchanges() 1 record at a time
records sec sec/record
1000 198.95 0.1989

Batch submitchanges()
records sec sec/record % change
100 12.20 0.1220 133%
200 18.33 0.0916 122%
500 37.59 0.0752 106%
1,000 71.07 0.0711 103%
2,000 138.64 0.0693 102%
10,000 680.36 0.0680

Fuyu Persimmon
  • 483
  • 6
  • 13
3

Well i've done it with hundreds of thousands of records across multiple tables in the one go without a problem. In fact, whereas calling SubmitChanges() for every InsertOnSubmit() in a case like that would hours, just calling SubmitChanges() right at the end would reduce the time takes to insert that many records to a couple of minutes.

In the case i described above, the arrangement i had was for reporting tables with a header table, details table (which linked to header) and an atom table (which linked to details). For every header record, i had multiple detail tables which would then again be linked by multiple atom records. In some cases, i'd end up inserting gazillions of records and they'd all go in no problems with the single SubmitChanges() call at the end and it all performed very well.

Frank Tzanabetis
  • 2,756
  • 2
  • 22
  • 22
3

It also depends on the kind of data you need to insert. Sometimes I need to insert a lot of records, where I also need the ID of, to insert more records in another table.

Because the ID gets assigned when you submit changes to the database, I need to call SubmitChanges on specific times.

When this is not needed, I just submit them with a 1000 at once or so (depending on the total number of records I need to insert).

Maybe you could do some speed tests, which are best for you. Depending on the hardware, expectations of amount of records, etc.

Wim Haanstra
  • 5,918
  • 5
  • 41
  • 57
  • That's a good point regarding getting an ID back. I'm concerned about the memory footprint as there can be some large records but 1000 at a time is a good place to start. – Ed Power Jul 01 '10 at 18:49
  • Yup, that is one thing I really hate about it. Sometimes each record needs a new ID, which makes you submit records way to much for a good performance. This is something you need to figure out in your code, just to see where you need them and where you can submit them later. – Wim Haanstra Jul 01 '10 at 18:57
1

There aren't really any "Guidelines" per-say. Id would say for efficiency you'd want to collect a bunch, maybe not 10k, but say 100? That would drastically reduce your DB Queries and shouldn't eat up too much ram caching them locally while you build up a transaction.

You should probably test with a couple different values and profile the performance (memory & speed) to find the optimal solution for your hardware though.

Aren
  • 54,668
  • 9
  • 68
  • 101