sp_executesql 是不是有性能問題？

弘毅致远 2011-07-23 03:34:28

DECLARE @user_id nvarchar(50)
DECLARE @case_type nvarchar(50)
DECLARE @case_status nvarchar(50)
DECLARE @page_size int
DECLARE @page_current int
DECLARE @item_count int

DECLARE @sql nvarchar(4000)
DECLARE @sql_temp nvarchar(1000)
DECLARE @sql_count nvarchar(4000)
DECLARE @sql_para nvarchar(4000)
DECLARE @sql_result nvarchar(4000)
DECLARE @orderby nvarchar(200)
DECLARE @canceled_status nvarchar(200)

DECLARE @Now smalldatetime
SET @Now = GETDATE()

SET @page_size = 20
SET @page_current = 1
SET @user_id = 'CEDDBB98-3023-4C61-9699-A8ABE5E13ED7'
SET @case_type = 'PR'
SET @item_count = 0
SET @case_status = 'finish'
set @canceled_status='canceled'

SET @sql = N'SELECT DISTINCT C.case_id,P.create_date AS process_date, P.process_id' +
' FROM MemberShip.dbo.users U INNER JOIN processes P with (nolock) ON U.user_id=P.process_user_id INNER JOIN cases C with (nolock) ON P.case_id=C.case_id INNER JOIN QueryTypeConfig Q ON Q.case_type = C.case_type ' +
--' left join PO_Confirm PC on PC.case_ID = C.Case_ID ' +
' WHERE '+
--' PC.confirm_status = 0 and '+
' P.finish_date IS NOT NULL AND ' +
' (C.Track_Status IS NULL OR C.Track_Status <> ''CANCELED'' ) AND ' +
' C.is_reject=0 AND Q.query_type = @case_type AND' +
' P.process_no=(SELECT MAX(process_no) FROM processes with (nolock) WHERE case_id=C.case_id) AND' +
' (EXISTS(SELECT P2.process_id FROM processes P2 with (nolock) WHERE P2.case_id=C.case_id AND P2.finish_date IS NOT NULL AND P2.process_user_id=@user_id)' +
' OR EXISTS(SELECT P2.process_id FROM processes P2 with (nolock) INNER JOIN MemberShip.dbo.Attorney Att ON Att.user_id = P2.process_user_id WHERE P2.case_id=C.case_id AND P2.finish_date IS NOT NULL AND Att.user_id_attorney=@user_id' +
' AND Att.validate_date_from<=@Now AND Att.validate_date_to>=@Now AND Att.is_accept=1 ) ' +
' OR EXISTS(SELECT P2.process_id FROM processes P2 with (nolock) INNER JOIN Processes_SpecialSign PS ON P2.process_id = PS.process_id WHERE P2.case_id=C.case_id AND PS.finish_date IS NOT NULL AND PS.user_id=@user_id)' +
' OR EXISTS(SELECT P2.process_id FROM processes P2 with (nolock) INNER JOIN Processes_SpecialSign PS ON P2.process_id = PS.process_id INNER JOIN MemberShip.dbo.Attorney Att ON Att.user_id = PS.user_id WHERE P2.case_id=C.case_id AND' +
' PS.finish_date IS NOT NULL AND Att.user_id_attorney=@user_id AND Att.validate_date_from<=@Now AND Att.validate_date_to>=@Now AND Att.is_accept=1 ))' +
' AND P.create_date >= DATEADD( month, -1, @Now ) AND P.create_date <= @Now'


print @sql

EXEC sp_executesql @sql,N'@user_id nvarchar(50), @Now smalldatetime,@case_type nvarchar(50)',@user_id,@Now,@case_type
-------------------------------------------------------------------------------------------------------------------------------------
如果直接執行print處理的sql，大約3S，
但通過sp_executesql來執行，直接跑死。最近DB Server被這個拖死了。

哪位大大有例似經驗的，取個經。
...全文
389 36 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
36 条回复
切换为时间正序
请发表友善的回复…
发表回复
弘毅致远 2011-07-25
  • 打赏
  • 举报
 回复
還是sql語句有效能問題，通過調整sql語句，暫時解決了這個問題。
-狙击手- 2011-07-23
  • 打赏
  • 举报
 回复
EXEC sp_executesql @sql,N'@user_id nvarchar(50), @Now smalldatetime,@case_type nvarchar(50)',@user_id,@Now,@case_type
---

你说一直在运行 ,你是放在查询分析器里执行还是你的其它程序里调用?

或是说:
如果print出的语句在查询分析器里快,而在应用程序里调用带sp_exec...的语句没有结果的话,你要考虑你的SQL语句是不是警告性的错误输出,那样会影响你的应用程序判断而造成超时的假现象
AcHerat 元老 2011-07-23
  • 打赏
  • 举报
 回复
这个问题确实有点怪！还是看看锁和执行计划的情况，对于sp_executesql来说，看下到底是哪里发生阻塞了。
弘毅致远 2011-07-23
  • 打赏
  • 举报
 回复
[Quote=引用 28 楼 happyflystone 的回复:]
全部使用完全限定对象名试试
[/Quote]
完全限定对象名 不懂/
guguda2008 2011-07-23
  • 打赏
  • 举报
 回复
我是觉得能重现场景的话，只要比较一下直接执行和exec的计划和IO就能看出大概了。
-狙击手- 2011-07-23
  • 打赏
  • 举报
 回复
既然担心Parameter sniffing问题,那最后请加上:option(recompile)
弘毅致远 2011-07-23
  • 打赏
  • 举报
 回复
[Quote=引用 27 楼 acherat 的回复:]
楼主试试把语句里的with (nolock) 去掉试试，也有可能是因为这个重新指定索引引起的。
[/Quote]

靜態的sql去掉和沒去掉差不多。
sp_execute也是一樣，去掉了還是run不來。
guguda2008 2011-07-23
  • 打赏
  • 举报
 回复
[Quote=引用 26 楼 happyflystone 的回复:]

引用 23 楼 guguda2008 的回复:
石头哥，我的意思是有可能sp_executesql第一次缓存的不是最优查询计划，传入了一个需要表扫描或聚集索引扫描的参数，然后后面执行的大部分都只需要索引查找，这样的话用sp_executesql执行的语句每次都会加载所有数据页，造成不必要的IO流量。


也不至于出现他说的整个执行不行了,一个2-3S的语句,再性能差不会半天不反应吧
[/Quote]
所以应该同时检查锁和IO的情况，监视超时语句的实际执行计划、执行期间的锁等待情况以及IO消耗。如果直接执行没问题的话阻塞的可能性其实不大的。我只能想到IO的问题。
-狙击手- 2011-07-23
  • 打赏
  • 举报
 回复
全部使用完全限定对象名试试
AcHerat 元老 2011-07-23
  • 打赏
  • 举报
 回复
楼主试试把语句里的with (nolock) 去掉试试，也有可能是因为这个重新指定索引引起的。
-狙击手- 2011-07-23
  • 打赏
  • 举报
 回复
[Quote=引用 23 楼 guguda2008 的回复:]
石头哥，我的意思是有可能sp_executesql第一次缓存的不是最优查询计划，传入了一个需要表扫描或聚集索引扫描的参数，然后后面执行的大部分都只需要索引查找，这样的话用sp_executesql执行的语句每次都会加载所有数据页，造成不必要的IO流量。
[/Quote]

也不至于出现他说的整个执行不行了,一个2-3S的语句,再性能差不会半天不反应吧
m4897625 2011-07-23
  • 打赏
  • 举报
 回复
我觉得是 print后把拼接的组织了
guguda2008 2011-07-23
  • 打赏
  • 举报
 回复
这样的猜想也能解释直接运行语句时速度很快的原因了，因为语句不同，所以执行计划重新生成了。
guguda2008 2011-07-23
  • 打赏
  • 举报
 回复
石头哥，我的意思是有可能sp_executesql第一次缓存的不是最优查询计划，传入了一个需要表扫描或聚集索引扫描的参数，然后后面执行的大部分都只需要索引查找，这样的话用sp_executesql执行的语句每次都会加载所有数据页，造成不必要的IO流量。
弘毅致远 2011-07-23
  • 打赏
  • 举报
 回复
[Quote=引用 19 楼 acherat 的回复:]
你把帖子的这整段放进一个查询分析器里执行可以出来么？耗时。
[/Quote]

貼出來的這段執行不出來，執行就掛了。
中午吃飯前點了下，吃完飯回來還在run。
-狙击手- 2011-07-23
  • 打赏
  • 举报
 回复
sp_executesql是要缓存计划的,耗时必然,但那也是初次
不至于不执行

当你运行sp_executesql时,开另一个查询:sp_lock 看看
guguda2008 2011-07-23
  • 打赏
  • 举报
 回复
你的内容太长了，没仔细看。SP_EXECUTESQL如果是内容相同传入参数不同可能会使用之前生成的执行计划，与直接执行SQL语句的时候的计划可能有所不同，也许晕这里有问题。
AcHerat 元老 2011-07-23
  • 打赏
  • 举报
 回复
你把帖子的这整段放进一个查询分析器里执行可以出来么？耗时。
guguda2008 2011-07-23
  • 打赏
  • 举报
 回复
是不是需要考虑查询计划重用的问题。如果能重现锁死场景的话，加上SET SHOW_PLAN_TEXT ON，看看直接运行2S的场景和锁死的场景的计划是否有不同。

如果实际执行计划相同，无阻塞，速度应该是一样的。
弘毅致远 2011-07-23
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 acherat 的回复:]
引用 12 楼 zuo_hy 的回复:

引用 9 楼 acherat 的回复:
单独在查询分析器里执行的耗时是多少！

2S。


那说明这个SQL本身没有问题，你看下在程序里是怎么调用的，如果有数据库的连接，有没有释放或者关闭，看下数据库连接池的设定。
[/Quote]

我是說把print處理的sql，另外的查詢分析器窗口2S以內就OK，但用sp_executesql 來執行動態構造出來的sql就不行了。
加载更多回复（16）
SQL Server EXEC和sp_executesql的区别
SQL Server EXEC和sp_executesql的区别
SQL Server 中 EXEC 与 SP_EXECUTESQL 的区别.doc
SQL Server 中 EXEC 与 SP_EXECUTESQL 的区别 MSSQL为我们提供了两种动态执行SQL语句的命令，分别是 EXEC 和 SP_EXECUTESQL ，我们先来看一下两种方式的用法。 先建立一个表，并添加一些数据来进行演示： 复制代码 CREATE TABLE t_student( Id INT NOT NULL, Name NVARCHAR (10) NULL, Age TINYINT NULL, School NVARCHAR(20) NULL, Class NVARCHAR(10) NULL, Score FLOAT NULL, CONSTRAINT [PK_Student_Id] PRIMARY KEY CLUSTERED(Id) ) GO INSERT INTO t_student VALUES(1,'张小红',8,'育才小学','一班',92) INSERT INTO t_student VALUES(2,'王丽丽',8,'育才小学','一班',90) INSERT INTO t_student VALUES(3,'张燕',7,'云华小学','二班',86) INSERT INTO t_student VALUES(4,'刘华',6,'云华小学','二班',85) 复制代码 一、EXEC EXEC命令可以执行一个存储过程也可以执行一个动态SQL语句。先来看看怎么执行存储过程： 新建一个存储过程 SP_GetStudent ，返回 成绩大于90 分的学生： 复制代码 CREATE PROCEDURE [dbo].[Sp_GetStudent] @Score FLOAT, @Nums INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT * FROM t_student WHERE Score >=@Score SELECT @Nums=COUNT(1) FROM t_student WHERE Score >=@Score IF(@Nums>0) RETURN 1 ELSE RETURN 0 END GO 复制代码 该存储过程涉及了 查询操作、返回值和输出参数，我们来看用EXEC 命令如何调用： 复制代码 DECLARE @return_value int, @OutNums int EXEC @return_value = [dbo].[Sp_GetStudent] @Score = 90, @Nums = @OutNums OUTPUT SELECT @OutNums as N'大于90分的人数' SELECT '返回值' = @return_value GO 复制代码 执行结果： 我们发现EXEC 执行存储过程和我们平时程序执行一个方法是几乎一样的，返回值参数 直接就可以等于存储过程的执行后的返回值，输出参数 在后面需要增加 OUTPUT 关键字。 执行存储过程不是重点，重点是执行动态sql语句，同样看一下例子： DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT; SET @TableName = 't_Student'; SET @Score = 90; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10)) EXEC (@sql); 执行结果： 注意：在执行拼接SQL 语句的时候，的EXEC括号中只允许包含一个字符串变量，但是可以串联多个变量，如果我们直接执行这个SQL语句： --这是错误的调用 EXEC ('SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10))); 执行就会提示错误。但是这样就没有问题： 复制代码 DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT DECLARE @Sql2 NVARCHAR(MAX) SET @TableName = 't_Student'; SET @Score = 90; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) SET @Sql2=' WHERE Score >= '+CAST(@Score AS NVARCHAR(10)) EXEC (@sql+@sql2) 复制代码 EXEC 执行拼接sql语句的时候不支持 嵌入式参数，如下： DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX) SET @IN_Score = 90 SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score' EXEC (@sql) 通过上面的代码发现，EXEC 执行拼接的SQL语句的时候，不支持内嵌参数，包括输入参数和输出参数。有的时候我们想把得到的count(*)传出来，用EXEC是不好办到的。接下来，再来看看SP_EXECUTESQL的使用： 二、SP_EXECUTESQL： SP_EXECUTESQL 是在 SQL 2005中引入的新的系统存储过程，也是用来处理动态SQL 语句的。它比EXEC 更加灵活，首先也执行一下第一次的拼接SQL语句： DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT; SET @TableName = 't_Student'; SET @Score = 90; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10)) EXEC SP_EXECUTESQL @sql --注意这里没有了() 执行结果： SP_EXECUTESQL 支持内嵌参数： 先来看一下SP_EXECUTESQL的语法： sp_executesql [ @stmt = ] stmt [ {, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' } {, [ @param1 = ] 'value1' [ ,...n ] } ] 说明： [ @stmt = ] stmt 包含 Transact-SQL 语句或批处理的 Unicode 字符串。stmt 必须是 Unicode 常量或 Unicode 变量。不允许使用更复杂的 Unicode 表达式（例如使用 + 运算符连接两个字符串）。不允许使用字符常量。如果指定了 Unicode 常量，则必须使用 N 作为前缀。例如，Unicode 常量 N'sp_who' 是有效的，但是字符常量 'sp_who' 则无效。字符串的大小仅受可用数据库服务器内存限制。在 64 位服务器中，字符串大小限制为 2 GB，即 nvarchar(max) 的最大大小。stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项 [ @params = ] N'@parameter_namedata_type[ ,... n ] ' 包含 stmt 中嵌入的所有参数定义的字符串。字符串必须是 Unicode 常量或 Unicode 变量。每个参数定义由参数名称和数据类型组成。n 是表示附加参数定义的占位符。在 stmt 中指定的每个参数必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数，则不需要 @params。该参数的默认值为 NULL。 [ @param1 = ] 'value1' 参数字符串中定义的第一个参数的值。该值可以是 Unicode 常量，也可以是 Unicode 变量。必须为 stmt 中包含的每个参数提供参数值。如果 stmt 中的 Transact-SQL 语句或批处理没有参数，则不需要这些值。 [ OUT | OUTPUT ] 指示参数是输出参数。除非是公共语言运行 (CLR) 过程，否则 text、ntext 和 image 参数均可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以为游标占位符，CLR 过程除外。 n 附加参数值的占位符。这些值只能为常量或变量，不能是很复杂的表达式（例如函数）或使用运算符生成的表达式。 返回代码值 : 0（成功）或非零（失败） 结果集：从生成 SQL 字符串的所有 SQL 语句返回结果集 看不懂没有关系，通过例子就会非常明白的，依旧还执行上面的 SQL 语句： DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX) SET @IN_Score = 90 SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score' EXEC SP_EXECUTESQL @sql,N'@Nums INT OUT,@Score INT',@OUT_Nums OUTPUT,@IN_Score SELECT @OUT_Nums AS '人数' 执行结果： 需要注意的是： 1、要求动态Sql和动态Sql参数列表必须是NVARCHAR 2、动态Sql的参数列表与外部提供值的参数列表顺序必需一致 3、一旦使用了 '@name = value' 形式之后，所有后续的参数就必须以 '@name = value' 的形式传递，比如： DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX) SET @IN_Score = 90 SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score' EXEC SP_EXECUTESQL @stmt=@sql,@params=N'@Nums INT OUT,@Score INT',@Nums=@OUT_Nums OUTPUT,@Score=@IN_Score SELECT @OUT_Nums AS '人数' 通过上面的例子已经很清晰的表明了，在执行动态SQL 语句的时候，EXEC 和 SP_EXECUTESQL 的区别了，来总结一下： 1、 性能： 官方描述：sp_executesql stmt 参数中的 Transact-SQL 语句或批处理在执行 sp_executesql 语句时才编译。随后，将编译 stmt 中的内容，并将其作为执行计划运行。该执行计划独立于名为 sp_executesql 的批处理的执行计划。sp_executesql 批处理不能引用调用 sp_executesql 的批处理中声明的变量。sp_executesql 批处理中的本地游标或变量对调用 sp_executesql 的批处理是不可见的。对数据库上下文所做的更改只在 sp_executesql 语句结束前有效。如果只更改了语句中的参数值，则 sp_executesql 可用来代替存储过程多次执行 Transact-SQL 语句。因为 Transact-SQL 语句本身保持不变，仅参数值发生变化，所以 SQL Server 查询优化器可能重复使用首次执行时所生成的执行计划。 说通俗一点就是：如果用 EXEC 执行一条动态 SQL 语句，由于每次传入的参数不一样，所以每次生成的 @sql 就不一样，这样每执行一次SQL SERVER 就必须重新将要执行的动态 Sql 重新编译一次 。但是SP_EXECUTESQL 则不一样，由于将数值参数化，要执行的动态 Sql 永远不会变化，只是传入的参数的值在变化，那每次执行的时候就不用重新编译，速度和效率自然有所提升。 2、从上面的例子我们已经能够看出 SP_EXECUTESQL 命令比 EXEC 命令更灵活，因为它提供一个接口，该接口及支持输入参数也支持输出参数。 3、EXEC 执行纯动态SQL，执行时可能无法使用预编译的执行计划，关键是不安全，可以导致 SQL 注入 ，而 SP_EXECUTESQL 执行参数化动态 SQL ,执行时能使用预编译的执行计划，而且保存存储过程时就可以确定可以使用的预编译的执行计划，而且最重要的是“安全”，天然免疫SQL 注入 作者：Rising Sun 出处：http://www.cnblogs.com/lxblog/ 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利.
系统存储过程，sp_executesql
系统存储过程，sp_executesql  语言在这里！  sp_executesql  执行可以多次重用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。  语法  sp_executesql [@stmt =] stmt  [  {, [@params =] N’@parameter_name data_type [,…n]’ }  {, [@param1 =] ‘value1’ [,…n] }  ]  参数  [@stmt =] stmt  包含 Transact-SQL 语句或批处理的 Unicode 字符串，stmt 必须是
SQLServer:探讨EXEC与sp_executesql的区别详解
摘要1,EXEC的使用2，sp_executesql的使用MSSQL为我们提供了两种动态执行SQL语句的命令，分别是EXEC和sp_executesql;通常,sp_executesql则更具有优势，它提供了输入输出接口，而EXEC没有。还有一个最大的好处就是利用sp_executesql，能够重用执行计划，这就大大提供了执行性能(对于这个我在后面的例子中会详加说明)，还可以编写更安全的代码。EXEC在某些情况下会更灵活。除非您有令人信服的理由使用EXEC，否侧尽量使用sp_executesql. 1,EXEC的使用EXEC命令有两种用法，一种是执行一个存储过程，另一种是执行一个动态的批处理。
在sp_executesql中使用like字句的方法
在sp_executesql中可以执行参数化的sql，但是如果sql中有like，那么一般都要拼字符串，下面的例子把两者结合了起来，既有拼sql的灵活性，又有参数化sql的优点。
MS-SQL Server

34,838

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章