按顺序运行时SQL查询速度慢,但在单独运行时速度很快
我有一张表,我将用昂贵的计算值(使用来自不可变XML列的xquery)填充值。为了加速部署到生产,我已经在测试服务器上预先计算了值,并将其保存到带有BCP的文件中。按顺序运行时SQL查询速度慢,但在单独运行时速度很快
我的脚本如下
-- Lots of other work, including modifying OtherTable
CREATE TABLE FOO (...)
GO
BULK INSERT FOO
FROM 'C:\foo.dat';
GO
-- rerun from here after the break
INSERT INTO FOO
(ID, TotalQuantity)
SELECT
e.ID,
SUM(e.Quantity) as TotalQuantity
FROM (select
o.ID,
h.n.value('TotalQuantity[1]/.', 'int') as TotalQuantity
FROM dbo.OtherTable o
CROSS APPLY XmlColumn.nodes('(item/.../salesorder/)') h(n)
WHERE o.ID NOT IN (SELECT DISTINCT ID FROM FOO)
) as E
GROUP BY e.ID
当我在Management Studio中的前两行秒钟之内完成运行脚本,但最后的语句需要4个小时才能完成。由于我的foo.dat是计算管理工作室报告(0 row(s) affected)
,因此没有行被添加到OtherTable
。
如果我取消了两三分钟后,查询执行,并选择刚刚过去的查询,并单独运行它在5秒内完成这一点。
值得注意的事实:
- 的OtherTable包含20万行和XmlColumn的数据是相当大的,总表的大小〜3GB
- foo表得到130万行
有什么事情可能会有所作为?
管理工作室关闭了隐式交易。就我所能理解的,每条陈述都会在自己的交易中运行。
更新:
如果我先选择并运行脚本,直到-- rerun from here after the break
,然后选择并运行刚刚过去的查询,它仍然是缓慢的,直到我取消执行,然后再试一次。这至少排除了与脚本中的前一代码“一起”运行的任何效果,并归结为相同的查询在第一次执行时速度慢,在第二次时速度很快(在所有其他条件相同的情况下运行)。
不知道究竟为什么它帮助,但我重写了最后一个查询到left outer join
,而不是突然执行降至15毫秒。
INSERT INTO FOO
(ID, TotalQuantity)
SELECT
e.ID,
SUM(e.Quantity) as TotalQuantity
FROM (select
o.ID,
h.n.value('TotalQuantity[1]/.', 'int') as TotalQuantity
FROM dbo.OtherTable o
INNER JOIN FOO f ON o.ID = f.ID
CROSS APPLY o.XmlColumn.nodes('(item/.../salesorder/)') h(n)
WHERE f.ID = null
) as E
GROUP BY e.ID
我用http://www.fulltablescan.com/index.php?/archives/149-查看 - 执行 - 程序 - 运行 - 运行 - 查询 - SQL - 服务器.html得到缓慢运行查询的执行计划,但据我所见,它在第二次运行查询时使用相同的执行计划。不幸的是,我无法得到每个部分的执行计数(我对数字感兴趣除非我等待4个小时,我会尽量让查询在一夜之间运行,以获得完整的执行计划 – 2012-01-04 09:19:58
此外,为什么执行计划会在来自Management Studio的两个相同调用之间发生变化,而两者之间没有其他活动? – 2012-01-04 10:04:07
'执行计划为什么会在':stats – 2012-01-04 15:18:29
难道有一定的相关统计是完全错误的新创建的Foo
表?如果SQL Server在首次运行查询时自动更新统计信息,则第二次运行将根据最新的统计信息创建其执行计划。
如果你查看统计信息批量插入(与STATS_DATE
功能)之后,然后不得不取消了长时间运行的查询后,再次检查了吗?即使查询被取消,统计数据是否得到更新?
在这种情况下,Foo
之后批量插入可以帮助一个UPDATE STATISTICS
。
之间变化?FOO表的PK的统计日期在查询执行后为空。听起来像是一个可能的修复。但是,我设法通过将查询重写为'LEFT OUTER JOIN'来获得合理的执行时间。 – 2012-01-04 11:55:13
你可以看到在执行计划的任何差异?最后一个陈述需要4个小时,你可以看看估计的计划,而不是实际的(至少是一开始)。 – 2012-01-04 08:12:49
“如果我在几分钟后取消查询执行并仅选择最后一个查询并单独运行,它将在5秒内完成。“ - 你是否自己运行select,将结果插入空foo中或将结果插入到foo中?foo获取大部分来自BCP进程的130M行还是来自OtherTable的插入? – 2012-01-04 08:49:41
@MarkBannister ,我正在运行带有填充表的select,我只是从按下cancel的地方继续使用同一个脚本,所有的1.3M行都来自批量插入(这就是'(0 row(s)affected) '表示)。 – 2012-01-04 09:08:45