In an existing system application which I am working on, we need to decide whether we should use entity framework or stored procedure to optimize the performance of our application.

I am not a big fan of Entity Framework but still, I know some of its good features.

Entity Framework uses LINQ which provides a fantastic feature to the developers, like – compile type error checking, IntelliSense at development time, common queries across the databases etc. All these features provide quick development turnaround time.

However, I was not sure whether it provides good performance in comparison to the stored procedure or not. So, I developed a simple console application to compare the results.

I did the same operation once by using EF and the second time with a stored procedure and noted the execution time both times.

Below is my console application followed by the result.

  1. class Program
  2. {
  3. static void Main(string[] args)
  4. {
  5. int queries = 500;
  6. Stopwatch spStopwatch = new Stopwatch();
  7. //stored procedure code
  8. spStopwatch.Start();
  9. for (int i = 0; i < queries; i++)
  10. {
  11. using (var sqlConn = new SqlConnection("Data Source=NJO-LPT-GJAIN;Initial Catalog=BookDb;Integrated Security=True"))
  12. {
  13. var cmd = new SqlCommand("searchBook", sqlConn) { CommandType = CommandType.StoredProcedure };
  14. cmd.Parameters.AddWithValue("@Bookname", "java");
  15. sqlConn.Open();
  16. SqlDataReader dr = cmd.ExecuteReader();
  17. List<book> books = new List<book>();
  18. while (dr.Read())
  19. {
  20. books.Add(new book { BookTitle = dr.GetString(0) });
  21. }
  22. sqlConn.Close();
  23. }
  24. }
  25. spStopwatch.Stop();
  26. Console.WriteLine("Time taken by SP " + spStopwatch.ElapsedMilliseconds);
  27. //Entity framework code
  28. var context = new BookDbEntities();
  29. var bookset = context.Set<Book>();
  30. spStopwatch.Start();
  31. for (int i = 0; i < queries; i++)
  32. {
  33. Book books = bookset.Where(x => x.BookTitle == "java").First();
  34. }
  35. spStopwatch.Stop();
  36. Console.WriteLine("Time taken by EF " + spStopwatch.ElapsedMilliseconds);
  37. Console.ReadLine();
  38. }
  39. }
  40. class book
  41. {
  42. public string BookTitle;
  43. }
  44. }

Entity Framework Vs Stored Procedure

I executed the same application at least 10 times and every time, the time taken by Entity Framework is almost 3-4 times more than the time taken by a stored procedure.

My opinion is that Entity Framework provides a very good feature but can’t beat the performance of the stored procedure because of its precompiled nature.

Please let me know if you have a different opinion.