How often to call DataContext.SubmitChanges() for a large number of inserts?

views:

answers:

How often to call DataContext.SubmitChanges() for a large number of inserts?

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?

+1 A:

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 2010-07-01 00:03:48

+1 A:

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 2010-07-01 01:58:43

+1 A:

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 2010-07-01 10:07:09

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.

ebpower 2010-07-01 18:49:04

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 2010-07-01 18:57:10

ansaurus

tags:

views:

answers:

How often to call DataContext.SubmitChanges() for a large number of inserts?

related questions