Slow Performance of using EXECUTE sp_executesql
-
HI Guys need ur advise here I am facing this issue that when i use the sp_executesql to execute my statement, the query time is way longer then normal. Previously i am using this methed in stored procedure simpely coding the statement SELECT * FROM Bla Bla but when i change to using DECLARE @SQL AS NVARCHAR(MAX) SET @SQL = 'SELECT * FROM bla bla Table' EXECUTE sp_executesql @SQL the query time slow down greatly i change to using sp_executesql because i need to create the STATEMENT base on some condition checking will be gald if u guys have any advise Thanks a million KaKaShi HaTaKe
-
HI Guys need ur advise here I am facing this issue that when i use the sp_executesql to execute my statement, the query time is way longer then normal. Previously i am using this methed in stored procedure simpely coding the statement SELECT * FROM Bla Bla but when i change to using DECLARE @SQL AS NVARCHAR(MAX) SET @SQL = 'SELECT * FROM bla bla Table' EXECUTE sp_executesql @SQL the query time slow down greatly i change to using sp_executesql because i need to create the STATEMENT base on some condition checking will be gald if u guys have any advise Thanks a million KaKaShi HaTaKe
This might be because SQL is not choosing the best execution plan. Check the execution plan which is followed to execute that dynamic query. Although, I am not aware that there is much you can do about this (except for getting rid of dynamic queries if possible), you can check out parameter sniffing if it can help. Here[^] is an article that tells about it.
-
HI Guys need ur advise here I am facing this issue that when i use the sp_executesql to execute my statement, the query time is way longer then normal. Previously i am using this methed in stored procedure simpely coding the statement SELECT * FROM Bla Bla but when i change to using DECLARE @SQL AS NVARCHAR(MAX) SET @SQL = 'SELECT * FROM bla bla Table' EXECUTE sp_executesql @SQL the query time slow down greatly i change to using sp_executesql because i need to create the STATEMENT base on some condition checking will be gald if u guys have any advise Thanks a million KaKaShi HaTaKe
Everyone keeps telling me that there is little or no difference between a stored proc and dynamic SQL, then I come across a post like this one and I think they are full of bullshit. Change your var from nvarchar(max) to varchar(8000), it may help and cannot hurt. REALLY make sure that you must use dynamic sql, there is often a way to get around using it if you construct your query correctly. Using case, conditional where and even if clauses to avoid dynamic sql. As danish suggested, take a look at the execution plan of the dynamic and a normal version of the proc and see where you are paying the penalty.
Never underestimate the power of human stupidity RAH
-
Everyone keeps telling me that there is little or no difference between a stored proc and dynamic SQL, then I come across a post like this one and I think they are full of bullshit. Change your var from nvarchar(max) to varchar(8000), it may help and cannot hurt. REALLY make sure that you must use dynamic sql, there is often a way to get around using it if you construct your query correctly. Using case, conditional where and even if clauses to avoid dynamic sql. As danish suggested, take a look at the execution plan of the dynamic and a normal version of the proc and see where you are paying the penalty.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
Everyone keeps telling me that there is little or no difference between a stored proc and dynamic SQL, then I come across a post like this one and I think they are full of bullsh*t.
They obviously are. Just ask a simple question: What takes more time - cooking and eating or just eating? They should understand.