我有一个非常小的表,其中有 12 行,可以使用以下语句创建:
CREATE TABLE dbo.SmallTable(ScoreMonth tinyint NOT NULL PRIMARY KEY,
ScoreGoal float NOT NULL
);
我有另一个表,其中包含 ≈100M 行,可以使用以下语句创建:
CREATE TABLE dbo.SlowCrossApply(RecordKey nvarchar(12) NOT NULL,
Score1 decimal(3, 2) NOT NULL,
Score2 decimal(3, 2) NOT NULL,
Score3 decimal(3, 2) NOT NULL,
Score4 decimal(3, 2) NOT NULL,
Score5 decimal(3, 2) NOT NULL,
Score6 decimal(3, 2) NOT NULL,
FromToday bit NOT NULL
);
ALTER TABLE dbo.SlowCrossApply ADD CONSTRAINT i01PK PRIMARY KEY CLUSTERED(RecordKey ASC)
WITH(FILLFACTOR = 90, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
DATA_COMPRESSION = PAGE
);
CREATE NONCLUSTERED INDEX i02TodayRecords ON dbo.SlowCrossApply(FromToday)
INCLUDE (Score1, Score2, Score3, Score4, Score5, Score6)
WHERE FromToday = 1
WITH(FILLFACTOR = 100, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
DATA_COMPRESSION = PAGE
);
i02TodayRecords
其中有 ≈1M 行。当我运行以下查询时——我努力将其格式化以使其看起来干净并防止水平滚动条——它需要 5 分钟以上才能完成:
SELECT b.RecordKey,
COALESCE(NULLIF(ROUND(((0.95 * (ROW_NUMBER() OVER(PARTITION BY a.Prefix
ORDER BY b.Score6 ASC
) - 1
)
)
/ COALESCE(NULLIF(COUNT(*) OVER(PARTITION BY a.Prefix) - 1, 0
), 1
)
) + 0.005, 2
), 0.96
), 0.95
) AS NewScore
FROM (SELECT LEFT(s.RecordKey, 2) AS Prefix,
CAST(ROUND(sm.ScoreGoal * COUNT(*), 0) AS int) AS Quant
FROM dbo.SlowCrossApply AS s
CROSS JOIN dbo.SmallTable AS sm
WHERE s.FromToday = 1 AND sm.ScoreMonth = MONTH(GETDATE())
GROUP BY LEFT(s.RecordKey, 2), sm.ScoreGoal
) AS a
CROSS APPLY (SELECT TOP(a.Quant) s2.RecordKey, s2.Score6
FROM dbo.SlowCrossApply AS s2
WHERE s2.FromToday = 1 AND s2.Score6 > 0 AND LEFT(s2.RecordKey, 2) = a.Prefix
ORDER BY s2.Score6 DESC
) AS b;
外部子查询只返回 10 行;如果我提供使用i02TodayRecords
或将外部子查询的结果放入表变量的提示,则只需不到 1 秒。最终结果返回 8000 多行。
执行计划显示 64% 的成本是由于该Cross Apply
部分的聚集索引上的急切索引假脱机造成的。
我知道索引提示有效(至少目前如此),但我希望避免使用它。理想情况下,我也不会走表变量路线。我可以做些什么来让查询优化器“知道”使用i02TodayRecords
吗?我意识到还有很多可能很重要的信息,如果需要,我会尽力提供上述信息。
一些可能有用的信息:索引的碎片少于 1%。两个索引的统计数据已通过 更新FULLSCAN
,数据库设置为具有简单的参数化和参数嗅探——不幸的是,我无法更改这些设置。关于后者,查询优化器没有用参数替换任何值,这与我运行的其他简单查询不同,在这些查询中我被迫使用提示来利用特定的过滤索引。
您可能面临的问题是SARGability,即在您的条款中使用该
LEFT
功能:WHERE
LEFT(s2.RecordKey, 2) = a.Prefix
有了它,您就无法为每一行运行该函数,然后进行比较。您不能按原样为此编制索引。将转换放入 CTE、视图或派生表中无济于事,编写函数来执行操作也无济于事。
解决此问题的一种方法是创建计算列并为其编制索引:
哪个可以被索引。我也稍微改变了你的索引定义:
另一种选择是将您的结果转储
CROSS JOIN
到临时表中:你跟进了:
在这种情况下,这并不重要。非唯一非聚集索引在非聚集索引的所有级别中存储聚集索引键列。请在此处查看我的帖子:聚簇索引键敢于尝试的地方。
和:
我通常不担心索引碎片,所以不。我的目标是创建索引来帮助查询。碎片化索引比不存在的索引有用得多,而且在许多情况下您永远不会注意到碎片化。
希望这可以帮助!