Extremely slow SP execution in Asp.net
-
Hi I have an interesting problem. I have written an SP to return some stats. When I run this through SQL Management studio - the result returns in 2 seconds or less. Now when I call this through asp.net - it takes anywhere from 2 minutes to 10 minutes+ (asp.net times out). I have tested this on my machine, and one of our offline production web servers. Now, when I attach the debugger to my asp.net code, where it makes the call, results are just as quick as when i run it through the management studio. Yesterday I thought I was onto something - the user I was connecting with, was being used by multiple sites and applications. I created a new user and tried using this. Now asp.net was returning the records as quickly as expected. However, this morning the performance has gone back to what it was, and it takes ages to run. I have connected to SQL management studio using the user credentials that the asp.net site is using (SQL connection string). When I run this, results return in 2 seconds. I also looked at the activity monitor - when I run this through management studio, CPU time shows as around 1000. When I run it through asp.net - this rises to between 100 000 and 400 000 in the CPU time. I am not using cursors, this is just a select, and usually returns 10 - 50 records at most. The query does access multiple databases, some of which are on a linked server. Thanks, Wayde
-
Hi I have an interesting problem. I have written an SP to return some stats. When I run this through SQL Management studio - the result returns in 2 seconds or less. Now when I call this through asp.net - it takes anywhere from 2 minutes to 10 minutes+ (asp.net times out). I have tested this on my machine, and one of our offline production web servers. Now, when I attach the debugger to my asp.net code, where it makes the call, results are just as quick as when i run it through the management studio. Yesterday I thought I was onto something - the user I was connecting with, was being used by multiple sites and applications. I created a new user and tried using this. Now asp.net was returning the records as quickly as expected. However, this morning the performance has gone back to what it was, and it takes ages to run. I have connected to SQL management studio using the user credentials that the asp.net site is using (SQL connection string). When I run this, results return in 2 seconds. I also looked at the activity monitor - when I run this through management studio, CPU time shows as around 1000. When I run it through asp.net - this rises to between 100 000 and 400 000 in the CPU time. I am not using cursors, this is just a select, and usually returns 10 - 50 records at most. The query does access multiple databases, some of which are on a linked server. Thanks, Wayde
Hi, I have found the solution, so am just posting an answer in case anyone else comes across this. Read the following link for information: http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx[^]