I found a legacy sproc in our system that includes a redundant second join for "performance reasons."
我在我们的系统中发现了一个遗留的sproc，其中包括冗余的第二个连接，这是出于“性能原因”
But if I look at the query execution plan it appears the second join actually increases the query cost by ~30%.
但是如果我查看查询执行计划，那么第二次连接实际上会使查询成本增加约30％。
Does a second join ever improve performance? What might the original author been thinking of when he or she added the second join?
第二次加入是否能提高性能？当他或她添加第二次加入时，原作者可能会想到什么？
2 个解决方案
#1
3
A second join can improve performance. Join's can be used for filtering, which reduces the number of rows.
第二次加入可以提高性能。 Join可以用于过滤，这可以减少行数。
This would be particularly true if the join keys are indexed, so the filtering can take place very quickly.
如果对连接键进行索引，则尤其如此，因此可以非常快速地进行过滤。
The reduced result set can also speed aggregations and sorting.
缩减的结果集还可以加速聚合和排序。
If the result sets are exactly the same, the second join might improve performance by enabling a better execution plan. However, there may be other ways to achieve the same goal.
如果结果集完全相同，则第二个连接可以通过启用更好的执行计划来提高性能。但是，可能还有其他方法可以实现相同的目标。
#2
0
If the additional table being joined to contained an index that could be used in the query, while the other tables did not... then you might get a performance boost by virtue of being able to use the index instead of a full scan.
如果要连接的附加表包含可以在查询中使用的索引，而其他表没有...那么由于能够使用索引而不是完整扫描，您可能会获得性能提升。
My guess is that you'd be better off just adding the index to one of the existing tables and avoiding the extra join, but I guess it would depend on the specific situation?
我的猜测是你最好只将索引添加到其中一个现有表中并避免额外的连接，但我想这取决于具体情况？
That's the only benefit I can think of (assuming you don't actually need to select any of the fields)!
这是我能想到的唯一好处（假设您实际上不需要选择任何字段）！
#1
3
A second join can improve performance. Join's can be used for filtering, which reduces the number of rows.
第二次加入可以提高性能。 Join可以用于过滤，这可以减少行数。
This would be particularly true if the join keys are indexed, so the filtering can take place very quickly.
如果对连接键进行索引，则尤其如此，因此可以非常快速地进行过滤。
The reduced result set can also speed aggregations and sorting.
缩减的结果集还可以加速聚合和排序。
If the result sets are exactly the same, the second join might improve performance by enabling a better execution plan. However, there may be other ways to achieve the same goal.
如果结果集完全相同，则第二个连接可以通过启用更好的执行计划来提高性能。但是，可能还有其他方法可以实现相同的目标。
#2
0
If the additional table being joined to contained an index that could be used in the query, while the other tables did not... then you might get a performance boost by virtue of being able to use the index instead of a full scan.
如果要连接的附加表包含可以在查询中使用的索引，而其他表没有...那么由于能够使用索引而不是完整扫描，您可能会获得性能提升。
My guess is that you'd be better off just adding the index to one of the existing tables and avoiding the extra join, but I guess it would depend on the specific situation?
我的猜测是你最好只将索引添加到其中一个现有表中并避免额外的连接，但我想这取决于具体情况？
That's the only benefit I can think of (assuming you don't actually need to select any of the fields)!
这是我能想到的唯一好处（假设您实际上不需要选择任何字段）！