Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jubjubber
Starting Member
3 Posts |
 Posted - 2008-07-15 : 20:55:23
|
| I am using SQL Server 2005 with an ASP.NET front end. I have a stored procedure where a query is dynamically generated and contained in a variable @QueryString. At the end of the stored procedure, I run this command:Exec (@QueryString)to execute the query. This stored procedure is called by an ASP.NET page and values are returned to a dataset.The problem is that when the ASP.NET page calls the stored procedure, it takes a long time for data to be returned (30 seconds).I did a test where I took the query that is contained in @QueryString and instead of calling Exec (@QueryString), I had the query hard coded into the stored procedure itself. Now, when the ASP.NET page calls this stored procedure, values are returned quickly (3 seconds). That is, I commented out the Exec (@QueryString) and instead, have the stored procedure call the hard coded query.Does anyone know what I am doing wrong? Does anyone know of a problem where using Exec in stored procedures in stored procedures in SQL Server 2005 - and called by ASP.NET pages - causes slow processing? Help! This used to return values quickly when I was running SQL Server 2000. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-15 : 21:40:19
|
| Compare execution plans of both. Also trace them in profiler, maybe optimizer didn't get efficient plan for dynamic query. |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-07-16 : 00:02:37
|
when you use dynamic sql with exec, you don't stand a good chance of a cache hit on your query plan. if you use a parameterized query with sp_executesql, you can make sure the plan is reused.for details: http://sommarskog.se/dynamic_sql.html
elsasoft.org |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-07-16 : 08:48:12
|
| Do you really need to use dynamic SQL? We cannot know for sure what the issue is without seeing more code, but in general you should avoid executing generated SQL whenever possible.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
Problem with EXEC in SQL Server 2005 Stored Proc