3

There are many articles here on EF taking a long time to save, but I've looked through them and used their answers and still seem to get very slow results.

My code looks like so:

using (MarketingEntities1 db = new MarketingEntities1())
        {
            //using (var trans = db.Database.BeginTransaction(IsolationLevel.ReadUncommitted))
            //{
                int count = 0;
                db.Configuration.AutoDetectChangesEnabled = false;
                db.Configuration.ValidateOnSaveEnabled = false;
                while (count < ranges.Count)
                {
                    if (bgw != null)
                    {
                        bgw.ReportProgress(0, "Saving count: " + count.ToString());
                    }
                    db.Set<xGeoIPRanx>().AddRange(ranges.Skip(count).Take(BATCHCOUNT));
                    db.SaveChanges();
                    count+=BATCHCOUNT;

                }
                //trans.Commit();
            //}
        }

Each batch takes 30+ seconds to complete. BatchCount is 1000. i know EF isn't that slow. You can see that I've stopped using transaction, I've taken tracking off, none of it seemed to help.

Some more info:

xGeoIpRanx is an empty table, with no PK(I'm not sure how much it would help). I'm trying to insert about 10 mil ranges.

Edit:

i feel stupid but im trying to use bulkInsert and i keep getting this entity doesnt exist errors, i look at this code 

using (var ctx = GetContext())
{
  using (var transactionScope = new TransactionScope())
  {
    // some stuff in dbcontext

    ctx.BulkInsert(entities);

    ctx.SaveChanges();
    transactionScope.Complete();
  }
}

What is "entities" I tried a list of my entities, that doesnt work, what data type is that?

nvm it works as expected it was a strange error due to how i generated the edmx file

Mr. MonoChrome
  • 1,289
  • 2
  • 16
  • 34
  • Well, profile the app to see what's taking time. Could be anything. Pause the debugger 10 times under load and look at the stack including external code. Where does it stop most often?; I don't see why batching would improve anything here. Use one batch for everything. – usr Aug 13 '14 at 16:23
  • Is your database server and web server on the same local network? If you're debugging from your local workstation, and the SQL server is off-site in a datacenter than this will be very slow. EF is "chatty" in that it does a lot of small SQL statements, so latency will be an issue. – Jon Aug 13 '14 at 16:23
  • @usr Its taking a long time on the .SaveChanges() Also I batched it for debugging purposes, to see that it was saving at all. – Mr. MonoChrome Aug 13 '14 at 17:09
  • 1
    @Mangist It is off site, we connect to it through a vpn – Mr. MonoChrome Aug 13 '14 at 17:09
  • @Ukemi and what's the stack trace there? Is it in EF code or does it stop in ADO.NET code? Probably ADO.NET. That means network latency and server execution time are causing this. – usr Aug 13 '14 at 17:09
  • @usr just from some quick tests, ADO.net code – Mr. MonoChrome Aug 13 '14 at 17:12
  • 1
    I would use [EntityFramework.BulkInsert](https://efbulkinsert.codeplex.com/) it is super fast – Aducci Aug 13 '14 at 17:12
  • Im going to try bulk insert and ill get back with all of you – Mr. MonoChrome Aug 13 '14 at 17:18
  • It's network latency, put your webserver in the same cage as your database server and you won't have this issue. If you're doing it over a VPN to your SQL Server, you need to figure out a better way of doing this (using SqlBulkLoad or similar) – Jon Aug 13 '14 at 17:23
  • So bulk insert didnt work, I ended up using http://stackoverflow.com/questions/2553545/sqlbulkcopy-and-entity-framework – Mr. MonoChrome Aug 14 '14 at 13:09

1 Answers1

4

Pause the debugger 10 times under load and look at the stack including external code. Where does it stop most often?

.

Its taking a long time on the .SaveChanges(). just from some quick tests, ADO.net code

That means network latency and server execution time are causing this. For inserts server execution time is usually not that high. You cannot do anything about network latency with EF because it sends one batch per insert. (Yes, this is a deficiency of the framework.).

Don't use EF for bulk work. Consider using table-values parameters or SqlBulkCopy or any other means of bulk inserting such as Aducci's proposal from the comments.

usr
  • 162,013
  • 33
  • 219
  • 345