How fast is classic ADO.net compared to Entity Framework?

Or maybe I should write: how slower is Entity Framework as compared to ADO.Net?

By Entity Framework I mean Microsoft’s open source package that allows you to manage DB objects via strongly-typed classes and collections.

By ADO.Net I mean peeking into the DB using the old ADO objects SQLConnection, SQLCommand, SQLParameters

This is the little test (note that it is a very peculiar test because rarely will you in real life insert, update and delete objects one by one: more massive operations are more likely):

– we create two table: Books and Authors. They are related via Author_Id, which is on the Books table.

– we insert 1000 authors and 1000 books

. we update 1000 books with a new title (one by one)

– we delete 1000 books (one by one)

– DB Is SQLserver version 11, running on a quad-core i5 @1.9 Ghz running Windows 8

– Server is a Windows 8 machine with 8Gb Gb RAM

The code for Entity Framework?

Book Model

namespace FastEF.Models
{
 public class Book
 {
 public int Id { get; set; }
 public string Title { get; set; }
 public Author Author { get; set; }
 
 }
}

Author Model

namespace FastEF.Models
{
 public class Author
 {
 public int Id { get; set; }
 public string Name { get; set; }
 public string Address { get; set; }
 public ICollection<Book> Books { get; set; }
}
}

DbContext

namespace FastEF.Models
{
 public class DBCreator:DbContext
 {
 public DbSet<Book> Books { get; set; }
 public DbSet<Author> Authors { get; set; }
}
}

Then, the action from Entity Framework test, which:

– inserts 1000 auhors and 1000 books related to the authors

– updates the 1000 books

– deletes the 1000 books


 public ActionResult EF()
        {
            Book bookToCreate = new Book();
            Author authorToCreate = new Author();
            Stopwatch tellTime = new Stopwatch();
            long insertingTime = 0;
            long updatingTime = 0;
            long deletingTime = 0;
            List generatedBookIds = new List();

            // let us delete table contents
            try
            {
                var objCtx = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)thisDB).ObjectContext;
                objCtx.ExecuteStoreCommand("DELETE FROM Books");
                objCtx.ExecuteStoreCommand("DELETE FROM Authors");

            }


            catch (Exception e)
            {
                // write exception. Maybe it's the first time we run this and have no tables
                Debug.Write("Error in truncating tables: {0}", e.Message);

            }

            // let us start the watch
            tellTime.Start();

            // INSERTING!
            // we create 1000 authors with name="John Doe nr: " + a GUID
            // and address ="5th Avenue nr: " + a GUID
            // we create a book called "The Cronicles of: " + a GUID and attach it to the author
            // we save the book, so the author is also automatically created

            for (int i = 0; i < 1000; i++)
            {

                // creating author
                authorToCreate = new Author();
                authorToCreate.Name = "John Doe nr. " + Guid.NewGuid();
                authorToCreate.Address = "5th Avenue nr. " + Guid.NewGuid();

                //creating book and linking it to the author
                bookToCreate = new Book();
                bookToCreate.Title = "The Chronicles of: " + Guid.NewGuid();
                bookToCreate.Author = authorToCreate;

                //saving the book. Automatically, the author is saved
                thisDB.Books.Add(bookToCreate);
                thisDB.SaveChanges();
                generatedBookIds.Add(bookToCreate.Id);
            }

            insertingTime = tellTime.ElapsedMilliseconds; // how did I do with inserting?

            tellTime.Restart(); // restart timer

            // We update the 1000 books by changing their title
            foreach (int bookId in generatedBookIds)
            {

                Book bookToUpdate = thisDB.Books.Find(bookId);
                bookToUpdate.Title = "New chronicles of: " + Guid.NewGuid();

                thisDB.SaveChanges();

            }

            updatingTime = tellTime.ElapsedMilliseconds; // how did I do with inserting?
            tellTime.Restart(); // restart timer

            // We delete 1000 books, one by one
            foreach (int bookId in generatedBookIds)
            {

                Book bookToDelete = thisDB.Books.Find(bookId);
                thisDB.Books.Remove(bookToDelete);

            }

            deletingTime = tellTime.ElapsedMilliseconds; // how did I do with inserting?
            tellTime.Stop(); // stop timer


            //printing the results 

            string returnedMessage = "Results with Entity Framwork 6.1: ";
            returnedMessage += "
1000 Insert operations in ms.: " + insertingTime.ToString(); returnedMessage += "
1000 Update operations in ms.: " + updatingTime.ToString(); returnedMessage += "
1000 Delete operations in ms.: " + deletingTime.ToString(); return Content(returnedMessage); }

The code for ADO.Net?

 public ActionResult SQLClient()
        {

            string insertAuthorSQL = "INSERT INTO Authors (Name, Address) VALUES (@name, @address)";
            string insertBookSQL = "INSERT INTO Books(Title, Author_Id) VALUES (@Title, @Author_Id)";
            string updateBookSQL = "UPDATE Books Set Title=@Title where Id=@Id";
            string deleteBookSQL = "DELETE Books where Id=@Id";

            Book bookToCreate = new Book();
            Author authorToCreate = new Author();
            Stopwatch tellTime = new Stopwatch();

            // SQL Objects we will use
            SqlConnection connAntiEF = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString());
            SqlCommand cmdAntiEF = new SqlCommand();

            // Open Connection
            connAntiEF.Open();

            long insertingTime = 0;
            long updatingTime = 0;
            long deletingTime = 0;
            List generatedBookIds = new List();

            // let us delete table contents
            try
            {
                cmdAntiEF = new SqlCommand("DELETE FROM Books", connAntiEF);
                cmdAntiEF.ExecuteNonQuery();
                cmdAntiEF = new SqlCommand("DELETE FROM Authors", connAntiEF);
                cmdAntiEF.ExecuteNonQuery();
            }


            catch (Exception e)
            {
                // write exception. 
                Debug.Write("Error in truncating tables: {0}", e.Message);

            }

            // let us start the watch
            tellTime.Start();

            // INSERTING!
            // we create 1000 authors with name="John Doe nr: " + a GUID
            // and address ="5th Avenue nr: " + a GUID
            // we create a book called "The Cronicles of: " + a GUID and attach it to the author
            // we save the book, so the author is also automatically created

            for (int i = 0; i < 1000; i++)
            {

                // creating author
                authorToCreate = new Author();
                authorToCreate.Name = "John Doe nr. " + Guid.NewGuid();
                authorToCreate.Address = "5th Avenue nr. " + Guid.NewGuid();

                //creating book and linking it to the author
                bookToCreate = new Book();
                bookToCreate.Title = "The Chronicles of: " + Guid.NewGuid();
                bookToCreate.Author = authorToCreate;

                // INSERT book with SQL and get its Id


                SqlParameter parmName = new SqlParameter("Name", authorToCreate.Name);
                SqlParameter parmAddress = new SqlParameter("Address", authorToCreate.Address);
                cmdAntiEF.CommandText = insertAuthorSQL;
                cmdAntiEF.Parameters.Add(parmName);
                cmdAntiEF.Parameters.Add(parmAddress);
                cmdAntiEF.ExecuteNonQuery();

                cmdAntiEF.Parameters.Clear();
                cmdAntiEF.CommandText = "SELECT @@IDENTITY";

                int insertedAuthorID = Convert.ToInt32(cmdAntiEF.ExecuteScalar());

                // INSERT book with SQL and get its Id


                parmName = new SqlParameter("title", bookToCreate.Title);
                parmAddress = new SqlParameter("author_id", insertedAuthorID);

                cmdAntiEF.CommandText = insertBookSQL;
                cmdAntiEF.Parameters.Add(parmName);
                cmdAntiEF.Parameters.Add(parmAddress);
                cmdAntiEF.ExecuteNonQuery();

                // we neeed the book's Id to iterate through the Id's later
                cmdAntiEF.CommandText = "SELECT @@IDENTITY";
                int insertedBookID = Convert.ToInt32(cmdAntiEF.ExecuteScalar());
                generatedBookIds.Add(insertedBookID);


                parmName = null;
                parmAddress = null;
                cmdAntiEF.Parameters.Clear();

            }


            insertingTime = tellTime.ElapsedMilliseconds; // how did I do with inserting?

            tellTime.Restart(); // restart timer

            // We update 1000 books by changing their title
            cmdAntiEF.CommandText = updateBookSQL;
            foreach (int bookId in generatedBookIds)
            {

                //parameters are loaded with the book's new data
                SqlParameter parmTitle = new SqlParameter("Title", "New chronicles of: " + Guid.NewGuid());
                SqlParameter parmId = new SqlParameter("Id", bookId);
                cmdAntiEF.Parameters.Add(parmTitle);
                cmdAntiEF.Parameters.Add(parmId);

                cmdAntiEF.ExecuteNonQuery();
                parmTitle = null;
                cmdAntiEF.Parameters.Clear();

            }

            updatingTime = tellTime.ElapsedMilliseconds; // how did I do with inserting?
            tellTime.Restart(); // restart timer

            // We delete 1000 books one by one
            cmdAntiEF.CommandText = deleteBookSQL;
            foreach (int bookId in generatedBookIds)
            {
                SqlParameter parmId = new SqlParameter("Id", bookId);
                cmdAntiEF.Parameters.Add(parmId);
                cmdAntiEF.ExecuteNonQuery();
                parmId = null;
                cmdAntiEF.Parameters.Clear();
            }

            connAntiEF.Close();

            deletingTime = tellTime.ElapsedMilliseconds; // how did I do with inserting?
            tellTime.Stop(); // stop timer

            // printing the results
            string returnedMessage = "Results with SQL Connection: ";
            returnedMessage += "
1000 Insert operations in ms.: " + insertingTime.ToString(); returnedMessage += "
1000 Update operations in ms.: " + updatingTime.ToString(); returnedMessage += "
1000 Delete operations in ms.: " + deletingTime.ToString(); return Content(returnedMessage); }

How did they do?

Entity Framework

Results with Entity Framwork 6.1:
1000 Insert operations in ms.: 11355
1000 Update operations in ms.: 20833
1000 Delete operations in ms.: 18117

Entity framework performance

Adding, updating, deleting 1000 sqlserver objects via EF

CPU average use: 35%

Memory average use: 65%

ADO.Net

Results with SQL Connection:
1000 Insert operations in ms.: 921
1000 Update operations in ms.: 309
1000 Delete operations in ms.: 311

ado.net insert and update and delete

Inserting, updating, deleting sql server objects via ado

How to interpret the results?

They cannot be compared, because using EF means using objects rather than non-typed records.

So, I keep on thinking ORMs are the way to go.

However, if one day I was asked to speed up parts of an application that is slow when reading / writing data, I would know where to go and look for possible ameliorations.