Queries time out from asp.net web app but run fine from management studio

25. February 2013 15:57 by Chris in dev  //  Tags:   //   Comments (0)

I had a support issue from a client today which turned out to be an interesting one I hadn't come across before so I thought I would share. Thankfully, others had so once I had identified exactly what the issue was google and stack overflow were able to quickly assist.

The scenario that initially bemused me was that fact that running a complex search sproc from SQL Server Management Studio was returning in a couple of seconds but exactly the same parameters fired into the sproc from ASP.NET caused the web app to timeout. Earlier I had even tried extending the default commandTimeout from 30secs to 4mins without success so SQL Server had really got it's knickers in a twist. Further the issue just appeared one day and I also couldn't replicate in the staging environment, which had similar data volumes.

It turns out others had hit the issue before, e.g.

http://stackoverflow.com/questions/9974/query-times-out-from-web-app-but-runs-fine-from-management-studio

It turns out the ADO.NET and Management studio connection/ user contexts differed and in my case, as per the link above, the explicit setting of the arithabort property brought the performance back to expected levels. Further, adding this in one sproc also fixed the issue with other reporting sprocs of the solution. Go figure.

 

About the author

I am Dr Christopher Sully (MCPD, MCSD) and I am a Cardiff, UK based IT Consultant/ Developer and have been involved in the industry since 1996 though I started programming considerably earlier than that. During the intervening period I've worked mainly on web application projects utilising Microsoft products and technologies: principally ASP.NET and SQL Server and working on all phases of the project lifecycle. If you might like to utilise some of the aforementioned experience I would strongly recommend that you contact me. I am also trying to improve my Welsh so am likely to blog about this as well as IT matters.

Month List