Entity Framework Vs Stored Procedures - Performance Measure

Question

I'm trying to establish how much slower Entity Framework is over Stored Procedures. I hope to convince my boss to let us use Entity Framework for ease of development.

Problem is I ran a performance test and it looks like EF is about 7 times slower than Stored Procs. I find this extremely hard to believe, and I'm wondering if I'm missing something. Is this a conclusive Test? Is there anything I can do to increase the performance of the EF Test?

        var queries = 10000;

        //  Stored Proc Test
        Stopwatch spStopwatch = new Stopwatch();
        spStopwatch.Start();
        for (int i = 0; i < queries; i++ )
        {
            using (var sqlConn = new SlxDbConnection().Connection)
            {
                var cmd = new SqlCommand("uspSearchPerformanceTest", sqlConn) { CommandType = CommandType.StoredProcedure };

                cmd.Parameters.AddWithValue("@searchText", "gstrader");
                sqlConn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                List<User> users = new List<User>();
                while (dr.Read())
                {
                    users.Add(new User
                    {
                        IsAnonymous = Convert.ToBoolean(dr["IsAnonymous"]),
                        LastActivityDate = Convert.ToDateTime(dr["LastActivityDate"]),
                        LoweredUserName = dr["LoweredUserName"].ToString(),
                        MobileAlias = dr["MobileAlias"].ToString(),
                        UserId = new Guid(dr["UserId"].ToString()),
                        UserName = (dr["UserName"]).ToString()
                    });
                }

                var username = users.First().UserName;
                sqlConn.Close();
            }
        }
        spStopwatch.Stop();
        Console.WriteLine("SP - {0} Queries took {1}", queries, spStopwatch.ElapsedMilliseconds );

        //  EF  Test
        Stopwatch entityStopWatch = new Stopwatch();

        var context = new SlxDbContext();
        var userSet = context.Set<User>();
        entityStopWatch.Start();
        for (int i = 0; i < queries; i++)
        {
            User user = userSet.Where(x => x.UserName == "gstrader").First();
        }

        entityStopWatch.Stop();
        Console.WriteLine("Entity - {0} Queries took {1}", queries, entityStopWatch.ElapsedMilliseconds);

Result:

SP - 10000 Queries took 2278

Entity - 10000 Queries took 16277

Answer 1

There are some things you can do to optimize your query. Here on MSDN you can find a nice overview.

But to be honest, a stored procedure with manual mapping will always be faster in performance. But ask yourself, how important is performance? In most projects, development time is way more important then performance. What was harder to develop? The raw query with parsing or the Entity Framework query?

ORMs are not designed because they perform so much better than a hand written approach. We use them because development is so much easier!

If you write your application with the Entity Framework and hide all your queries behind a repository pattern you can develop real quick and then, when performance becomes an issue, measure your application to detect the bottleneck. Then maybe some of your queries need optimization and can be moved to stored procedures and manual mapping.