Code
2024-03-15 07:00:00

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

我还尝试过用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) 2008年3月7日21:29:56 版权所有(c) 1988-2003微软公司 Windows NT 5.2标准版(Build 3790: Service Pack 2)

但是，如果没有参数嗅探，SQL Server不能像SQL Server运行查询一样快，这是什么原因呢?

我的下一个尝试是让StoredProcedureA调用StoredProcedureB调用StoredProcedureC调用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版本的查询上提供任何查询提示?如果SQL Server在这两个实例中准备了不同的执行计划，您是否可以使用一个提示告诉它使用哪个索引，以便计划与第一个匹配?

一些例子，你可以点击这里。

编辑:如果您可以在这里发布您的查询计划，也许我们可以确定计划之间的一些差异。

第二:将链接更新为特定于SQL-2000。你必须向下滚动，但有第二个标题为“表格提示”，这就是你要找的。

第三:“坏”查询似乎忽略了“Openers”表上的[IX_Openers_SessionGUID] -添加一个INDEX提示来强制它使用该索引是否会改变事情?

2009-01-13 21:57:14

您尝试过重新构建Report_Opener表上的统计数据和/或索引吗?如果统计数据仍然显示数据库首次启动时的数据，那么SP的所有重新遵从都将毫无价值。

初始查询本身工作得很快，因为优化器可以看到参数永远不会为空。在SP的情况下，优化器不能确保参数永远不会为空。

2009-01-13 21:57:54

我有另一个主意。如果你创建这个基于表的函数:

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只是在某个地方做了一个错误的假设，也许这将迫使它纠正这个假设。我不想多走一步，但我不确定还有什么原因。

2009-01-13 23:15:57

这可能是不太可能的，但考虑到你观察到的行为是不寻常的，它需要检查，没有人提到过它。

您是否绝对确定所有对象都由dbo拥有，并且您没有自己或其他用户拥有的流氓副本?

当我偶尔看到奇怪的行为时，这是因为一个对象实际上有两个副本，你得到哪一个取决于指定的内容和你登录的身份。例如，一个视图或过程的两个副本完全有可能具有相同的名称，但属于不同的所有者——当您不是作为dbo登录到数据库，并且在创建对象时忘记将dbo指定为对象所有者时，就会出现这种情况。

请注意，在文本中，您正在运行一些没有指定所有者的东西，例如

sp_recompile ViewOpener

例如，如果dbo和[一些其他用户]拥有viewOpener的两个副本，那么如果你没有指定，你实际重新编译哪一个取决于情况。Report_Opener视图也是如此——如果有两个副本(它们可能在规范或执行计划上不同)，那么使用哪个取决于情况——由于没有指定所有者，因此完全有可能您的临时查询可能使用一个，而编译过程可能使用另一个。

正如我所说，这可能是不太可能的，但这是可能的，应该检查，因为您的问题可能是您只是在错误的地方寻找错误。

2009-01-14 09:57:32

我发现了问题，下面是存储过程的慢版本和快版本的脚本:

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万行的即时线轴查询的差异:

慢:设置ANSI_NULLS关闭

快速:设置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。

微软在KB296769中承认了这一事实(错误:不能使用SQL企业管理器创建包含链接服务器对象的存储过程)。解决方法是在存储过程对话框中包含ANSI_NULLS选项:

Set ANSI_NULLS ON
Go
Create Proc spXXXX as
....
2009-01-14 15:27:01

我有同样的问题，原来的海报，但引用的答案没有解决我的问题。来自存储过程的查询仍然运行得非常慢。

我在这里找到了另一个答案“参数嗅探”，谢谢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秒。

2009-07-08 02:26:15

这次你发现问题了。如果下次你不那么幸运，想不出来，你可以使用计划冻结，不要再担心错误的执行计划。

2009-07-08 02:37:48

为您的数据库执行此操作。我也有同样的问题-它在一个数据库中工作得很好，但当我使用SSIS导入(不是通常的恢复)将这个数据库复制到另一个数据库时，这个问题发生在我的大多数存储过程中。所以在谷歌了一些之后，我找到了Pinal Dave的博客(顺便说一下，我遇到了他的大部分帖子，确实帮助了我很多，所以感谢Pinal Dave)。

我在我的数据库上执行以下查询，它纠正了我的问题:

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO

希望这能有所帮助。只是把帮助过我的人的帮助传递给他们。

2009-12-04 21:34:50

以下是解决方案:

create procedure GetOrderForCustomers(@CustID varchar(20))

as

begin

select * from orders

where customerid = ISNULL(@CustID, '')

end

——就是这样

2010-08-27 17:02:25

我就遇到过这个问题。我的问题是这样的:

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

我将数据类型更改为datetime，瞧!从30分钟变成了1分钟!

create procedure my_procedure (@dtFrom datetime)
as
select a, b, c from sometable where date > @dtFrom
2014-03-06 08:54:59

这可能听起来很愚蠢，而且从SessionGUID的名称来看似乎很明显，但是列是Report_Opener上的唯一标识符吗?如果不是，您可能想要尝试将其强制转换为正确的类型，并给它一个机会或将变量声明为正确的类型。

作为sproc的一部分创建的计划可能不直观地工作，并在一个大表上执行内部强制转换。

2015-07-15 16:45:43

我也面临着同样的问题&这篇文章对我很有帮助，但没有一个帖子的答案解决了我的具体问题。我想把这个对我有用的解决方法贴出来，希望它能帮助到其他人。

https://stackoverflow.com/a/24016676/814299

在查询的最后，添加OPTION (OPTIMIZE FOR (@now UNKNOWN))

2017-05-31 15:58:13

推荐文章

aliyun

最新文章

标签

2024 code 京ICP备15047053号-1