0

I'm currently trying to accomplish bulk inserts/updates with EF, since I have a lot of data incoming that needs to be saved into the Db. At every 1000 insert or update commands I dispose of the context in order to keep the number of entities it tracks low-ish.

I achieve this by calling the dispose method of my unit of work right after SaveChanges() :

public void Dispose()
{
    context.Dispose();
    context = null;
}

Then instantiating a new context and giving it to the unit of work to use from then on.

The prcess gets repeated every 60 seconds and so far it seems to be working ok-ish, but from time to time I can see that the SQL server gets stuck between the 60 second cylces. A Query that fetches all data that's currently in db is used before the inserts in order to find out if I only need to update certain entities. (Thus I only update entities that I already have in DB)

var _allMatchDbEntries = matchRepository.GetAll()
                                .Include(x => x.Bets)
                                        .ToList();

This EF Select statement hangs for 30-40 seconds (untill what I'm guessing is a lock getting removed) and then fires and completes in under 1 second. (I tested the query separately in management studio as well it isn't slowing at all)

I have no idea why this delay/lockup would occur. Could anyone tell me if I need to do anything else besides calling Context.Dispose() in order to help SQL server get unstuck? Or is this standard behaviour of calling SaveChanges() ?

p.s. while this select query using join was hanging, simple ones like select * from matches table and select * from bets table were working just fine in MangementStudio.

mm8
  • 163,881
  • 10
  • 57
  • 88
user7519314
  • 11
  • 1
  • You don't have to dispose the context.just Google entity framework bulk insert... – George Vovos Feb 19 '17 at 19:24
  • Look here: https://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework or https://efbulkinsert.codeplex.com/ – cSteusloff Feb 19 '17 at 19:25
  • George Vovos, I wanted to leave that as a last resort. Do you think that disposing of the context causes my problem? – user7519314 Feb 19 '17 at 19:33
  • Check the first link @cSteusloff posted. Now for the read,Include can slow it down.See if you can avoid it and if not try to write a join query.also,if you don't want to update the data use AsNoTracking() – George Vovos Feb 19 '17 at 19:45
  • The problem isn't the include. The query isnt slow. EF just fires the query 60-70 seconds after it is called in code – user7519314 Feb 19 '17 at 19:58
  • Set context.Database.Log and see the generated sql query of your code.Make sure it is the same as the one you run in management studio – George Vovos Feb 19 '17 at 20:44
  • Thanks for the tip. I'll try that tomorrow. I'm trying to make things work with Entity Framework Extensions at the moment. Is it as straightforward as switching context.add / .update / .savechanges with their Bulk" versions? – user7519314 Feb 19 '17 at 21:41
  • I seem to be having the exact same problem with Entity Framework Extensions. – user7519314 Feb 19 '17 at 22:00
  • Removing the Selects that do a Join/Include and replacing them with simple selects that target individual tables seems to be my solution... – user7519314 Feb 19 '17 at 22:33

0 Answers0