0

We've finalized application that uses Entity Framework 5, and performs about 1000 inserts/second to specific table.

Now client has requested that application is not on the same server as is the database. When we've deployed application in such scenario, the time required to insert records has raised up to 4x.

We've done performance logs, and narrowed the performance loss to executing inserts.

Is there a known issue with using Entity Framework "on the wire" to work with remote database and handling large sets of data (100.000+ rows)?

Admir Tuzović
  • 10,997
  • 7
  • 35
  • 71

2 Answers2

1

I've found two performance problems with EF when loading a large number of records.

1) Insert is performed record-by-record (can see this when calling db.SaveChanges() with SQL profiler turned on.

2) Even before SaveChanges(), the DbSet.Add() calls get slower and slower as you progress through the records. Turning off change tracking helps, but not much.

I use SqlBulkCopy to do large volume inserts. SqlBulkCopy issues a single insert for all records. For the second problem, I use simple collections to collate the data, rather than adding to DbSets.

There are a couple of extra tasks required for SqlBulkCopy - needs to know the table schema, so I pull this out of EF metadata. Validation is a bit weak (nonexistent) so you need to implement it against the buffer collections.

Addendum after comment:

Sure, I understand that you want to know why EF + Network = slow.

The above was to say that our experience is that EF + 100,000 records = slow, regardless of the deployment topology. So, my guess is that the performance difference you are seeing is not EF related

It may be due to the protocols enabled on SQL Server (via Sql Server Configuration Manager) - if you have Shared Memory enabled on the server, you might expect an app living on the same server as the db to run faster than one on a separate server, as the second scenario must talk over TCP/IP.

You might try disabling Shared Memory and seeing what impact that has on same-server performance.

We have never seem this kind of impact, and I'm curious to see the final resolution to the question.

Ackroydd
  • 1,482
  • 1
  • 13
  • 14
1

Did you have a look at this question and Ladislav Mrnka's answer in particular?

Community
  • 1
  • 1
MaxSC
  • 4,698
  • 2
  • 23
  • 36