CODEWITHSUNDEEP
.netentity-framework
SSK
SSK

Reputation: 803

Which one should i choose Entity Framework or ADO.Net?

We are having a task to insert around 5000 records into a SQL table. Initially we went through EF and here is the code sample

foreach (var BCol in batchCollectionoutput1)
{
    var context1 = new TestENTITIES.TestDbContext();
   context1.Database.CommandTimeout = 6000;
   context1.Configuration.AutoDetectChangesEnabled = false;
   context1.Configuration.ValidateOnSaveEnabled = false;

  foreach (TestENTITIES.Output1 test in BCol )
    context1.Output1.Add(test);

    context1.SaveChanges();
  }

it takes 43412 milliseconds

after which we wanted to check using SQL Bulk copy method

using (SqlConnection sourceConnection = new SqlConnection(srcConnectionString))
 {
     SqlCommand myCommand1 =  new SqlCommand("SELECT TOP 1000 * FROM input", sourceConnection);

       sourceConnection.Open();
       SqlDataReader reader1 = myCommand1.ExecuteReader();

       using (SqlConnection destinationConnection = new SqlConnection(destConnectionString))
       {
            destinationConnection.Open();

           using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
           {
             bulkCopy.BatchSize = 500;
             bulkCopy.NotifyAfter = 1000;
             bulkCopy.DestinationTableName = "Output1";
             bulkCopy.WriteToServer(reader1);

          }

    reader1.Close();

                }
            }

which takes only 1124 milliseconds..

is that something we are doing wrong in Entity framework method? Please advise

Upvotes: 1

Views: 615

Answers (2)

Jesper
Jesper

Reputation: 7605

As Mark's comment says, you're not making a direct comparison because you're calling SaveChanges in every iteration of the loop.

There are three ways to do this:

  1. Run every row insert in its own transaction and database roundtrip. Very slow.

  2. Run every row insert batched up in one transaction and database roundtrip.

  3. Make a direct bulk copy. Very fast.

Your EF code is doing 1. You could be doing 2 by moving SaveChanges outside of the loop. You can't do 3. with EF, but you can't do 3. with "just" the usual ADO.NET machinery either (with just SQL commands) - you need a special facility (SqlBulkCopy) for it.

Upvotes: 0

amcdermott
amcdermott

Reputation: 1585

No - you would expect EF to be slower than BulkInsert.

EF is more suited for scenarios where you want to retrieve data from your DB, map the results into a POCO, apply some changes and then save them. The real benefit is that it is database agnostic and the developer doesn't need to worry about the plumbing of creating database connections, commands etc.

Where you want to load data from an import file or similar Bulk Copy is certainly the way to go from a performance perspective.

Upvotes: 3

Related Questions