首页   技术笔记   网址导航   Json在线解析   二维码   Ip地址查询   在线流程图  
新用户注册 | 会员登录
当前位置:  开发笔记 > 编程语言 > 正文

SQL Server:查询速度快,但程序速度慢

作者：惬听风吟jyy_802 | 2023-09-01 03:13
如何解决《SQLServer:查询速度快,但程序速度慢》经验，为你挑选了4个好方法。

查询运行速度很快:

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
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

但是,如果没有参数嗅探,那么SQL Server无法像运行查询的SQL Sever那样快速运行.

我的下一次尝试将有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从存储过程快速运行.

小智.. 385

我和原版海报有同样的问题,但引用的答案并没有解决我的问题.查询仍然从存储过程运行得很慢.

我在这里找到了另一个答案"参数嗅探",感谢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秒.



1> 小智..：

我和原版海报有同样的问题,但引用的答案并没有解决我的问题.查询仍然从存储过程运行得很慢.

我在这里找到了另一个答案"参数嗅探",感谢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秒.

我是唯一一个对这种行为感到困惑的人吗？要求声明局部变量以防止参数嗅探？SQL Server是否应该足够聪明以防止这种情况发生？这只会导致微软短视设计恕我直言的不必要的代码臃肿.
+1但是,这很奇怪,并提出了很多问题,比如我们是否应该为所有程序执行此操作,如果没有,何时执行此操作？
现在可以使用查询提示 - OPTION(OPTIMIZE FOR(@varA UNKNOWN,@ varB UNKNOWN)来实现
15分钟 - > 8秒!生命保护
@BennettDill` WITH RECOMPILE`对我没什么影响,只有本地参数.
@ I46kok发生这种情况的原因是，正在缓存过程查询计划以及传递给它的参数。在后续调用中，生成的此查询计划将与新参数一起重用。这可能会引起问题，因为如果数据分布不均匀，则一个参数可能会生成次优计划，而另一个参数则会生成次优计划。使用局部变量本质上与OPTIMIZE FOR UNKNOWN相同，因为不能嗅探局部变量。但是，它仍然可能导致某些参数出现问题，这就是我认为OPTION（RECOMPILE）更好的原因。
这让我困惑了好几天!90分钟到不到10秒.
+1谢谢!几周来一直有这个问题,这看起来是修复.将此链接放在同一行中的其他一些可能的修复程序http://realsqlguy.com/dont-get-slimed-bad-parameter-sniffing/

2> Ian Boyd..：

我发现了问题,这是存储过程的慢速和快速版本的脚本:

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

这个答案也可以有意义,因为视图确实有一个连接子句,它说:

(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

而且查询很慢.

所以问题不在于查询是从存储过程运行的.问题是企业管理器的连接默认选项是ANSI_NULLS off,而不是ANSI_NULLS onQA的默认选项.

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

Set ANSI_NULLS ON
Go
Create Proc spXXXX as
....



3> 小智..：

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

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

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

希望这可以帮助.只是通过帮助我的其他人的帮助.

只是未来读者的一个FYI:`DBCC REINDEX`已被弃用,所以你应该寻找替代方案.

4> jessieloo..：

我遇到了同样的问题,这篇文章对我很有帮助,但没有一个已发布的答案解决了我的具体问题.我想发布对我有用的解决方案,希望它可以帮助别人.

/sf/ask/17360801/

在查询结束时,添加OPTION(OPTIMIZE FOR(@now UNKNOWN))

推荐阅读
devbox
惬听风吟jyy_802
这个屌丝很懒，什么也没留下！
关注作者
Tags | 热门标签
RankList | 热门文章
DevBox开发工具箱 | 专业的在线开发工具网站    京公网安备 11010802040832号  |  京ICP备19059560号-6
Copyright © 1998 - 2020 DevBox.CN. All Rights Reserved devBox.cn 开发工具箱 版权所有