Performance Comparison between Entity Framework and Stored Procedures

database-performance entity-framework performance-testing stored-procedures

Question

I'm attempting to quantify the speed difference between Stored Procedures and Entity Framework. I want to persuade my supervisor to allow us to adopt Entity Framework for development simplicity.

The issue is that, according to a performance test I did, EF is around 7 times slower than Stored Procs. I have a really difficult time accepting this, and I'm wondering if I'm missing anything. Is this an indisputable Test? Is there anything I can do to improve the EF Test's performance?

        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:

10000 queries in SP took 2278

Entity: 10000 queries, 16277 total

1
62
3/16/2012 2:35:57 PM

Accepted Answer

You may take a few actions to improve your inquiry. You may get an excellent summary at This is MSDN.

However, a stored process with manual mapping will always execute more quickly. But how significant is performance, really? Performance is seldom more crucial than development time in projects. What took more time to develop? The Entity Framework query or the raw query with parsing?

Because ORMs outperform a handwritten technique so lot better, they are not developed. Because they make development so much simpler, we utilize them.

You can create an application quickly if you use the Entity Framework and conceal all of your queries behind a repository design. When performance problems arise, you can measure your application to find the bottleneck. Then maybe some of your queries can be transferred to stored procedures and manual mapping if they need to be optimized.

86
5/7/2016 7:05:15 AM

Popular Answer

I concur with @Wouter de Kort. Additionally, you may utilize EF together with procedures to aid migration from one to the other when you need to switch to procedures.

In a normal application, transitioning to processes will be quicker if you functional integration into well built procedures. i.e. Complete as much work as you can within the one sproc call. When a customer presses the check-out button in a shopping cart MVC project, for instance, you might utilize the ORM to do something like:

  1. check the validity of a user's login by looking up their authentication.
  2. search for permissions (can they buy the specified items? , are there any unique specifications?)
  3. check the stock levels to make sure they weren't drained throughout the procedure
  4. To reserve (remove from the accessible inventory) things before payment, write to the database.
  5. find payment information
  6. logging, etc.

The point is that the MVC application will utilize an ORM to make several calls to the DB in order to go on to the next step, even if the steps are entirely different.

If all of this logic is included in a single, well-written sproc, then all that is required is one call to the sproc. When using the MVC-ORM route, data must first be transferred from the DB to the driver, supplied to the ORM (often over the internet to a separate host), read by the MVC app to make a basic choice, and then the process must be repeated until all steps have been carried out. When utilizing a sproc that incorporates the check-out step, far less data copying and transferring, network IO, context switching, etc. is required.

Consider the MVC-ORM solution in this manner. Person "A" is just aware of the facts, but Person "B" is fully capable of using the information that have been provided to him to make judgments. B emails A to ask for information. Before making a judgment, "B" can ask for a few additional details based on "A's" response. That's a lot of communicating back and forth.

If you just need to ask one question to someone who is fully informed and capable of making judgments, their brain will automatically begin processing the information and producing a response. There is no discussion with another individual. Naturally, it will move more quickly.

That does not imply that it is always better. The ability to change or test these components individually results from the separation of facts from decisions; but, if you are committed to your MVC and database, then this is a "non-issue".

However, many MVC supporters detest writing SQL and see implementing any decision logic in SQL as a catastrophe. Since it expedites their growth, it is essential for such persons to have all logic expressed in the the same language as the MVC notation. Some RDMBS allow you to write sprocs in the same language as the MVC, making this a "non-issue" in such circumstances. Examples include. Use and write Net - SQL Server sprocs in C#. The benefit in this situation is that you can have speedy sprocs that encapsulate numerous tasks in one call and you can use a programming language that you are already proficient in. Postgresql functions (no sprocs) can be written in Perl, Python, PHP, etc.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow