我可以在不使用联合的情况下使用 OR 更快地进行此 JOIN 吗? [英] Can I make this JOIN using OR faster without using unions?

问题描述

在 SQL Server 2008 R2 中，是否有一种方法可以使用一些提示来强制此查询(在连接中使用 OR)运行速度与以下将 OR 替换为联合的查询一样快?

In SQL Server 2008 R2, is there a way to use some hints to force this query, which uses OR in a join, to run as fast as the below one, which replaces the OR with Unions?

慢查询:

select <stuff> from 
UPCs u
JOIN Ingredients i on 
(u.UPCID = i.UPCID) or ( u.CategoryID = i.CategoryID and u.Quality = i.Quality) or     (u.ManufacturerID = i.ManufacturerID and u.Quality = i.Quality)

更快的查询做同样的事情，但单独应用条件并将结果与​​联合连接:

select <stuff> from UPCs u join Ingredients i on 
(u.UPCID = i.UPCID)
UNION ALL
select <stuff> from UPCs u join Ingredients i on u.CategoryID = i.CategoryID and u.Quality =     i.Quality)
UNION ALL 
select <stuff> from UPCs u join Ingredients i on u.ManufacturerID = i.ManufacturerID and u.Quality = i.Quality

对于联合，它在 UPC 上进行循环连接和 2 个哈希连接，然后将联合的结果连接在一起.UPC ID是聚簇主键，其他字段没有索引，两个表都很大.

With the unions, it's doing a loop join on UPCs, and 2 hash joins, then concatenating the results of the union together. UPC ID is a clustered primary key, the other fields are non-indexed, and both tables are very large.

请注意，我正在对成分进行后续连接，因此我很乐意将其分解为多个成分连接，但前提是它比联合还要快.现实世界的查询是这样的:

Please note, I'm doing subsequent joins off of ingredients, so I'd happily break it up into multiple joins on ingredient, but only if it's even faster than the union. Real world query is something like this:

Select <stuff> from #UnknownPoursForThisEvent up
JOIN UPCs u on up.UPCID = u.UPCID
JOIN Tags t on t.TagNumber = up.TagNumber
JOIN Ingredients i on (i.UPCID = u.UPCID) or ( u.CategoryID = i.CategoryID and u.Quality =     i.Quality) or (u.ManufacturerID = i.ManufacturerID and u.Quality = i.Quality)
join Recipe r on i.RecipeID = r.RecipeID
join TicketItemAliases tia on tia.RecipeID = tia.RecipeID
<... Join, left join, and apply lots of other criteria>

执行计划.http://imgur.com/eMzqPvL&zqQ6snz#1

推荐答案

逻辑上

select <stuff> from 
UPCs u
  JOIN Ingredients i on 
       (u.UPCID = i.UPCID) or 
       (u.CategoryID = i.CategoryID and u.Quality = i.Quality) or
       (u.ManufacturerID = i.ManufacturerID and u.Quality = i.Quality)

应该和这个简化版的

select <stuff> from 
UPCs u
  JOIN Ingredients i on 
       u.UPCID = i.UPCID or 
       (u.Quality = i.Quality And
       (u.CategoryID = i.CategoryID Or u.ManufacturerID = i.ManufacturerID))

但是嘿，也许执行路径会有所不同.

but hey maybe the execution path will be different.

这篇关于我可以在不使用联合的情况下使用 OR 更快地进行此 JOIN 吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！