代码之家 › 专栏 › 技术社区 › Aaronaught

SQL Server DRI(在删除级联上)速度慢吗?

cascade sql-server-2008 performance sql-server

Aaronaught · 技术社区 · 15 年前

我一直在分析一个系统中与一个特别慢的删除操作相关的重复出现的“错误报告”(perf问题)。长话短说:看来 CASCADE DELETE 凯斯负有很大责任,我想知道(a)这是否合理,以及(b)为什么会这样。

我们有一个小部件的模式,比如说,那些小部件位于相关表和相关表的大型图形的根,等等。非常明确地说,不鼓励从这个表中删除;这是“核选择”,用户不会抱有相反的幻想。尽管如此,有时还是必须这样做。

架构如下所示:

Widgets
   |
   +--- Anvils [1:1]
   |    |
   |    +--- AnvilTestData [1:N]
   |
   +--- WidgetHistory (1:N)
        |
        +--- WidgetHistoryDetails (1:N)

列定义如下所示:

Widgets (WidgetID int PK, WidgetName varchar(50))
Anvils (AnvilID int PK, WidgetID int FK/IX/UNIQUE, ...)
AnvilTestData (AnvilID int FK/IX, TestID int, ...Test Data...)
WidgetHistory (HistoryID int PK, WidgetID int FK/IX, HistoryDate datetime, ...)
WidgetHistoryDetails (HistoryID int FK/IX, DetailType smallint, ...)

没什么太可怕的,真的。一 Widget 可以是不同的类型, Anvil 是一种特殊类型,因此关系是1:1(或者更准确地说是1:0..1)。然后是大量的数据-可能有成千上万行 AnvilTestData 每 砧座 随着时间的推移收集,处理硬度,腐蚀,准确的重量,锤子兼容性,可用性问题,并与卡通头部冲击试验。

然后每一个 小装置 对各种类型的交易(生产、库存移动、销售、缺陷调查、RMA、维修、客户投诉等)有着漫长而乏味的历史。根据其年龄,单个小部件可能有10-2万个细节,或者根本没有。

所以,毫不奇怪的是, 级联删除 这里的每一层关系。如果A 小装置 需要删除,这意味着发生了严重的错误,我们需要删除该小部件曾经存在的任何记录,包括其历史记录、测试数据等。再次,核选项。

关系都是索引的,统计数据是最新的。正常的查询速度很快。除了删除之外,这个系统对所有的东西都很平滑。

最后,由于各种原因,我们一次只允许删除一个小部件,因此delete语句如下所示:

DELETE FROM Widgets
WHERE WidgetID = @WidgetID

很简单,看起来无害的删除… 需要2分钟才能跑完 ,对于具有 没有数据!

经过艰难的执行计划,我终于能够找出 铁砧试验数据 和 WidgetHistoryDetails 删除为成本最高的子操作。所以我试着关掉 CASCADE (但是保持实际的fk,只是将其设置为 NO ACTION )把剧本改写成如下内容:

DECLARE @AnvilID int
SELECT @AnvilID = AnvilID FROM Anvils WHERE WidgetID = @WidgetID

DELETE FROM AnvilTestData
WHERE AnvilID = @AnvilID

DELETE FROM WidgetHistory
WHERE HistoryID IN (
    SELECT HistoryID
    FROM WidgetHistory
    WHERE WidgetID = @WidgetID)

DELETE FROM Widgets WHERE WidgetID = @WidgetID

这两个“优化”都导致了显著的加速,每一个都缩短了将近一分钟的执行时间,因此原来的2分钟删除现在需要大约5-10秒-至少新的小部件,没有很多历史或测试数据。

只是说清楚,还有一个 叶栅 从 WidgetHistory 到 WidgetHistory详细信息 ,在扇形分叉最高的地方,我只删除了源于 Widgets .

层叠关系的进一步“扁平化”导致逐渐减少的戏剧性但仍然明显的加速,以至于删除新的当所有对较大表的级联删除都被删除并替换为显式删除时,小部件几乎是即时的。

我在用 DBCC DROPCLEANBUFFERS 和 DBCC FREEPROCCACHE 每次测试前。我已经禁用了所有可能导致进一步减速的触发器(尽管这些触发器无论如何都会出现在执行计划中)。我也在测试旧的小部件,同时注意到速度明显加快;删除过去需要5分钟,现在需要20-40秒。

现在我是一个“选择不破碎”哲学的热心支持者,但是对于这种行为似乎没有任何合理的解释,除了 级联删除 关系。

所以,我的问题是:

这是SQL Server中DRI的已知问题吗? (我在谷歌上或这里似乎找不到任何关于这类事情的参考资料;我怀疑答案是否定的。)
如果不是,我看到的行为还有其他解释吗?
如果这是一个已知的问题,为什么这是一个问题,是否有更好的解决办法,我可以使用?

1 回复 | 直到 15 年前

Quassnoi 15 年前

SQL Server 最擅长基于集合的操作,而 CASCADE 从本质上讲,删除是基于记录的。

SQL Server 与其他服务器不同,它尝试优化基于即时集的操作,但是,它只工作一个级别的深度。需要删除上层表中的记录才能删除下层表中的记录。

换句话说,级联操作是上下工作的,而您的解决方案是上下工作的,这更基于集合,更高效。

下面是一个示例架构:

CREATE TABLE t_g (id INT NOT NULL PRIMARY KEY)

CREATE TABLE t_p (id INT NOT NULL PRIMARY KEY, g INT NOT NULL, CONSTRAINT fk_p_g FOREIGN KEY (g) REFERENCES t_g ON DELETE CASCADE)

CREATE TABLE t_c (id INT NOT NULL PRIMARY KEY, p INT NOT NULL, CONSTRAINT fk_c_p FOREIGN KEY (p) REFERENCES t_p ON DELETE CASCADE)

CREATE INDEX ix_p_g ON t_p (g)

CREATE INDEX ix_c_p ON t_c (p)

,此查询:

DELETE
FROM    t_g
WHERE   id > 50000

它的计划是:

  |--Sequence
       |--Table Spool
       |    |--Clustered Index Delete(OBJECT:([test].[dbo].[t_g].[PK__t_g__176E4C6B]), WHERE:([test].[dbo].[t_g].[id] > (50000)))
       |--Index Delete(OBJECT:([test].[dbo].[t_p].[ix_p_g]) WITH ORDERED PREFETCH)
       |    |--Sort(ORDER BY:([test].[dbo].[t_p].[g] ASC, [test].[dbo].[t_p].[id] ASC))
       |         |--Table Spool
       |              |--Clustered Index Delete(OBJECT:([test].[dbo].[t_p].[PK__t_p__195694DD]) WITH ORDERED PREFETCH)
       |                   |--Sort(ORDER BY:([test].[dbo].[t_p].[id] ASC))
       |                        |--Merge Join(Inner Join, MERGE:([test].[dbo].[t_g].[id])=([test].[dbo].[t_p].[g]), RESIDUAL:([test].[dbo].[t_p].[g]=[test].[dbo].[t_g].[id]))
       |                             |--Table Spool
       |                             |--Index Scan(OBJECT:([test].[dbo].[t_p].[ix_p_g]), ORDERED FORWARD)
       |--Index Delete(OBJECT:([test].[dbo].[t_c].[ix_c_p]) WITH ORDERED PREFETCH)
            |--Sort(ORDER BY:([test].[dbo].[t_c].[p] ASC, [test].[dbo].[t_c].[id] ASC))
                 |--Clustered Index Delete(OBJECT:([test].[dbo].[t_c].[PK__t_c__1C330188]) WITH ORDERED PREFETCH)
                      |--Table Spool
                           |--Sort(ORDER BY:([test].[dbo].[t_c].[id] ASC))
                                |--Hash Match(Inner Join, HASH:([test].[dbo].[t_p].[id])=([test].[dbo].[t_c].[p]))
                                     |--Table Spool
                                     |--Index Scan(OBJECT:([test].[dbo].[t_c].[ix_c_p]), ORDERED FORWARD)

第一, SQL Server 从中删除记录 t_g ,然后将删除的记录与 t_p 并从后者中删除,最后,连接从中删除的记录 TYP 具有 t_c 并从中删除 TYC .

在这种情况下,一个三表连接会更有效,这就是您的解决方法。

如果能让你感觉好些, Oracle 不会以任何方式优化级联操作:它们总是 NESTED LOOPS 如果你忘记在引用列上创建索引,上帝会帮助你的。