Entity Framework vs Stored Procedure


When developer queries SQL Server for data, Entity Framework (EF) and Stored Procedure (SP) are 2 of the most common options. Often, individual preferences could be debatable.

In an existing system I am working on, there are about 40+ stored procedures being implemented.

Stored procedure adds complexity for maintenance. For example, you need to ensure certain version of the SP is compatible with certain version of the codes. During deployment, you need to deploy code + SP instead of just codes. Imagine rolling back changes if your deployment fails. Another factor is debugging. Debugging codes is obviously more pleasant than debugging SP.

I prefer to only use my database as a medium of storage. I like to keep the database as simple and clean as possible and off load other works to external components or services that are best at doing what they are designed for. For example, if I need fast searching I prefer index service such as Solr / Azure Search by designing relevant facets rather than building up big indexes that add storage overhead to database; if I need to process business logic, I will do it in application because it is much easier to scale an application over a database.

Having said that, SP has it’s advantage such as being pre-compiled, it is included in the execution plan hence has performance advantage. The reusability factor of SP is debatable because if the codes is structured in a reusable manner, the codes that stored the database logic can definitely be reused just like a SP.

Since the system I’m working on has a bunch of SP (and the main argument is performance). Out of curiosity, I did a performance profiling comparing EF vs SP on an actual database.

Some initialization codes…

            int loop = 10;
            Guid id = Guid.Parse("XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX");
            string someId = "XXXXXXXX";

            DateTime now = DateTime.Now;
            Stopwatch stopWatch = new Stopwatch();
            Stopwatch stopWatchScope = new Stopwatch();
            string milisecond = string.Empty;
            TimeSpan ts = new TimeSpan();

Codes to query database using Entity Framework (with LINQ) that I re-implement base on an existing stored procedure:

            MyDatabaseEntities context = new MyDatabaseEntities();
            Console.WriteLine("Time for Entity Framework");
            for (int i = 0; i < loop; i++)
                var query = from c in context.Customers
                            join cs in context.CustomerStatus on c.PK_CustomerStatus equals cs.PK_CustomerStatus
                            join p in context.Programs on c.ProgramID equals p.ProgramID
                            where c.CustomerID == id
                                && (cs.CustomerStatusCode == "Condition 1" || cs.CustomerStatusCode == "Condition 2")
                                && p.someID == someId
                                && p.StartDate < now && p.EndDate > now
                            select c;
                Customer customer = query.ToList().FirstOrDefault();
                var a = customer.CustomerID;
                ts = stopWatch.Elapsed;
                milisecond = ts.TotalMilliseconds.ToString();
                Console.WriteLine($"Attempt {i}: {milisecond} milisecond");

            ts = stopWatchScope.Elapsed;
            milisecond = ts.TotalMilliseconds.ToString();
            Console.WriteLine($"=>Total time for EF: {milisecond} milisecond");

Codes to query database using Stored Procedure:

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=xxx-xxx-xxx-xxx.database.windows.net;" +
                "Initial Catalog=xxxxxxx;" +
                "User id=xxxxxx;" +

            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.CommandText = "MyExistingSimpleStoredProcedure";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter parmId = new SqlParameter("@CustomerID", SqlDbType.UniqueIdentifier);
            parmId.Value = id;
            parmId.Direction = ParameterDirection.Input;
            SqlParameter parmSomeId = new SqlParameter("@someId", SqlDbType.VarChar);
            parmSomeId.Value = someId;
            parmSomeId.Direction = ParameterDirection.Input;

            cmd.Connection = conn;

            Console.WriteLine("Time for Stored Procedure");
            for (int i = 0; i < loop; i++)
                reader = cmd.ExecuteReader();
                ts = stopWatch.Elapsed;
                milisecond = ts.TotalMilliseconds.ToString();
                Console.WriteLine($"Attempt {i}: {milisecond} milisecond");

            ts = stopWatchScope.Elapsed;
            milisecond = ts.TotalMilliseconds.ToString();
            Console.WriteLine($"=>Total time for SP: {milisecond} milisecond");

Where this is the query in the stored procedure:

		C.EndDate As CustomerEndDate,
		C.StartDate As CustomerStartDate,
	From dbo.Customer  C with (nolock)
	Inner Join [dbo].CustomerStatus CS with (nolock) On C.PK_CustomerStatus = CS.PK_CustomerStatus 	And   cs.CustomerStatusCode in ('Condition 1','Condition 2') 
	Inner Join Program P  with (nolock)  On P.ProgramID  = C.ProgramID
		Where  C.CustomerID = @CustomerID
		AND P.SomeID = @someId
		AND (@date BETWEEN P.StartDate AND P.EndDate)

Here are the result after 5 rounds of execution:

Stored Procedure won

Stored Procedure won

Entity Framework won

Entity Framework won

Stored Procedure won

The overall winner is Stored Procedure, where Stored Procedure won 3 times while Entity Framework won 2 times.

A few interesting insight from the profiling:

  1. Stored Procedure performed marginally better in overall.
  2. Entity Framework is marginally slower but it is not as slow as making Stored Procedure a clear winner.
  3. The first call for Entity Framework is significantly higher compare to the consecutive calls. If we were to exclude initial call in EF, EF will be a clear winner.

Choosing between Entity Framework or Stored Procedure might not be straight forward especially if you have an existing application that has massive implementation in either approach. However here are some guidelines to make your decision making less painful.

  1. If you have an existing system that has massive implementation on either approach, stick with the existing approach.
  2. If you have existing development team members who have strong background in stored procedure or entity framework respectively, continue to tap onto their strength.
  3. If you are starting a new project, go for Entity Framework for ease of development and good maintainability although the cost to pay is slight performance downgrade.

Until next time!