UNION ALL 可以比 JOIN 更快还是我的 JOIN 很烂? [英] Can UNION ALL be faster than JOINs or do my JOINs just suck?

问题描述

我有一个带有 uniqueidentifier 列的 Notes 表，我将它用作数据库中各种其他表的 FK(别担心，uniqueidentifier 其他表上的列不是聚集的 PK).这些其他表代表业务对象的层次结构.作为一个简单的表示，假设我还有另外两个表:

I have a Notes table with a uniqueidentifier column that I use as a FK for a variety of other tables in the database (don't worry, the uniqueidentifier columns on the other tables aren't clustered PKs). These other tables represent something of a hierarchy of business objects. As a simple representation, let's say I have two other tables:

• 潜在客户(PK LeadID)
• 报价(PK QuoteID、FK LeadID)

• Leads (PK LeadID)
• Quotes (PK QuoteID, FK LeadID)

在应用程序中 Lead 的显示中，我需要显示与该潜在客户相关的所有注释，包括标记为属于该潜在客户的任何 Quote 的注释.就我所见，我有两个选择——一个 UNION ALL 或几个 LEFT JOIN 语句.以下是它们的外观:

In the display of a Lead in the application, I need to show all notes related to the lead, including those tagged to any Quote that belongs to that lead. I have two options as far as I can see — either a UNION ALL or several LEFT JOIN statements. Here's how they'd look:

SELECT N.*  
FROM Notes N  
JOIN Leads L ON N.TargetUniqueID = L.UniqueID  
WHERE L.LeadID = @LeadID

UNION ALL

SELECT N.*  
FROM Notes N  
JOIN Quotes Q ON N.TargetUniqueID = Q.UniqueID  
WHERE Q.LeadID = @LeadID 

或者...

SELECT N.*  
FROM Notes N  
LEFT JOIN Leads L ON N.TargetUniqueID = L.UniqueID  
LEFT JOIN Quotes Q ON N.TargetUniqueID = Q.UniqueID  
WHERE L.LeadID = @LeadID OR Q.LeadID = @LeadID

在现实生活中，我总共有五个表可以附加注释，而且这个数字会随着应用程序的增长而增长.我已经在我正在使用的 uniqueidentifier 列上设置了非聚集索引，并且 SQL Profiler 说我不能再做任何改进，但是当我对实际大小的进行性能测试时测试数据集，我得到以下数字:

In real life I have a total of five tables that the notes could be attached to, and that number could grow as the application grows. I already have non-clustered indexes set up on the uniqueidentifier columns I'm using, and SQL Profiler says I can't make any more improvements, but when I do a performance test on a realistically-sized test data set, I get the following numbers:

• UNION ALL — 0.010 秒
• LEFT JOIN — 0.744 秒

• UNION ALL — 0.010 sec
• LEFT JOIN — 0.744 sec

我一直听说使用 UNION 很糟糕，而 UNION ALL 只是稍微好一点，但性能数据似乎并不能证明这一点.诚然，UNION ALL SQL 代码可能更难维护，但在这种性能差异下，它可能是值得的.

I had always heard that using UNION was bad, and that UNION ALL was only marginally better, but the performance numbers don't seem to bear that out. Granted, the UNION ALL SQL code might be more of a pain to maintain, but at that kind of performance difference it's probably worth it.

那么 UNION ALL 在这里真的更好还是我在 LEFT JOIN 代码中遗漏了一些会减慢速度的东西?

So is UNION ALL really better here or am I missing something on the LEFT JOIN code that is slowing things down?

推荐答案

UNION ALL 版本可能很容易满足 2 个索引查找.OR 可能导致扫描.执行计划是什么样的?

The UNION ALL version would probably be satisfied quite easily by 2 index seeks. OR can lead to scans. What do the execution plans look like?

您是否也尝试过这样避免访问 Notes 两次?

Also have you tried this to avoid accessing Notes twice?

;WITH J AS
(
SELECT UniqueID FROM Leads WHERE LeadID = @LeadID
UNION ALL
SELECT UniqueID FROM Quotes WHERE LeadID = @LeadID
)

SELECT N.*  /*Don't use * though!*/
FROM Notes N  
JOIN J ON N.TargetUniqueID = J.UniqueID  

这篇关于UNION ALL 可以比 JOIN 更快还是我的 JOIN 很烂?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！