sp_executesql 是不是有性能問題？

[弘毅致远]

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被這個拖死了。

哪位大大有例似經驗的，取個經。

[弘毅致远]

[Quote=引用 13 楼 acherat 的回复:]
引用 12 楼 zuo_hy 的回复:

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

2S。


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

我是說把print處理的sql，另外的查詢分析器窗口2S以內就OK，但用sp_executesql 來執行動態構造出來的sql就不行了。

[guguda2008]

是不是需要考虑查询计划重用的问题。如果能重现锁死场景的话，加上SET SHOW_PLAN_TEXT ON，看看直接运行2S的场景和锁死的场景的计划是否有不同。

如果实际执行计划相同，无阻塞，速度应该是一样的。

[AcHerat]

你把帖子的这整段放进一个查询分析器里执行可以出来么？耗时。

[guguda2008]

你的内容太长了，没仔细看。SP_EXECUTESQL如果是内容相同传入参数不同可能会使用之前生成的执行计划，与直接执行SQL语句的时候的计划可能有所不同，也许晕这里有问题。

[-狙击手-]

sp_executesql是要缓存计划的,耗时必然,但那也是初次
不至于不执行

当你运行sp_executesql时,开另一个查询:sp_lock 看看

[弘毅致远]

[Quote=引用 19 楼 acherat 的回复:]
你把帖子的这整段放进一个查询分析器里执行可以出来么？耗时。
[/Quote]

貼出來的這段執行不出來，執行就掛了。
中午吃飯前點了下，吃完飯回來還在run。

[guguda2008]

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

[guguda2008]

这样的猜想也能解释直接运行语句时速度很快的原因了，因为语句不同，所以执行计划重新生成了。

[m4897625]

我觉得是 print后把拼接的组织了

[-狙击手-]

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

也不至于出现他说的整个执行不行了,一个2-3S的语句,再性能差不会半天不反应吧

[AcHerat]

楼主试试把语句里的with (nolock) 去掉试试，也有可能是因为这个重新指定索引引起的。

[-狙击手-]

全部使用完全限定对象名试试

[guguda2008]

[Quote=引用 26 楼 happyflystone 的回复:]

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


也不至于出现他说的整个执行不行了,一个2-3S的语句,再性能差不会半天不反应吧
[/Quote]
所以应该同时检查锁和IO的情况，监视超时语句的实际执行计划、执行期间的锁等待情况以及IO消耗。如果直接执行没问题的话阻塞的可能性其实不大的。我只能想到IO的问题。

[弘毅致远]

[Quote=引用 27 楼 acherat 的回复:]
楼主试试把语句里的with (nolock) 去掉试试，也有可能是因为这个重新指定索引引起的。
[/Quote]

靜態的sql去掉和沒去掉差不多。
sp_execute也是一樣，去掉了還是run不來。

[-狙击手-]

既然担心Parameter sniffing问题,那最后请加上:option(recompile)

[guguda2008]

我是觉得能重现场景的话，只要比较一下直接执行和exec的计划和IO就能看出大概了。

[弘毅致远]

[Quote=引用 28 楼 happyflystone 的回复:]
全部使用完全限定对象名试试
[/Quote]
完全限定对象名 不懂/

[AcHerat]

这个问题确实有点怪！还是看看锁和执行计划的情况，对于sp_executesql来说，看下到底是哪里发生阻塞了。

[-狙击手-]

EXEC sp_executesql @sql,N'@user_id nvarchar(50), @Now smalldatetime,@case_type nvarchar(50)',@user_id,@Now,@case_type
---

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

或是说:
如果print出的语句在查询分析器里快,而在应用程序里调用带sp_exec...的语句没有结果的话,你要考虑你的SQL语句是不是警告性的错误输出,那样会影响你的应用程序判断而造成超时的假现象

[弘毅致远]

還是sql語句有效能問題，通過調整sql語句，暫時解決了這個問題。