.net 实体框架太慢了。我有哪些选择？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8347670/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Entity Framework is Too Slow. What are my options?
提问by Vaccano
I have followed the "Don't Optimize Prematurely" mantra and coded up my WCF Service using Entity Framework.
我遵循了“不要过早优化”的口号，并使用实体框架对我的 WCF 服务进行了编码。
However, I profiled the performance and Entity Framework is too slow. (My app processes 2 messages in about 1.2 seconds, where the (legacy) app that I am re-writing does 5-6 messages in the same time. (The legacy app calls sprocs for its DB Access.)
但是，我分析了性能和实体框架太慢了。（我的应用程序在大约 1.2 秒内处理 2 条消息，其中我正在重写的（旧版）应用程序同时处理 5-6 条消息。（旧版应用程序为其 DB Access 调用 sprocs。）
My profiling points to Entity Framework taking the bulk of the time per message.
我的分析指出实体框架占用了每条消息的大部分时间。
So, what are my options?
那么，我有哪些选择？
Are there better ORMs out there?
(Something that just supports normal reading and writing of objects and does it fast..)Is there a way to make Entity Framework faster?
(Note: when I say faster I mean over the long run, not the first call. (The first call is slow (15 seconds for a message), but that is not a problem. I just need it to be fast for the rest of the messages.)Some mysterious 3rd option that will help me get more speed out of my service.
有更好的 ORM 吗？
（只支持正常读写对象并且速度很快的东西..）有没有办法让实体框架更快？
（注意：当我说更快时，我的意思是从长远来看，而不是第一次通话。（第一次通话很慢（消息为 15 秒），但这不是问题。我只需要它在其余部分快速的消息。）一些神秘的第三个选项将帮助我从我的服务中获得更快的速度。
NOTE:Most of my DB interactions are Create and Update. I do very very little selecting and deleting.
注意：我的大多数数据库交互都是创建和更新。我很少选择和删除。
采纳答案by J. Tihon
You should start by profiling the SQL commands actually issued by the Entity Framework. Depending on your configuration (POCO, Self-Tracking entities) there is a lot room for optimizations. You can debug the SQL commands (which shouldn't differ between debug and release mode) using the ObjectSet<T>.ToTraceString()method. If you encounter a query that requires further optimization you can use some projections to give EF more information about what you trying to accomplish.
您应该首先分析实体框架实际发出的 SQL 命令。根据您的配置（POCO、自我跟踪实体），有很大的优化空间。您可以使用该ObjectSet<T>.ToTraceString()方法调试 SQL 命令（在调试模式和发布模式之间应该没有区别）。如果您遇到需要进一步优化的查询，您可以使用一些投影为 EF 提供有关您尝试完成的操作的更多信息。
Example:
例子：
Product product = db.Products.SingleOrDefault(p => p.Id == 10);
// executes SELECT * FROM Products WHERE Id = 10
ProductDto dto = new ProductDto();
foreach (Category category in product.Categories)
// executes SELECT * FROM Categories WHERE ProductId = 10
{
dto.Categories.Add(new CategoryDto { Name = category.Name });
}
Could be replaced with:
可以替换为：
var query = from p in db.Products
where p.Id == 10
select new
{
p.Name,
Categories = from c in p.Categories select c.Name
};
ProductDto dto = new ProductDto();
foreach (var categoryName in query.Single().Categories)
// Executes SELECT p.Id, c.Name FROM Products as p, Categories as c WHERE p.Id = 10 AND p.Id = c.ProductId
{
dto.Categories.Add(new CategoryDto { Name = categoryName });
}
I just typed that out of my head, so this isn't exactly how it would be executed, but EF actually does some nice optimizations if you tell it everything you know about the query (in this case, that we will need the category-names). But this isn't like eager-loading (db.Products.Include("Categories")) because projections can further reduce the amount of data to load.
我只是在我的脑海里输入了它，所以这并不是它的执行方式，但是如果你告诉它你知道的关于查询的一切（在这种情况下，我们将需要类别 -名称）。但这与预先加载 (db.Products.Include("Categories")) 不同，因为投影可以进一步减少要加载的数据量。
回答by Sean
The fact of the matter is that products such as Entity Framework will ALWAYS be slow and inefficient, because they are executing lot more code.
事实是，诸如实体框架之类的产品总是缓慢且低效，因为它们正在执行更多代码。
I also find it silly that people are suggesting that one should optimize LINQ queries, look at the SQL generated, use debuggers, pre-compile, take many extra steps, etc. i.e. waste a lot of time. No one says - Simplify! Everyone wants to comlicate things further by taking even more steps (wasting time).
我还发现人们建议优化 LINQ 查询、查看生成的 SQL、使用调试器、预编译、采取许多额外步骤等的建议很愚蠢。即浪费了大量时间。没有人说 - 简化！每个人都想通过采取更多步骤（浪费时间）使事情进一步复杂化。
A common sense approach would be not to use EF or LINQ at all. Use plain SQL. There is nothing wrong with it. Just because there is herd mentality among programmers and they feel the urge to use every single new product out there, does not mean that it is good or it will work. Most programmers think if they incorporate every new piece of code released by a large company, it is making them a smarter programmer; not true at all. Smart programming is mostly about how to do more with less headaches, uncertainties, and in the least amount of time. Remember - Time! That is the most important element, so try to find ways not to waste it on solving problems in bad/bloated code written simply to conform with some strange so called 'patterns'
一种常识方法是根本不使用 EF 或 LINQ。使用普通 SQL。没有什么问题。仅仅因为程序员中有从众心理，他们有使用那里的每一个新产品的冲动，并不意味着它是好的或行得通的。大多数程序员认为，如果他们整合了大公司发布的每段新代码，就会使他们成为更聪明的程序员；根本不是真的。智能编程主要是关于如何以更少的头痛、不确定性和最少的时间做更多的事情。记住——时间！这是最重要的元素，所以尽量找到方法，不要把它浪费在解决糟糕/臃肿代码中的问题上，这些代码只是为了符合一些奇怪的所谓“模式”而编写的
Relax, enjoy life, take a break from coding and stop using extra features, code, products, 'patterns'. Life is short and the life of your code is even shorter, and it is certainly not rocket science. Remove layers such as LINQ, EF and others, and your code will run efficiently, will scale, and yes, it will still be easy to maintain. Too much abstraction is a bad 'pattern'.
放松，享受生活，从编码中休息一下，停止使用额外的功能、代码、产品和“模式”。人生苦短，代码的生命更短，这当然不是火箭科学。删除 LINQ、EF 等层，您的代码将高效运行，可扩展，是的，它仍然易于维护。太多的抽象是一种糟糕的“模式”。
And that is the solution to your problem.
这就是您问题的解决方案。
回答by Steve Wortham
One suggestion is to use LINQ to Entity Framework only for single-record CRUD statements.
一种建议是仅将 LINQ to Entity Framework 用于单记录 CRUD 语句。
For more involved queries, searches, reporting, etc, write a stored procedure and add it to the Entity Framework model as described on MSDN.
对于更多涉及的查询、搜索、报告等，请编写存储过程并将其添加到实体框架模型中，如MSDN 上所述。
This is the approach I've taken with a couple of my sites and it seems to be a good compromise between productivity and performance. Entity Framework will not always generate the most efficient SQL for the task at hand. And rather than spending the time to figure out why, writing a stored procedure for the more complex queries actually saves time for me. Once you're familiar with the process, it's not too much of a hassle to add stored procs to your EF model. And of course the benefit of adding it to your model is that you get all that strongly typed goodness that comes from using an ORM.
这是我在我的几个网站上采用的方法，它似乎是生产力和性能之间的一个很好的折衷方案。实体框架并不总是为手头的任务生成最有效的 SQL。而不是花时间找出原因，为更复杂的查询编写存储过程实际上为我节省了时间。一旦您熟悉了该过程，将存储过程添加到您的 EF 模型并不会太麻烦。当然，将它添加到您的模型中的好处是您可以获得使用 ORM 所带来的所有强类型优点。
回答by JulianR
If you're purelyfetching data, it's a big help to performance when you tell EF to not keep track of the entities it fetches. Do this by using MergeOption.NoTracking. EF will just generate the query, execute it and deserialize the results to objects, but will not attempt to keep track of entity changes or anything of that nature. If a query is simple (doesn't spend much time waiting on the database to return), I've found that setting it to NoTracking can double query performance.
如果您纯粹是在获取数据，那么当您告诉 EF 不要跟踪它获取的实体时，这对性能有很大帮助。使用 MergeOption.NoTracking 执行此操作。EF 只会生成查询，执行它并将结果反序列化为对象，但不会尝试跟踪实体更改或任何此类性质的内容。如果查询很简单（不会花太多时间等待数据库返回），我发现将其设置为 NoTracking 可以使查询性能加倍。
See this MSDN article on the MergeOption enum:
请参阅有关 MergeOption 枚举的 MSDN 文章：
Identity Resolution, State Management, and Change Tracking
This seems to be a good article on EF performance:
这似乎是一篇关于 EF 性能的好文章：
回答by Sean Kearon
You say that you have profiled the application. Have you profiled the ORM too? There is an EF profiler from Ayende that will highlight where you can optimise your EF code. You can find it here:
您说您已经对应用程序进行了概要分析。你也分析过 ORM 吗？Ayende 提供了一个 EF 分析器，可以突出显示可以优化 EF 代码的位置。你可以在这里找到它：
Remember that you can use a traditional SQL approach alongside your ORM if you need to to gain performance.
请记住，如果需要提高性能，您可以将传统的 SQL 方法与 ORM 一起使用。
If there a faster/better ORM? Depending on your object/data model, you could consider using a one of the micro-ORMs, such as Dapper, Massiveor PetaPoco.
如果有更快/更好的 ORM？根据您的对象/数据模型，您可以考虑使用微 ORM 之一，例如Dapper、Massive或PetaPoco。
The Dapper site publishes some comparitive benchmarks that will give you an idea how they compare to other ORMs. But it's worth noting that the micro-ORMs do not support the rich feature set of the full ORMs like EF and NH.
Dapper 站点发布了一些比较基准，可以让您了解它们与其他 ORM 的比较。但值得注意的是，微 ORM 不支持完整 ORM 的丰富功能集，如 EF 和 NH。
You may want to take a look at RavenDB. This is a non-relational database (from Ayende again) that lets you store POCOs directly with no mapping needed. RavenDB is optimised for reads and makes the developers life a whole lot easier by removing need to manipulate schema and to map your objects to that schema. However, be aware that this is a significantly different approach to using an ORM approach and these are outlined in the product's site.
您可能想看看RavenDB。这是一个非关系数据库（再次来自 Ayende），可让您直接存储 POCO，无需映射。RavenDB 针对读取进行了优化，通过消除操作模式和将对象映射到该模式的需要，使开发人员的生活变得更加轻松。但是，请注意，这是使用 ORM 方法的一种截然不同的方法，产品站点中概述了这些方法。
回答by MemeDeveloper
回答by Valera Kolupaev
From my experience, the problem not with EF, but with ORM approach itself.
根据我的经验，问题不在于 EF，而在于 ORM 方法本身。
In general all ORMs suffers from N+1problem not optimized queries and etc. My best guess would be to track down queries that causes performance degradation and try to tune-up ORM tool, or rewrite that parts with SPROC.
一般来说，所有 ORM 都存在N+1问题，而不是优化查询等。我最好的猜测是追踪导致性能下降的查询并尝试调整 ORM 工具，或使用 SPROC 重写该部分。
回答by Andrew
This is simple non-framework, non-ORM option that loads at 10,000/second with 30 fields or so. Running on an old laptop, so probably faster than that in a real environment.
这是一个简单的非框架、非 ORM 选项，以 10,000/秒的速度加载，包含 30 个左右的字段。在旧笔记本电脑上运行，因此可能比在真实环境中更快。
https://sourceforge.net/projects/dopersistence/?source=directory
https://sourceforge.net/projects/dopersistence/?source=directory
回答by TheMiddleMan
I ran into this issue as well. I hate to dump on EF because it works so well, but it is just slow. In most cases I just want to find a record or update/insert. Even simple operations like this are slow. I pulled back 1100 records from a table into a List and that operation took 6 seconds with EF. For me this is too long, even saving takes too long.
我也遇到了这个问题。我讨厌放弃 EF，因为它运行良好，但速度很慢。在大多数情况下，我只想查找记录或更新/插入。即使像这样简单的操作也很慢。我将 1100 条记录从表中提取到列表中，并且该操作使用 EF 耗时 6 秒。对我来说这太长了，甚至储蓄也需要太长时间。
I ended up making my own ORM. I pulled the same 1100 records from a database and my ORM took 2 seconds, much faster than EF. Everything with my ORM is almost instant. The only limitation right now is that it only works with MS SQL Server, but it could be changed to work with others like Oracle. I use MS SQL Server for everything right now.
我最终制作了自己的 ORM。我从数据库中提取了相同的 1100 条记录，我的 ORM 用了 2 秒，比 EF 快得多。我的 ORM 几乎是即时的。现在唯一的限制是它只能与 MS SQL Server 一起使用，但可以更改为与其他人一起使用，例如 Oracle。我现在一切都使用 MS SQL Server。
If you would like to try my ORM here is the link and website:
如果你想尝试我的 ORM，这里是链接和网站：
https://github.com/jdemeuse1204/OR-M-Data-Entities
https://github.com/jdemeuse1204/OR-M-Data-Entities
Or if you want to use nugget:
或者，如果您想使用 nugget：
PM> Install-Package OR-M_DataEntities
PM> Install-Package OR-M_DataEntities
Documentation is on there as well
文档也在那里
回答by tfa
I used EF, LINQ to SQL and dapper. Dapper is the fastest. Example: I needed 1000 main records with 4 sub records each. I used LINQ to sql, it took about 6 seconds. I then switched to dapper, retrieved 2 record sets from the single stored procedure and for each record added the sub records. Total time 1 second.
我使用了 EF、LINQ to SQL 和 dapper。Dapper 是最快的。示例：我需要 1000 个主记录，每个记录有 4 个子记录。我用的是LINQ to sql，大概用了6秒。然后我切换到 dapper，从单个存储过程中检索 2 个记录集，并为每个记录添加子记录。总时间 1 秒。
Also the stored procedure used table value functions with cross apply, I found scalar value functions to be very slow.
此外，存储过程使用交叉应用的表值函数，我发现标量值函数非常慢。
My advice would be to use EF or LINQ to SQL and for certain situations switch to dapper.
我的建议是使用 EF 或 LINQ to SQL，并在某些情况下切换到 dapper。
