SQL Server：查询快，但过程慢
查询运行速度很快：
DECLARE @SessionGUID uniqueidentifier
SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908'
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
子树成本：0.502
但是将相同的 SQL 放在存储过程中运行速度很慢，并且执行计划完全不同
CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
EXECUTE ViewOpener @SessionGUID
子树成本：19.2
我跑了
sp_recompile ViewOpener
而且它仍然运行相同（很糟糕），我还将存储过程更改为
CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
SELECT *, 'recompile please'
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
再回来，试图真正欺骗它重新编译。
我已经删除并重新创建了存储过程，以便让它生成一个新计划。
我尝试通过使用诱饵变量强制重新编译并防止参数嗅探：
CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
DECLARE @SessionGUIDbitch uniqueidentifier
SET @SessionGUIDbitch = @SessionGUID
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUIDbitch
ORDER BY CurrencyTypeOrder, Rank
我还尝试定义存储过程 WITH RECOMPILE：
CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier
WITH RECOMPILE
AS
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
所以它的计划永远不会被缓存，我尝试在执行时强制重新编译：
EXECUTE ViewOpener @SessionGUID WITH RECOMPILE
这没有帮助。
我尝试将过程转换为动态 SQL：
CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier
WITH RECOMPILE AS
DECLARE @SQLString NVARCHAR(500)
SET @SQLString = N'SELECT *
FROM Report_OpenerTest
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank'
EXECUTE sp_executesql @SQLString,
N'@SessionGUID uniqueidentifier',
@SessionGUID
这没有帮助。
实体“Report_Opener”是一个未编入索引的视图。该视图仅引用基础表。没有表包含计算列，索引或其他。
对于它的地狱，我尝试创建视图
SET ANSI_NULLS ON
SET QUOTED_IDENTIFER ON
那并没有解决它。
这是怎么回事
查询速度很快
将查询移动到视图中，从视图中选择很快
从存储过程的视图中选择慢 40 倍？
我尝试将视图的定义直接移动到存储过程中（违反 3 条业务规则，并破坏了一个重要的封装），这使得它只慢了大约 6 倍。
为什么存储过程版本这么慢？ SQL Server 运行 ad-hoc SQL 比其他类型的 ad-hoc SQL 更快的原因是什么？
我真的不想
在代码中嵌入 SQL
更改所有代码 Microsoft SQL Server 2000 - 8.00.2050 (Intel X86) Mar 7 2008 21:29:56 版权所有 (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
但是，如果不是参数嗅探，SQL Server 无法像 SQL Sever 运行查询一样快速运行的原因是什么。
我的下一次尝试是让 StoredProcedureA call StoredProcedureB call StoredProcedureC call StoredProcedureD 来查询视图。
如果做不到这一点，让存储过程调用存储过程，调用 UDF，调用 UDF，调用存储过程，调用 UDF 来查询视图。
总而言之，以下内容在 QA 中运行得很快，但在放入存储过程时速度很慢：
原本的：
--Runs fine outside of a stored procedure
SELECT *
FROM Report_OpenerTest
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
sp_executesql：
--Runs fine outside of a stored procedure
DECLARE @SQLString NVARCHAR(500)
SET @SQLString = N'SELECT *
FROM Report_OpenerTest
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank'
EXECUTE sp_executesql @SQLString,
N'@SessionGUID uniqueidentifier',
@SessionGUID
EXEC(@sql)：
--Runs fine outside of a stored procedure
DECLARE @sql NVARCHAR(500)
SET @sql = N'SELECT *
FROM Report_OpenerTest
WHERE SessionGUID = '''+CAST(@SessionGUID AS varchar(50))+'''
ORDER BY CurrencyTypeOrder, Rank'
EXEC(@sql)
执行计划
好计划：
|--Sort(ORDER BY:([Expr1020] ASC, [Currencies].[Rank] ASC))
|--Compute Scalar(DEFINE:([Expr1020]=If ([Currencies].[CurrencyType]='ctCanadianCash') then 1 else If ([Currencies].[CurrencyType]='ctMiscellaneous') then 2 else If ([Currencies].[CurrencyType]='ctTokens') then 3 else If ([Currencies].[CurrencyType]
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([Openers].[OpenerGUID]))
|--Filter(WHERE:((([Currencies].[IsActive]<>0 AND [Currencies].[OnOpener]<>0) AND ((((((([Currencies].[CurrencyType]='ctUSCoin' OR [Currencies].[CurrencyType]='ctMiscellaneousUS') OR [Currencies].[CurrencyType]='ctUSCash') OR [Currencies].
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Currencies].[CurrencyGUID], [Openers].[OpenerGUID]) WITH PREFETCH)
| |--Nested Loops(Left Outer Join)
| | |--Bookmark Lookup(BOOKMARK:([Bmk1016]), OBJECT:([GrobManagementSystemLive].[dbo].[Windows]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Openers].[WindowGUID]))
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1014]), OBJECT:([GrobManagementSystemLive].[dbo].[Openers]))
| | | | |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_SessionGUID]), SEEK:([Openers].[SessionGUID]=[@SessionGUID]) ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Windows].[IX_Windows]), SEEK:([Windows].[WindowGUID]=[Openers].[WindowGUID]) ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType]))
| |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID] AND [OpenerDetails].[CurrencyGUID]=[Currenc
|--Hash Match(Cache, HASH:([Openers].[OpenerGUID]), RESIDUAL:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]))
|--Stream Aggregate(DEFINE:([Expr1006]=SUM(If (((([Currencies].[CurrencyType]='ctMiscellaneous' OR [Currencies].[CurrencyType]='ctTokens') OR [Currencies].[CurrencyType]='ctChips') OR [Currencies].[CurrencyType]='ctCanadianCoin') OR [
|--Nested Loops(Inner Join, OUTER REFERENCES:([OpenerDetails].[CurrencyGUID]) WITH PREFETCH)
|--Nested Loops(Inner Join)
| |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_OneOpenerPerSession]), SEEK:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
|--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[PK_Currencies_CurrencyGUID]), SEEK:([Currencies].[CurrencyGUID]=[OpenerDetails].[CurrencyGUID]) ORDERED FORWARD)
糟糕的计划
|--Sort(ORDER BY:([Expr1020] ASC, [Currencies].[Rank] ASC))
|--Compute Scalar(DEFINE:([Expr1020]=If ([Currencies].[CurrencyType]='ctCanadianCash') then 1 else If ([Currencies].[CurrencyType]='ctMiscellaneous') then 2 else If ([Currencies].[CurrencyType]='ctTokens') then 3 else If ([Currencies].[Currency
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([Openers].[OpenerGUID]))
|--Filter(WHERE:((([Currencies].[IsActive]<>0 AND [Currencies].[OnOpener]<>0) AND ((((((([Currencies].[CurrencyType]='ctUSCoin' OR [Currencies].[CurrencyType]='ctMiscellaneousUS') OR [Currencies].[CurrencyType]='ctUSCash') OR [Currenc
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Currencies].[CurrencyGUID], [Openers].[OpenerGUID]) WITH PREFETCH)
| |--Filter(WHERE:([Openers].[SessionGUID]=[@SessionGUID]))
| | |--Concatenation
| | |--Nested Loops(Left Outer Join)
| | | |--Table Spool
| | | | |--Hash Match(Inner Join, HASH:([Windows].[WindowGUID])=([Openers].[WindowGUID]), RESIDUAL:([Windows].[WindowGUID]=[Openers].[WindowGUID]))
| | | | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Windows].[IX_Windows_CageGUID]))
| | | | |--Table Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Openers]))
| | | |--Table Spool
| | | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType]))
| | |--Compute Scalar(DEFINE:([Openers].[OpenerGUID]=NULL, [Openers].[SessionGUID]=NULL, [Windows].[UseChipDenominations]=NULL))
| | |--Nested Loops(Left Anti Semi Join)
| | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType]))
| | |--Row Count Spool
| | |--Table Spool
| |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID] AND [OpenerDetails].[CurrencyGUID]=[Cu
|--Hash Match(Cache, HASH:([Openers].[OpenerGUID]), RESIDUAL:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]))
|--Stream Aggregate(DEFINE:([Expr1006]=SUM([partialagg1034]), [Expr1007]=SUM([partialagg1035]), [Expr1008]=SUM([partialagg1036]), [Expr1009]=SUM([partialagg1037]), [Expr1010]=SUM([partialagg1038]), [Expr1011]=SUM([partialagg1039]
|--Nested Loops(Inner Join)
|--Stream Aggregate(DEFINE:([partialagg1034]=SUM(If (((([Currencies].[CurrencyType]='ctMiscellaneous' OR [Currencies].[CurrencyType]='ctTokens') OR [Currencies].[CurrencyType]='ctChips') OR [Currencies].[CurrencyType]='
| |--Nested Loops(Inner Join, OUTER REFERENCES:([OpenerDetails].[CurrencyGUID]) WITH PREFETCH)
| |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
| |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[PK_Currencies_CurrencyGUID]), SEEK:([Currencies].[CurrencyGUID]=[OpenerDetails].[CurrencyGUID]) ORDERED FORWARD)
|--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_OneOpenerPerSession]), SEEK:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
不好的是急切地绕制 600 万行；另一个不是。
注意：这不是关于调整查询的问题。我有一个快速运行的查询。我只希望 SQL Server 从存储过程中快速运行。
sp_recompile 加一 - 修复了我的类似问题
我遇到了与原始海报相同的问题，但引用的答案并没有为我解决问题。从存储过程中查询仍然运行得很慢。
我找到了另一个答案 here "Parameter Sniffing"，谢谢 Omnibuzz。归结为在存储过程查询中使用“局部变量”，但阅读原文以获得更多理解，这是一篇很棒的文章。例如
慢方式：
CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
SELECT *
FROM orders
WHERE customerid = @CustID
END
快捷方式：
CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))
AS
BEGIN
DECLARE @LocCustID varchar(20)
SET @LocCustID = @CustID
SELECT *
FROM orders
WHERE customerid = @LocCustID
END
这样做将我的执行时间从 5 多分钟减少到大约 6-7 秒。
我发现了问题，这里是存储过程的慢版本和快版本的脚本：
dbo.ViewOpener__RenamedForCruachan__Slow.PRC
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Slow
@SessionGUID uniqueidentifier
AS
SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
dbo.ViewOpener__RenamedForCruachan__Fast.PRC
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Fast
@SessionGUID uniqueidentifier
AS
SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
如果你没有发现差异，我不怪你。区别根本不在于存储过程。将 0.5 成本的快速查询转变为执行 600 万行的急切假脱机的区别：
慢： SET ANSI_NULLS OFF
快速： SET ANSI_NULLS ON
这个答案也可以说得通，因为视图确实有一个 join 子句，上面写着：
(table.column IS NOT NULL)
因此涉及到一些NULL。
通过返回 Query Analizer 并运行进一步证明了解释
SET ANSI_NULLS OFF
.
DECLARE @SessionGUID uniqueidentifier
SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908'
.
SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
而且查询很慢。
所以问题不是，因为查询是从存储过程运行的。问题是 Enterprise Manager 的连接默认选项是 ANSI_NULLS off，而不是 QA 的默认选项 ANSI_NULLS on。
Microsoft 在 KB296769 中承认了这一事实（错误：无法使用 SQL 企业管理器创建包含链接服务器对象的存储过程）。解决方法是在存储过程对话框中包含 ANSI_NULLS 选项：
Set ANSI_NULLS ON
Go
Create Proc spXXXX as
....
ANSI_NULLS ON 是如何产生如此巨大的性能差异的。
JOIN 子句在 ANSI_NULLS OFF 时具有不同的含义。突然行匹配，导致优化器以完全不同的方式运行查询。想象一下，它们并没有消除 99.9% 的所有行，而是突然返回。
ANSI_NULLS OFF 已弃用并被视为不好的做法
我遇到了同样的问题，这篇文章对我很有帮助，但没有一个发布的答案解决了我的具体问题。我想发布对我有用的解决方案，希望它可以帮助其他人。
https://stackoverflow.com/a/24016676/814299
在查询结束时，添加 OPTION (OPTIMIZE FOR (@now UNKNOWN))
为您的数据库执行此操作。我有同样的问题 - 它在一个数据库中运行良好，但是当我使用 SSIS 导入（不是通常的还原）将此数据库复制到另一个数据库时，我的大多数存储过程都会出现这个问题。所以在谷歌搜索了一些之后，我找到了blog of Pinal Dave (which btw, I encountered most of his post and did help me a lot so thanks Pinal Dave)。
我在我的数据库上执行以下查询，它纠正了我的问题：
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
希望这可以帮助。只是传递帮助我的其他人的帮助。
DBCC REINDEX 已被弃用，因此您应该寻找替代方案。
DBCC DBREINDEX，MS 说：“此功能将在 Microsoft SQL Server 的未来版本中删除。不要在新的开发工作中使用此功能，并尽快修改当前使用此功能的应用程序。使用 ALTER INDEX反而。”
我遇到了这个问题。我的查询看起来像：
select a, b, c from sometable where date > '20140101'
我的存储过程定义如下：
create procedure my_procedure (@dtFrom date)
as
select a, b, c from sometable where date > @dtFrom
我将数据类型更改为日期时间，瞧！从30分钟到1分钟！
create procedure my_procedure (@dtFrom datetime)
as
select a, b, c from sometable where date > @dtFrom
这次你发现了你的问题。如果下次你运气不好并且无法弄清楚，你可以使用 plan freezing 并且不用担心错误的执行计划。
您是否尝试过重建 Report_Opener 表上的统计信息和/或索引。如果统计数据仍然显示数据库首次启用时的数据，那么 SP 的所有重新编译都将毫无价值。
初始查询本身运行很快，因为优化器可以看到参数永远不会为空。在 SP 的情况下，优化器无法确定参数永远不会为空。
尽管我通常反对它（尽管在这种情况下，您似乎有真正的理由），但您是否尝试过在查询的 SP 版本上提供任何查询提示？如果 SQL Server 在这两个实例中准备不同的执行计划，您可以使用提示告诉它使用什么索引，以便该计划与第一个匹配吗？
例如，you can go here。
编辑：如果您可以在此处发布您的查询计划，也许我们可以确定计划之间的一些差异。
第二：将链接更新为特定于 SQL-2000。您必须向下滚动一条路径，但还有第二个标题为“表格提示”，这就是您要寻找的。
第三：“坏”查询似乎忽略了“Openers”表上的 [IX_Openers_SessionGUID] - 添加 INDEX 提示以强制它使用该索引会改变事情吗？
这可能不太可能，但鉴于您观察到的行为不寻常，需要检查它并且没有其他人提到它。
您是否绝对确定所有对象都归 dbo 所有，并且您没有自己或其他用户拥有的恶意副本？
只是偶尔，当我看到奇怪的行为时，这是因为实际上有一个对象的两个副本，而您获得的副本取决于指定的内容以及您的登录身份。例如，完全有可能拥有同名但由不同所有者拥有的视图或过程的两个副本 - 当您没有以 dbo 身份登录数据库并且忘记将 dbo 指定为对象所有者时，可能会出现这种情况您创建对象。
请注意，在文本中您正在运行一些没有指定所有者的事情，例如
sp_recompile ViewOpener
例如，如果存在 dbo 和 [某个其他用户] 拥有的 viewOpener 的两个副本，那么如果您不指定，您实际重新编译哪个副本取决于具体情况。 Report_Opener 视图同上——如果有两个副本（并且它们的规格或执行计划可能不同），那么使用的内容取决于具体情况——并且由于您没有指定所有者，因此您的临时查询很可能使用一个并且编译过程可能会使用其他。
正如我所说，这可能不太可能，但有可能并且应该检查，因为您的问题可能是您只是在错误的地方寻找错误。
-- 解决方法如下：
create procedure GetOrderForCustomers(@CustID varchar(20))
as
begin
select * from orders
where customerid = ISNULL(@CustID, '')
end
- 而已
这听起来很傻，从 SessionGUID 的名称来看似乎很明显，但是该列是 Report_Opener 上的唯一标识符吗？如果没有，您可能想尝试将其转换为正确的类型并试一试或将您的变量声明为正确的类型。
作为 sproc 的一部分创建的计划可能会不直观地工作，并且会在一张大桌子上进行内部转换。
varchar 列与 nvarchar 值（例如 WHERE CustomerName = N'zrendall'）进行比较的 where 子句存在性能问题。 SQL Server 必须在比较之前将每个列值上转换为 nvarchar。
我有另一个想法。如果您创建这个基于表的函数会怎样：
CREATE FUNCTION tbfSelectFromView
(
-- Add the parameters for the function here
@SessionGUID UNIQUEIDENTIFIER
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
)
GO
然后使用以下语句从中选择（甚至将其放入您的 SP）：
SELECT *
FROM tbfSelectFromView(@SessionGUID)
看起来正在发生的事情（每个人都已经评论过）是 SQL Server 只是在某个错误的地方做了一个假设，也许这会迫使它纠正这个假设。我讨厌添加额外的步骤，但我不确定还有什么可能导致它。
