生成SQL查询计划需要5分钟，查询本身以毫秒为单位运行。这是怎么回事？

I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatten out. Unfortunately, it's 3rd party and I cannot change it).

我在SQL Server 2008上运行的存储过程相当复杂（或者看起来很丑陋，取决于你如何看待它）。它基于具有pk表和fk表的视图的大量逻辑。 fk表与pk表连接的次数略多于30次（fk表的设计很差 - 它使用了我需要展平的名称值对。不幸的是，它是第三方而我无法改变它）。

Anyway, it had been running fine for weeks until I periodically noticed a run that would take 3-5 minutes. It turns out that this is the time it takes to generate the query plan. Once the query plan exists and is cached, the stored procedure itself runs very efficiently. Things run smoothly until there is a reason to regenerate and cache the query plan again.

无论如何，它已经运行好几周，直到我定期发现需要3-5分钟的运行。事实证明，这是生成查询计划所需的时间。一旦查询计划存在并被缓存，存储过程本身就会非常有效地运行。事情顺利进行，直到有理由再次重新生成和缓存查询计划。

Has anyone seen this? Why does it take so long to generate the plan? Are there ways to make it come up with a plan faster?

有没有人见过这个？为什么生成计划需要这么长时间？有没有办法让它更快地提出计划？

3 个解决方案

#1

You can try using a Plan Guide. Plan generation will still last some time, but should be significantly shorter.

您可以尝试使用计划指南。计划生成仍将持续一段时间，但应显着缩短。

#2

Have you considered rewriting your 30-join SELECT to smth like this?

您是否考虑过像这样重写30-join SELECT？

SELECT [key], NULL AS [a], NULL AS [b]
  INTO #temp
  FROM [pk-table]

UPDATE t SET t.[a] = fk.[a], t.[b] = fk.[b]
  FROM #temp t
  INNER JOIN (
    SELECT f.[key],
      MAX(CASE WHEN f.[name] = 'a' THEN f.[value] ELSE NULL END) AS [a],
      MAX(CASE WHEN f.[name] = 'b' THEN f.[value] ELSE NULL END) AS [b]
    FROM [fk-table] f
    GROUP BY f.[key]
    ) fk ON (fk.[key] = t.[key]

Although it's maybe not an answer to your original question :)

虽然它可能不是你原来问题的答案:)

#3

Something will have caused the plan to require recompiling such as a statistics update or DDL change. The list if here: Execution Plan Caching and Reuse

有些事情会导致计划需要重新编译，例如统计更新或DDL更改。列表如果：执行计划缓存和重用

The query in it's current form will always take 3-5 minutes to recompile: this can't be avoided.

它当前表单中的查询总是需要3-5分钟才能重新编译：这是无法避免的。

Assuming you can't change it (PIVOT, use a trigger to maintain a "proper" table etc), then you can only control when the recompilation happens.

假设您无法更改它（PIVOT，使用触发器维护“正确”表等），那么您只能控制何时重新编译。

Remus' plan guide answer is one way. I'd also look at my statistic maintenance and ensure it's done overnight say, so it only happens once at start of day

Remus的计划指南答案是一种方式。我也会看看我的统计维护并确保它在一夜之间完成，所以它只在一天开始时发生

#1