Entity Framework Transaction: What is better performance-wise?

Question

Which has better performance:

using (GADEEntities context = new GADEEntities(_connectionString))
{
   using (TransactionScope transaction = new TransactionScope())
   {
      AddToContext1(context);
      AddToContext2(context);
      AddToContext3(context);
      ...

      context.SaveChanges();

      transaction.Complete();
   }
}

or

using (GADEEntities context = new GADEEntities(_connectionString))
{
   using (TransactionScope transaction = new TransactionScope())
   {
      AddToContext1(context);
      context.SaveChanges();

      AddToContext2(context);
      context.SaveChanges();

      AddToContext3(context);
      context.SaveChanges();
      ...

      transaction.Complete();
   }
}

At any time, this could translate into 5000+ inserts into a DB on a clients machine. Is either way any different?

@Oded: I unfortunately don't have time to do any official testing myself due to tight project deadline. Hard telling from VS due to VS overhead when running debugger and a client's hardware can vary in performance. So I'm hoping someone could give me a quick answer. My initial thought agrees with the post from Nik. You have any thoughts? — jonnyb, Apr 28 '11 at 19:05
Thoughts only, not backed up by testing... I would say all operations are happening in memory with EF and the real overhead is the network IO which has to happen in any case. Negligible difference I would guesstimate. — Oded, Apr 28 '11 at 19:08
Just to add (again) that my best guesstimate should not replace testing. — Oded, Apr 28 '11 at 19:15
Is there even a need for TransactionScope in the first example? SaveChanges is wrapped in a transaction as is. — e36M3, Apr 28 '11 at 20:41
@e36M3: Yes it is necessary. I need everything to be in the same Unit Of Work. If one fails, then everything needs to rollback. Even if you call `SaveChanges()` in a trans and one fails, everything will roll back due to the transaction being promoted. — jonnyb, May 03 '11 at 20:52
@jonnyItunes, I'm just saying that if you only have one call to SaveChanges, it's already in a transaction by default, such as your first example. Unless I'm missing something. — e36M3, May 05 '11 at 15:25

score 2 · Accepted Answer · edited May 23 '17 at 10:00

2

It's very likely that your first version will be always faster, depending on what AddToContext exactly does. If your AddToContext method adds a single or only a few new objects to the context it will be definitely much faster. Calling SaveChanges after each insert (and probably also update and delete) slows the performance extremely down.

Here are a few measurements in a similar question:

Fastest Way of Inserting in Entity Framework

edited May 23 '17 at 10:00

Community

1
1

answered May 25 '11 at 23:11

Slauma

167,754
56
385
407

Thanks for responding to my older post! This is exactly what I was looking for and validates my hunch. I'll have to dive back in to my code and see how to best apply. Thanks again! – jonnyb May 26 '11 at 19:40
@jonnyItunes: I had your question in my favorites since I was interested in the answer as well but had no clue what's the best option. One or two weeks later I found the answer myself in another context but forgot your question. Yesterday during cleaning up my favorites a bit I discovered your question again. Case closed now, question unfavorated ;) – Slauma May 26 '11 at 20:15
@jonnyItunes: Just to mention this: The surrounding transaction doesn't make a difference in performance. I had tested it in both cases - with and without `TransactionScope`, the result regarding performance is the same. – Slauma May 26 '11 at 20:22

score 1 · Answer 2 · answered Apr 28 '11 at 18:53

1

The way you have it set up, I do not think there is any significant difference. The data will be transmitted either way, and that's the real bottleneck.

answered Apr 28 '11 at 18:53

Nik

6,733
11
49
79

score 1 · Answer 3 · answered Apr 28 '11 at 19:59

There is very big difference because second version is terribly wrong.

What are you doing by this code:

AddToContext1(context);
context.SaveChanges(false);

You add record to context in Added state and let the context insert the record to the database but in the same time you are saying: "Let the data in Added state".

What happesn if you call this:

AddToContext2(context);
context.SaveChanges(false);

You add another recored to context in Added state and let the context insert all records in Added state to the database = the first record will be added again

It doesn't matter if AddToContext actually performs update because it will simply do the DB command again. So if you have 5.000 records you will insert or update the first one 5.000 times!

If you want to use second version you still have to accept changes during each saving.

Btw. SaveChanges overload accepting bool is obsolete in EFv4.

Thanks for pointing out my mistake in option 2. I updated the code, based on your answer, to better reflect my question. I am currently using the first option in my application, but didn't know if there was anything to gain by breaking up the transactions into smaller chunks. — jonnyb, Apr 28 '11 at 20:39

Entity Framework Transaction: What is better performance-wise?

3 Answers3