为了账号安全,请及时绑定邮箱和手机立即绑定

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

/ 猿问

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

这没有帮助。



这没有帮助。


实体“ 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查询视图。


查看完整描述

3 回答

?
慕斯卡3215842

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


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


查看完整回答
反对 回复 2019-09-18
?
烙印99

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


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

....


查看完整回答
反对 回复 2019-09-18
?
斯蒂芬大帝

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


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


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

GO

EXEC sp_updatestats

GO 

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


查看完整回答
反对 回复 2019-09-18

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信