sql - 为什么外部联接比单独查询慢

Question

我有一个基本上如下所示的查询:

Select *
From UserSearches us
left outer join Quotes q on q.UserSearchId = us.Id and q.QuoteNumber is not null
left outer join ContainerDetails cd on cd.QuoteId = q.Id
left outer join Surcharges s on s.ContainerDetailId = cd.Id
where us.SearchDate between @beginDate and @endDate

给定 @beginDate 和 @endDate 的某些值，我的搜索需要 30 秒才能返回大约 100K 行。

最终目标是填充一些具有父子子子关系的对象。因此，经过一些实验，我发现可以通过以下方式显着加快查询速度:

Select *
From UserSearches us
left outer join Quotes q on q.UserSearchId = us.Id and q.QuoteNumber is not null
left outer join ContainerDetails cd on cd.QuoteId = q.Id
where us.SearchDate between @beginDate and @endDate

Select cd.Id into #cdIds
From UserSearches us
left outer join Quotes q on q.UserSearchId = us.Id and q.QuoteNumber is not null
left outer join ContainerDetails cd on cd.QuoteId = q.Id
where us.SearchDate between @beginDate and @endDate

Select * From Surcharges s
inner join #cdIds on s.ContainerDetailId = #cdIds.Id

DROP TABLE #cdIds

这会在 10 秒内运行，这对我来说毫无意义。当然，一开始就加入附加费应该会更快。

附加费表具有以下索引:

PK:

ALTER TABLE [dbo].[Surcharges] ADD  CONSTRAINT [PK_dbo.Surcharges] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)

IX1:

CREATE NONCLUSTERED INDEX [IX_Surcharge_ContainerDetailId] ON [dbo].[Surcharges]
(
    [ContainerDetailId] ASC
)
INCLUDE (   [Id],
[Every],
[Single],
[Column],
[About],
[Twelve],
[Of],
[Them],
)

IX2:

CREATE NONCLUSTERED INDEX [IX_ContainerDetailId] ON [dbo].[Surcharges]
(
    [ContainerDetailId] ASC
)

总而言之，为什么对我的附加费进行单独查询比首先加入它们要快？

编辑:以下是执行计划。这些是您可以在 Sql Studio 中打开的 .sqlplan 文件:

Query 1 - Combined

Query 2 - Seperate

Answer 1

要了解正在发生的情况，请查看实际的执行计划。

最好在 SQL Sentry Plan Explorer .

您将看到第一个变体的实际数据大小 = 11,272 MB，共 100,276 行。

在第二个变体中，填充临时表的查询仅返回 19,665 行中的 173KB。最后一个查询返回 87,510 行中的 1,685 MB。

11,272 MB 远大于 1,685 MB

难怪第一个查询速度较慢。

这种差异是由两个因素造成的:

1. 在第一个变体中，您选择 UserSearches、Quotes、ContainerDetails 表中的所有列。在第二种变体中，您仅从 ContainerDetails 中选择 ID。除了从磁盘读取和通过网络传输额外字节之外，这种差异会导致计划的显着不同。第二个变体不执行排序，不执行键查找并使用散列连接而不是嵌套循环。它在Quotes上使用不同的索引。第二种变体在 ContainerDetails 上使用索引 Scan 而不是 Seek。

2. 查询生成不同数量的行，因为第一个变体使用 LEFT JOIN 和第二个 INNER JOIN。

因此，为了使它们具有可比性:

1. 不要使用 * 只显式列出您需要的列。
2. 在两种变体中都使用INNER JOIN(或LEFT JOIN)附加费。

<小时/>

更新

您的问题是“为什么 SQL Server 会更快地运行第二个查询”，答案是:因为查询不同并且它们产生不同的结果(不同的行集、不同的列集)。

现在你问另一个问题:如何使它们相同且快速。

您的两个变体中哪一个会产生您想要的正确结果？我假设它是带有临时表的第二个变体。

请注意，我在这里不是回答如何使它们更快。我在这里回答如何使它们相同。

以下单个查询应该产生与带有临时表的第二个变体完全相同的结果，但没有显式临时表。我希望它的性能与带有临时表的第二种变体类似。我故意使用 CTE 来编写它，以复制带有临时表的变体的结构，尽管没有临时表很容易重写它。优化器无论如何都会足够聪明地做到这一点。

WITH
CTE
AS
(
    Select cd.Id
    From
        UserSearches us
        left outer join Quotes q on q.UserSearchId = us.Id and q.QuoteNumber is not null
        left outer join ContainerDetails cd on cd.QuoteId = q.Id
    where
        us.SearchDate between @beginDate and @endDate
)
Select *
From
    Surcharges s
    inner join CTE on s.ContainerDetailId = CTE.Id
;