ORM选择n + 1个性能；加入或不加入 [英] ORM Select n + 1 performance; join or no join
问题描述
也有类似的问题，但我认为没有人问过这个特定问题.
There are similar questions to this, but I don't think anyone has asked this particular question.
场景:
客户-订单(其中订单具有客户ID)-OrderPart-零件
Customer - Order (where Order has a CustomerID) - OrderPart - Part
我想要一个查询，该查询返回一个包含所有订单以及每个订单及其零件的客户.
I want a query that returns a customer with all its orders and each order with its parts.
现在我有两个主要选择:
Now I have two main choices:
- 使用嵌套循环(产生单独的查询)
- 使用数据加载选项(产生单个查询联接)
问题:
关于ORM的大多数建议和示例都建议使用选项2，我可以理解为什么.但是，选项2可能会发送回大量重复的数据，例如:
Most advice and examples on ORMs suggest using option 2 and I can see why. However, option 2 will potentially be sending back a huge amount of duplicated data, eg:
选项1的结果(3个查询):
Option 1 results (3 queries):
ID Name Country
1 Customer1 UK
ID Name
1 Order1
2 Order2
ID Name
1 Part1
2 Part2
3 Part3
选项2的结果(1个查询):
Option 2 results (1 query):
ID Name Country ID Name ID Name
1 Customer1 UK 1 Order1 1 Part1
1 Customer1 UK 1 Order1 2 Part2
1 Customer1 UK 1 Order1 3 Part3
1 Customer1 UK 2 Order2 1 Part1
1 Customer1 UK 2 Order2 2 Part2
选项1发回带有3个查询的13个字段.选项2在1个查询中发回42个字段.现在，假设客户表有30个字段，而订单具有更复杂的子联接，那么数据重复会迅速变得庞大.
Option 1 sends back 13 fields with 3 queries. Option 2 sends back 42 fields in 1 query. Now imagine Customer table has 30 fields and Orders have more complex sub joins, the data duplication can quickly become huge.
以下内容对整体性能有什么影响?
What impact on overall performance do the following things have:
- 建立数据库连接的开销
- 发送数据所花费的时间(如果在其他服务器上，则可能跨网络)
- 带宽
选项2始终是最佳选择，选项1始终是最佳选择，还是取决于情况?如果需要，应使用什么标准来确定?是否有足够的ORM聪明地自己解决?
Is option 2 always the best choice, option 1 the best choice or does it depend on the situation? If it depends, what criteria should you use to determine? Are any ORMs clever enough to work it out for themselves?
推荐答案
建立数据库连接的开销
如果它们通常位于同一子网中，则很少.如果不是，那么这仍然不是很大的开销，可以通过大多数ORM拥有的缓存来解决( NHibernate 具有第一级和第二级缓存).
Overhead of making a database connection
Very little if they are on the same subnet, which they usually are. If they're not then this is still not a huge overhead and can be overcome with caching, which most ORMs have (NHibernate has 1st and 2nd level caching).
对于SELECT N+1，这显然会更长，因为每次都必须发送select语句，最长可能长达1k.它还必须从池中获取新的连接.在2002年至2003年间，使用Chatty和矮胖的说法一直是争论的焦点，但是除非这是一个非常大的应用程序，否则现在并没有太大的不同，在这种情况下，您可能希望有经验的(或薪水更高的)专家来发表他的观点-即顾问.
For SELECT N+1 this will obviously be longer as it will have to send the select statement each time, which might be up to 1k long. It will also have to grab a new connection from the pool. Chatty versus chunky use to be an argument around 2002-2003 but now it really doesn't make a huge difference unless this is a really big application, in which case you will probably want a more experienced (or better paid) pundit giving his views - i.e. a consultant.
但是，我希望加入连接，因为数据库将在其10或10多年的开发过程中针对这种用法进行优化.如果性能确实很慢，则View可以解决此问题或存储过程.
I would favour joins however, as databases will be optimised for this usage over their 10 or more years of development. If performance is really slow a View can sort this out, or Stored Procedure.
顺便说一句，SELECT N+1可能是人们首次使用NHibernate(包括我)时遇到的最常见的性能问题，并且实际上需要进行调整才能解决.这是因为NHibernate对ORM而言就像C ++对语言一样.
By the way, SELECT N+1 is probably the commonest performance problem people experience with NHibernate when they first start using it (including me), and is something that actually takes tweaking to sort out. This is because NHibernate is to ORMs what C++ is to languages.
每个Customer额外的SELECT语句最终将建立多达许多Customer对象* Orders.因此，对于大型系统而言，这可能很明显-但正如我提到的那样，ORM通常具有适当的缓存机制来消除此问题.考虑到SELECT语句的数量也不会那么大:
An extra SELECT statement for every Customer will eventually build up to however many Customer objects * Orders. So for a large system this might be noticeable - but as I mentioned, ORMs usually have caching mechanisms in place to negate this problem. The amount of SELECT statements also isn't going to be that huge considering:
- 大多数情况下，您与SQL Server处于同一网络上
- 增加的字节数会导致额外的0.5-50k的额外带宽吗?想一想大多数服务器上的速度有多快.
这篇关于ORM选择n + 1个性能；加入或不加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
