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
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
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.
Also were you using tracked entities in EF 6.1?
https://msdn.microsoft.com/en-us/data/hh949853.aspx#5
LikeLike
Hi! Thanks for your comment.
Disabling entity tracking does not seems like a very easy task in order to improve EF’s performance. Here is an article by Arthur Vickers (of the Microsoft EF team) that shows why you may not want to do it.
http://blog.oneunicorn.com/2012/03/12/secrets-of-detectchanges-part-3-switching-off-automatic-detectchanges/
Thank you again and have a great day!
LikeLike