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
)
总而言之,为什么对我的附加费进行单独查询比在第一时间加入它们更快？
解决方法
您将看到第一个变体的实际数据大小= 100,276行中的11,272 MB.
在第二个变体中,填充临时表的查询在19,665行中仅返回173KB.最后一个查询在87,510行中返回1,685 MB.
11,272 MB远超过1,685 MB
难怪第一个查询速度较慢.
这种差异是由两个因素造成的：
>在第一个变体中,您可以从UserSearches,Quotes,ContainerDetails表中选择所有列.而在第二个变体中,您只从ContainerDetails中选择ID.除了从磁盘读取和通过网络额外字节传输之外,这种差异导致了截然不同的计划.第二个变体不执行排序,不进行密钥查找并使用哈希联接而不是嵌套循环.它在报价上使用不同的索引.第二个变体使用ContainerDetails上的索引扫描而不是Seek.
>查询产生不同的行数,因为第一个变量使用LEFT JOIN和第二个INNER JOIN.
所以,要使它们具有可比性：
>而不是仅使用* list明确显示您需要的那些列.
>使用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
;
