我在内联表值函数中有一个递归 CTE。ITVF 返回一个 ID 列表,其中包含一个人的一长串祖先序列,它通常会循环大约 12 到 18 次才能到达末尾。它的速度相当快,但在许多人使用时,估计会出现错误,因此它变得非常慢。
CTE 看起来像这样
WITH ancestors AS (
SELECT
IndID,
AncestorID
FROM
dbo.persons
UNION ALL
SELECT
IndID,
AncestorID
FROM
ancestors a
INNER JOIN dbo.persons p ON p.IndID = a.AncestorID
)
SELECT IndID, AncestorID FROM ancestors
我有一千万行,所以这是一个相当大的表。当我请求一个 IndID 时,执行计划说它估计有 7 行,但实际得到了 1300 行。对于单个请求来说,它是可以接受的(运行时间不到一秒),但如果我将它加入到另一个请求中,那么它就会被调用,比方说 100 次,速度就会下降,因为估计越来越差。
需要明确的是,即使在 IVTF 之外也存在估计误差。我只是为了清楚地表明我不能只使用临时表。它需要保留在 IVTF 中,以便我可以将其加入更大、更复杂的请求中,并且它保持可并行性。我可以做什么来更好地估计行数?
更新:粘贴计划
更新 2:不太简化
我有点陷入两个问题之间。要么我使用 MSTVF 并且所有查询都无法并行化,要么我使用 ITVF 并希望 SQL 之神慷慨,不要严重低估行数,因此现在所有内容都在硬盘驱动器而不是 RAM 上交换。我希望这只是我的愚蠢,而且这是一个愚蠢的简单解决办法。
更新 3 据我所知回答所提出的问题。
uno)更新到最新的累积更新。没有按预期改变任何内容,但正如您所说,保持最新状态是件好事:)
dos)我们使用标准版,但我确实有一个列存储,但我不记得为什么这样做。它位于 IndID、FirstNameID、LastNameID 上。我会尝试删除它,今天我们只有 2 个数据库用户,如果它崩溃了其他问题,我们可以管理停机时间。
去掉ColumnStore后,确实节省了大约30秒!虽然还是慢,但已经好多了。我必须检查我的笔记以找出为什么要使用该列存储。
dos:第2部分)你所感受到的“动力不足的盒子”的感觉正是让我到现在为止的原因。我以为我们的机器动力不足,但与这里的 IT 人员交谈后,他们说我们没有使用超过 25% 的可用资源,因此瓶颈肯定是在 SQL 级别。因此,我上个月要求将 SQL 2017 更新到 2022,然后,现在我发现我的大多数繁重查询总是以序列化方式运行,所以开始优化,直到我遇到了这一点。我尝试过OPTION(USE HINT('DISALLOW_BATCH_MODE'), MAXDOP 8);
,但没有看到速度有任何变化。
tres) 该请求确实应该返回大约 1400 万行,所以这方面不用担心。但是,资源预留中只估计了 8 行,这难道不是它比应有的速度慢得多的原因吗?
更多上下文)我在本月所有工作之前使用的是 MSTVF,当我切换到 IVTF 时,速度更快,但如果您明白我的意思,则花费的时间与请求的行数的曲线是指数而不是线性的。我愿意重新思考这一切是如何完成的。
我在一个研究小组工作,我的部分工作是为研究人员提取数据集。我几乎是数据库上唯一的重度用户,我的同事更多地从事工作的“插入和清理数据”部分。所以我几乎可以用索引、函数等做我想做的事,只要表结构本身不改变太多。
更新 4 - 什么? 我不明白,我试图制作一个漂亮的图形来显示“花费的时间与询问的行数”指数曲线,所以我更改了查询以获得漂亮的平方数。
select
count(*)
FROM
(SELECT TOP 10000 * FROM individus.Individus WHERE AnneeNaissance > 1901 AND AnneeDeces < 1911) i CROSS APPLY
individus.GetAscendanceSimple(i.IndID) a
它在 10 秒内运行...即使尝试了 TOP 10,000,000 并且仍然很快,所以我只需要输入一个任意大的数字,这样我的所有案例都被覆盖,并且它运行得像我希望的那样快(TOP 很重要)。在将其作为解决方案之前,我一定是错的不是吗?如果我们需要做的就是修复规划,那么这真是一个愚蠢的修复。