I have a stored procedure in my SQL Server 2008 database on Winhost that runs in about 2 seconds when I execute it using SQL Server Management Studio (SSMS). But when the same procdedure is called by my ASP.NET 4.0 application, it takes 20 seconds or more to execute. Troubleshooting I've done: 1) The stored proc is called by my ASP.NET app using a TableAdapter. In VS2010 you can get a preview of the data by clicking on the TableAdapter in the designer view. The stored proc takes over 20 seconds to return when called this way too. 2) I can execute the stored proc directly via the Server Explorer in VS2010. It takes over 20 seconds to return here as well. 3) I have the identical database on another hosting service, MochaHost, when I execute the stored proc via VS2010 it runs in 2 seconds, just like it does via SSMS on MochaHost- so on MochaHost the sproc isn't slow when executed via VS2010 or ASP.NET. (But, I'm considering leaving MochaHost for Winhost since MochaHost's control panel is painfully slow.) Does anyone have any idea why a proc would run 10 times faster when executed via SSMS vs. VS2010 or an ASP.NET app?
SQL will self tune - as it runs more it learns more. If you've recently installed your database on Winhost - it could be using a very slow query plan. You cannot just test times, you have to view the query plan from each to make sure they're the same first.
Solved I found a solution, although I don't fully understand why it works. Here's where I found the solution: http://aspadvice.com/blogs/ssmith/a...es-Between-ADO.NET-and-Management-Studio.aspx I added the statement WITH RECOMPILE to my proc. The explanation is that for some stored procs it's better for SQL Server to not compute a new execution plan. But, what I don't understand is why this would be different when executing a stored proc from SSMS vs. VS2010 or ASP.NET. A little more info; I was calling the same sproc, on the same instance of the database on Winhost from SSMS, then from VS. So the only difference was the environmet from which the sproc was called, but the execution time from VS was 10X slower before I added the statement WITH RECOMPILE. FYI, here's anothe article with another solution to a similar problem: http://social.msdn.microsoft.com/Fo...s/thread/6e5353b9-4837-4022-a535-5408ed6f852a Ray, thanks for the offer to look at the database, but I don't want to publish the URL in a public forum. The database belongs to a client who is sensitive about IP. If you are still interested in looking at the database I can send the URL to you by e-mail. And I'd be happy to get your input!
