May 6, 2009 at 9:21 am
I have a stored proc that takes a parameter that based on its value, calls 1 of 2 other procs. When a Reporting Services report calls the proc via a dataset, the data retrieval takes about 83 seconds. When I call the same proc with the same parameters via Management Studio, the result set is returned in about 1.5 seconds. How is this possible?
The only thing that I can think of is that I changed the stored proc to branch to another stored proc instead of having all the SQL in one proc. Help!!!
May 6, 2009 at 9:35 am
Update:
Based on another posting that I found via google, I deleted the main stored proc and the 2 procs called from the main stored proc and recreated them. For what ever reason, this fixed the issue with the time of data retrieval. The report now generates in under 2 seconds. But according to the posting, this is only a temporary solution since after a period of time, the report generation degrades, and he has to delete and recreate the stored proc again.
Obviously, this is not a permanet solution. Any ideas as to what is happening and how to fix this issue permanently?
Thanks.
May 6, 2009 at 10:37 am
May 6, 2009 at 11:03 am
Awesome. You are the greatest. Thanks for the info.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply