快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

SQL Server中的高性能关系部门

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

SQL中使用关系除法来选择符合许多不同条件的行。对于许多任务,这是一种被忽略但有效的技术。尽管SQL可能看起来令人生畏且复杂,但是如果在应用最终逻辑之前尽可能减少行数,SQL可以很好地执行。Dwain Camps解释了如何做,并显示了性能提升。

尽管关系除法是在关系代数中定义的,但是对于任何人而言,这都是一个挑战性的查询,无论他们对SQL有什么经验。尽管这是解决许多数据库任务的最有效方法,但仅识别那些最好由关系部门解决的特定业务需求是非常困难的。我们将尝试提供帮助,并提供一些可用于提供高性能结果的代码模式。

关系划分是用于例如标识以下内容的方法:

  • 具有工作技能的求职者(本文中使用的主要示例)。
  • 仅包含特定项目子集的订单。
  • 在两个或多个特定位置具有操作区域的客户。
  • 在许多物料清单(BOM)中共享的零件。
  • 在其计算机上安装了具有相同许可软件的唯一不同集合的用户,例如MS Office,Windows XP和MS Visual Studio。

在本文中,我建议可以通过在应用最终逻辑之前尽可能多地减少行数来提高几乎所有关系划分解决方案的性能。

在进行本文的研究时,我偶然发现了著名的SQL作者Joe Celko撰写的Dived We Stand:关系部门的SQL,该文章发表在Simple Talk上。我们将大量利用他的关系划分示例,因为他在解释它们方面做得很好。我们将更改数据结构,以便为您提供替代的图片,希望更多示例可以帮助您使所有事情变得更加清晰。

问题场景和数据设置

首先,我们需要创建一些示例数据,这些数据可以用于本文将要探讨的大多数问题。如果您想继续学习而不必复制/粘贴本文中的查询,则可以下载随附的资源文件:01 CandidatesSkills Sample Queries.sql,其中包含与第一个示例相关的所有示例查询。

假设您是一家招聘公司。在您的数据库中,除了可能的候选人列表和许多客户感兴趣的所有技能(例如SQL,SSIS,SSRS等)列表之外,您可能还会调用一个表CandidatesSkills。这是候选人与其所拥有技能的映射表。

按照惯例,我们将候选人和技能的ID都使用;但我们将省略与候选人和技能相关的表格,因为它们与我们的重点无关。在关系除法中,该CandidatesSkills表称为股息表。该表需要良好的索引编制,以使本文中的任何建议查询都能正常执行。

现在,我们进一步假设一个客户伴随着一个职位空缺,而对于这个职位空缺,他拥有一份他所寻求的技能清单。他是我们要求更高的客户之一,因此他坚持认为,我们介绍的任何候选人都必须具备所有列出的技能。让我们将其放入一个助手表,根据关系划分,它是除数表。

现在,让我们将样本数据(具有其技能的候选人)与我们的需求进行比较,并确定关系划分的案例。

1960-33cb4caa-ecea-415d-891c-39855e9524e

我们有五名候选人似乎具有相关技能。

  • 候选人1具备所有三项必修技能,以及一项附加技能
  • 候选人2仅具备三个必修技能
  • 候选人3和5都不具备要求的技能
  • 候选人4仅具备三个必需技能中的两个

由于应聘者2仅拥有必需的技能,而不再拥有其他技能,因此将该应聘者与职位发布相匹配就是“ 无剩余关系部门(RDNR)”。候选人1拥有所需的技能再加上一项,因此,这被称为“ 剩余关系划分”(RDWR),其余就是不需要的另一项技能。有人可能会争辩说,由于候选人4满足了三个要求中的两个,所以这也可能是关系划分的一种情况。但是我们排除了这种情况,因为我们的客户要求拥有所有必需的技能才能使候选人合格。

余数关系划分(RDWR)

Joe Celko在引用的文章中针对此问题提供了两种解决方案。他表示其中一个比另一个表现更好,我同意。该解决方案如下:

有趣的是,在本文的讨论线程中,SQL MVP Peter Larsson可能以在PESO或SWEPESO的绰号而闻名,他提出了一个更快的解决RDWR问题的方法,他在自己的《思维导论》中提出了这一点。在Box外部的博客条目,名为“ 适当的关系划分集”。实际上,他为该问题建议的解决方案具有足够的通用性,可以解决RDWR或RDNR问题(请参阅最后一行代码中的注释以了解操作方法)。

这两种解决方案均基于我们的样本数据得出以下结果,只需查看我们之前提供的图形,您就会发现它们是正确的:

当然,一旦您确信解决方案是正确的,就必须对大量数据进行测试,这一点很重要。为此,我们在附加的资源文件(02 Create Large CandidatesSkills Tables.sql)中提供了一个脚本,有兴趣的读者可以使用该脚本创建两个大表CandidatesSkills来支持测试这两个解决方案的性能。从这些结果中获得的结果可能在一定程度上取决于数据中数据点的分散性。

这是硬道理吗?在本文的后面,我将展示一种可以与这两种解决方案结合使用的技术,从而可以提高这两种解决方案的性能。

关系部门无余(RDNR)

对于RDNR,Joe在文章中也提供了解决方案。在PESO的博客中,他实际上是根据与Joe的往来将解决方案集扩展到了四个。根据我的测试,最快的就是这个。您可以尝试使用1M Rows.sql的03 RDNR解决方案测试自己重现这些结果。

当我查看此内容时,我想到了一个小改进,测试证实了该变体的速度稍快。

同样在他的博客中,PESO提供了一个解决方案,该解决方案表示他与SQL MVP Adam Machanic之间的合作。

所有这三个解决方案都将从我们的样本数据中产生预期结果,即:

再一次,我们今天不在这里比较这些解决方案的性能。相反,我们现在将为您提供一种方法,通过该方法,到目前为止提出的任何解决方案(我们将其称为基本查询)都可以针对大型行集进行改进。

有剩余和没有剩余时改进关系划分

考虑下面的查询,我们专门构建了该查询以识别满足(但不一定是全部)除数条件的候选人。

这将产生以下结果集:

该结果集具有一些非常有趣的属性:

  • 它恰好只包含可以满足RDWR和RDNR情况的CandidateID。但是,如果我们正在寻找更多的工作要求(> 3),那么它还将包含其他行,需要对其进行进一步检查,以将设置减少到目标除数。
  • 它包含与已识别候选者关联的所有行。
  • 我们创建的索引非常快;在查询计划中产生恰好四个索引查找。其中三个查找使用表上的非聚集INDEX。

但是,它确实有一个缺陷:如果我们碰巧少于3个工作要求,它将无法正常工作。让我们对其进行修复,并将此有趣的新代码放入通用表表达式(CTE)中。

即使工作需求少于三个,但至少有一个,这也将起作用,并且仍然执行相同的四个索引查找。它起作用的原因是,当前三个元素之一为NULL(意味着它不存在)时,它将“短路” EXISTS检查。更有趣的是,由于它所做的只是产生我们需要检查的行的子集,因此我们可以将其与先前针对RDNR和RDWR提出的五个解决方案中的任何一个结合起来,只需将对CandidatesSkills表的引用替换为对而是CTE!例如,如果将其与RDNR的PESO + Adam Machanic合作相结合,我们将得到:

可以确认这会为我们的样本数据生成正确的结果集。我们称此为“关系划分的行减少法”。

为了真正了解这对基本查询性能的影响,我们确实需要增加测试工具中的测试数据量。通过创建一个包含约1000万行的表(CandidateSkills2),您已经可以完成此操作。我们可以使用“行减少法”应用到目前为止讨论的五个查询中的每一个,并以每个基本查询为基础对此进行图形化处理,这意味着我们将以“行减少法”的百分比显示基准所占的时间查询。您可以通过在10M Rows.sql上运行04带有和不带有行减少功能来重现这些结果

1960-d9aff564-89d4-48c8-9933-d2662482cf6

从这些结果可以明显看出,行缩减方法在每种情况下都是有益的,可以将所需的经过时间减少到基本查询所需时间的约20-55%。

1960-7962a960-eabd-406f-80e6-c9903bc8aa3

CPU使用率的变化更大。对于RDWR问题,通常较少,而对于RDNR问题,通常较多(有时几乎两倍)。对于经过时间的改善,这可能是一个有效的折衷方案。

再次注意,我们避免将这五个解决方案的原始速度相互比较,因为这可能取决于股息表中数据的分散程度和/或除数表中要匹配的元素数量。在我们的例子中,每行减少应用了三次。我们对更多的行减少进行了实验(实际上最多可以减少七次),并发现总会有收益递减的点,此后,更多的行减少会不利地影响经过时间。拥有1000万行数据的最佳点似乎是减少了三到四行。如果您的股息表中有数亿或数十亿行,则可能会更高。

这里的底线是,如果您进行关系划分并有最喜欢的解决方案,那么使用行缩减技术(适用于大型股息表)很可能为您提供性能更快的解决方案。

具有多个除数的关系除法

到目前为止,在我们遇到的问题中,我们只处理了一个目标数据子集(除数表),我们希望在其中识别该数据在主表(除数表)中的位置。您可能想知道为什么我们在除数表中包含JobID列?所以现在您可以找出答案。

假设我们实际上有两个不同的工作要求。我们可以将第二个工作要求添加到除数表中,如下所示:

现在我们的除数表中包含以下数据。

和以前一样,根据我们是否想要精确的除法(RDNR),对于JobID = 1,我们将获得CandidateID 2;对于剩余的除法(RDWR),对于JobID = 1,我们将获得CandidateIDs 1和2。还希望返回JobID = 2的匹配项,RDNR为5,RDWR为4/5。在PESO的博客中,他神秘地暗示,他建议的任何一个查询都可以进行修改,以处理多个除数的情况,因此,让我们尝试看看是否可以这样做。我们将选择一种适用于任何一种情况的方式。

很容易证明(对于所示情况)此查询返回:

如果MIN(cnt)> = 1,则返回以下内容。

我们已经讨论过的其他查询可以类似地修改为处理多个除数的情况。

但是,摆在我们面前的问题是我们的行减少方法是否也适用。答案是肯定的,尽管有一个限制。为了应用行减少方法,我们只能对工作要求的交叉点进行操作。为了使这项工作有效,我们必须修改查询,该查询将计算用于行减少的三个变量,如下所示(将工作需求的交集分配给我们的三个变量)。

我们修改了行减少方法(如下),以处理@ XID1可能为NULL的情况。您可以自己验证结果是否与以前相同。

显然,当除数更多时,此限制变得更加严格;因为您拥有的越多,则除了空集之外的交集发生的可能性就越小。但是,我们已经证明可以做到。如果存在重叠,则当您有很大的股息表时,它仍然应该有助于提高查询的性能。

托德师

乔·塞尔科(Joe Celko)还提出了另一个关系划分问题,他称其为托德分部(因为他将其归因于斯蒂芬·托德)。再次,我们将重新组织问题的范围,以提供对可以解决的业务问题类型的更多见解。

我们将从一个ProjectTasks表开始,该表包含多个项目以及每个项目所需的任务。请注意,特定任务可能与各种项目重叠。

我们还有一个ResourceTasks表,该表将资源映射到任务。再一次,可以将任务分配给多个资源。

我们期望的结果集是项目列表以及根据资源任务分配给哪个项目而分配给他们的资源。

Joe Celko建议以下两个查询中的任何一个都将返回期望的结果,并进一步建议第二个查询在某些情况下可能会稍微快一点(由我确认)。

这两个查询都有两个结构,出于性能原因,我通常会尽量避免:CROSS JOIN(由使用两个表之间的逗号的非ANSI标准语法所伪装)和DISTINCT。笛卡尔积(来自CROSS JOIN)会生成许多不必要的行,而DISTINCT会导致发生昂贵的排序。因此,让我们看看是否可以更快地提出一些建议。

实际上,我已经提出了两种解决方案,因为根据测试工具中的行数,一种或另一种可能会稍快一些。如果完全按照提供的方式运行Todds Division.sql的05 Test Harness,则应该看到类似于以下所示的计时结果。

时间(毫秒)
询问 过去 中央处理器
塞尔科1 14,109 37,209
Celko 2 14,251 37,144
德温C 1 398 1,014
德温2 392 1,186

如果您按照测试工具中的说明增加行数,并在不想等待它们完成的情况下注释一下Joe Celko的查询,则可能会发现在行数较高时,我的第二个解决方案会稍微快一些(经过的时间) )比我的第一个。这两种解决方案都代表一种行减少方法,因为它们使用INNER JOIN来减少否则将由CROSS JOIN产生的笛卡尔乘积生成的行。

由于除数表中的条目数众多,因此针对RDNR和RDWR提出的行缩减方法无法解决此特定问题。永远不会有足够的重叠来帮助减少执行时间。

罗姆利师

我必须在此感谢乔·塞尔科(Joel Celko)–因为他是轻描淡写的大师。罗姆利分部(以所罗门·史密斯·巴尼的理查德·罗姆利的名字命名)无疑是一个相当复杂的问题。我们将再次尝试描述问题,但是我们将创建一个替代方案来帮助阐明这种关系分歧何时起作用。

假设我们有一家拥有机器和装配线的工厂。每条装配线可以一次支持多个流程:每台机器与一个流程唯一关联,并且一次只能支持一个产品。我们希望确定装配线和产品是否全部,不支持或支持某些过程。

让我们先设置一些样本数据,然后我们看一下可能的解决方案。首先,组装线和相关流程:

对于机器及其相关的产品/过程,我们将从以下内容开始:

Joe Celko提出了以下查询来解决Romley的部门:

这将产生以下结果集:

如果查看查询的执行计划,您会发现此方法仅使用两次聚簇索引扫描。我最初的感觉是,这很难克服。

1960-81cb4c31-de49-4cfb-bab9-ea741c2d740

我不感到羞耻,只是解决这个坏男孩花了我一段时间,但是我的工作当然特别具有挑战性,因为我最终想提出一个更快的查询。经过一番摸索(我似乎做了很多)之后,我想出了以下解决方案,该解决方案看起来似乎更复杂,但至少可以实现预期的结果。

这种方法的查询执行计划的成本较高,并且看起来也相当复杂。

1960-c2456748-44c5-4027-bfe5-20911b79579我经常从个人经验中发现,查询计划的成本可能是骗人的。最终测试是如何在大型测试工具上运行。因此,我们构建了一种可以在两种情况下运行的软件。

属性 大约行
案件 组装线 机器 产品展示 工艺流程 #AssemblyLinesProcesses #机械
1个 100 1,000 100 100 950 1,000
2 1,000 10,000 100 100 9,500 10,000

您可以使用06 Romleys Division.sql的测试工具来自己运行测试。最初是为了运行案例1而设置的,但是您可以在开始运行案例2时取消注释一些代码。运行这些案例时得到的结果是:

时间(毫秒)– CELKO 时间(毫秒)– Dwain.C
案件 过去 中央处理器 过去 中央处理器
1个 1,143 4,415 413 421
2 106,395 407,942 42,063 42,027

出乎意料的是,这意味着我查询Romley’s Division的总时间约为原始查询的40%,而它仅使用10%的CPU。改进归因于两个主要因素:

  • CROSS JOIN应用于两个预聚合,因此显着减小了生成的笛卡尔积的大小。这为我提供了最终结果的前两列所需的确切行。
  • 在OUTER APPLY中使用INNER JOIN本身就是一种行减少,并且通过WHERE子句进一步减少了行,该子句将行限制为仅适用于CROSS JOIN的行。

虽然不是使用最初提出的行减少方法的情况(同样由于各种原因在这里也不适用),但它表明通过一点努力,一个非常棘手的查询就可以变成高性能的查询。

结论

当SQL MVP Itzik Ben-Gan提出了一个挑战,以识别序列中的子序列时,我首先对关系划分的行缩减方法的可能性感到震惊。在他的文章的第一部分(左侧的链接)中,我在文章后的讨论中提出了三种解决方案,最终提出了“行减少法”。在讨论部分第二部分在这篇文章中,PESO评论说这是一种关系划分的特例,他称之为有序关系部门。同样在该部分中,针对本文提出的最佳解决方案发布了该方法的性能结果。不久之后,我意识到我的建议可以普遍应用于关系划分的其他更一般的情况,这在早期就说明了我的观点,即有时关系划分很难与特定的业务问题相关联。我们将让您看看这两篇优秀的文章,以了解最快的解决方案是什么。

行缩减方法似乎可以提高任何关系除法查询(带或不带余数)的性能,尤其是当您面对的行集非常大时。即使您的关系部门有多个余数,即使有一些限制,也可以使用此方法。

建议的行缩减方法不能应用于Todd或Romley的特殊关系划分案例;但是在这些情况下实现高性能查询的关键还涉及在应用最终逻辑解决案例之前尽可能减少行的原理。

本文底部的zip文件中随本文提供的资源文件(SQL脚本)的摘要如下:

  • 01 CandidatesSkills示例Queries.sql –包含本文讨论的针对RDWR,RDNR的示例数据,以及针对这些情况的示例查询(使用Rows Reduction方法的示例查询)以及使用多个除数的关系除法(即,所有使用CandidatesSkills表)。
  • 02创建大型CandidatesSkills Tables.sql-这将设置两个表,CandidatesSkills1和CandidatesSkills2分别包含1M和10M行。
  • 03在1M Rows.sql中测试RDNR解决方案 –这将验证在100万行中针对RDNR的解决方案,因此我可以选择Joe Celko必须提供的最佳解决方案。
  • 04在1000万行上具有和不具有行减少功能Rows.sql-这将运行我们在10M行进行比较的五个解决方案(请参阅所提供的两个直方图)。
  • 05 Todds Division.sql的测试工具-它包含一个用于所有Todd’s Division查询的大型测试工具。如果要作为本节中的示例查询运行,可以手动取消注释/注释某些行。
  • 06 Romleys Division.sql的测试工具-它包含用于所有Romley’s Division查询的大型测试工具(案例1)。如果要作为本节中的示例查询运行,可以手动取消注释/注释某些行。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638