我正在尝试改进这个查询。当我以用户身份运行它时,它会在大约 1 分钟内运行,但在 ETL 加载期间可能需要长达 40 分钟的时间。不应该有任何锁定问题,因为这些表没有在其他任何地方引用。
根据查询计划,我应该朝哪个方向走?
我可以改进我的索引。它使用哈希匹配和合并连接可能会通过索引来改善这一点。
我是否需要一种不是一系列 LEFT JOIN 的方法?
列名和表名已被混淆:
我正在尝试改进这个查询。当我以用户身份运行它时,它会在大约 1 分钟内运行,但在 ETL 加载期间可能需要长达 40 分钟的时间。不应该有任何锁定问题,因为这些表没有在其他任何地方引用。
根据查询计划,我应该朝哪个方向走?
我可以改进我的索引。它使用哈希匹配和合并连接可能会通过索引来改善这一点。
我是否需要一种不是一系列 LEFT JOIN 的方法?
列名和表名已被混淆:
以下是执行计划中所花费时间的一些粗略统计:
因此,查询中有一个明显的瓶颈:与#Cohort_1 的连接。SQL Server 估计该表中的行大小为 12 KB,因此我猜测该表中有一些 LOB 列。批处理模式哈希联接的 LOB 数据存在一些限制。这可能就是为什么您会看到一些批处理模式哈希联接,然后计划切换到行模式,并使用昂贵的合并联接以及其他行模式哈希联接。
一般来说,我会坚持使用一种方法:要么获取具有行模式合并联接的查询计划,要么获取主要采用批处理模式哈希联接的计划。您的表都具有相同的行数并在同一列上联接,因此合并联接在这里可能效果很好,但查询可能需要在 MAXDOP 1 下运行才能获得最佳性能。我建议获取以下测试用例的实际查询计划:
OPTION (HASH JOIN, LOOP JOIN)
尝试通过添加提示并重写查询以在末尾添加 #Cohort_1 来获得更好的哈希连接计划OPTION (MERGE JOIN)
为查询添加提示您还担心 ETL 期间查询可能需要长达 40 分钟的时间,这比您在测试中观察到的时间要长得多。解决此问题的最基本方法是启用查询存储并查找查询存储日志与您在自己的测试中看到的日志之间的差异。查询计划是否不同?等待统计数据是否不同?一个查询是否比另一个查询使用更多的 CPU?所有这些信息都可以在 SQL Server 2019 上获得。您不必猜测是否存在阻塞。查询存储会告诉你这一点。
在评论中,您说了以下内容:
我认为这是一种冒犯性的言论。DBA 的工作不是为数据库提供服务。他们应该努力为最终用户服务。最终用户包括您(开发人员)。如果他们想要采取由于开销而无法启用查询存储的立场(这几乎肯定是错误的),那么他们需要以其他方式为您提供所需的信息。您可能需要将此问题上报给管理层或尝试找到某种折衷方案。例如,如果 ETL 工作负载仅在夜间运行,则可以仅在夜间启用查询存储。但我向您保证,如果 DBA 遇到了他们负责的问题,他们将进行必要的诊断,而不是为开销找借口。
查看查询运行时间较长时的实际计划,等待统计信息会告诉您原因。
或者打开查询存储并查看那里的等待统计信息。
从设计角度来看,拥有多个单独的队列暂存表而不是一个更宽的暂存表是很奇怪的。在临时表上使用列存储很奇怪。它们的构建成本很高,并且重建和输出列存储的所有行也很昂贵。使用堆或聚集索引来做到这一点更便宜。
对于这样的查询计划,使用列存储索引作为某些数据获取运算符的源,我会朝相反的方向走。
OPTION(HASH JOIN)
在查询末尾使用MAXDOP 8
到提示中合并联接不支持批处理模式,而您的计划中至少有一种批处理模式,而散列联接则支持批处理模式。
整个事情可能会更好,完全以批处理模式操作,而不是在行模式下进行一些操作,并在计划的各个点来回切换。
增加 DOP 的目的是分散每个线程的行分布,以便更多的线程可以同时处理更少的行,这也可能会以额外的 CPU 时间为代价来改善挂钟时间。
除了别人说的。
i) 注意查询计划中的警告标志并消除转换警告。为什么转换
Personid
为 varchar?同样注意其他转换。数据类型应该统一。ii)虽然查询看起来很简单,但表设计似乎有缺陷,导致优化器不堪重负。开启和平均。每个temp包含33L数据。而且其中很多都是大数据类型。
iii)尝试另一种方法,从第一个插入所有 personid 和其他列。然后使用内连接
Staging.CohortStagingTemp AS A
更新Project.BaseTable_STG
每个临时表,其中一个是一个。我相信这会更快