实体框架与存储过程 - 性能测量 [英] Entity Framework Vs Stored Procedures - Performance Measure

问题描述

我正在尝试建立多少比较慢的实体框架超过存储过程。我希望说服我的老板让我们使用实体框架来方便开发。

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.

问题是我进行了一个性能测试，看起来像EF是大约7倍存储产品我觉得这很难相信，我想知道我是否缺少一些东西。这是一个决定性的测试吗？有什么可以做的，以提高EF测试的性能？

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);

结果：

SP - 10000查询花了2278

SP - 10000 Queries took 2278

实体 - 10000个查询花费了16277

Entity - 10000 Queries took 16277

推荐答案

您可以采取一些措施来优化查询。 MSDN上的可以找到一个很好的概述。

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?

ORM不是设计的，因为它们执行得比手写方式好得多。我们使用它们，因为开发更容易！

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.

这篇关于实体框架与存储过程 - 性能测量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！