SQL Server 2008, stored procedure run slow from code but fast in SSMS

Question

Hello,

I have a stored procedure that when I ran it from within the SSMS, it runs fast about 20 sec. but when I execute this same stored procedure from my .net application it takes about 50-70 secs!! Why is that happening?

Thank you.

Answer 1

Several reasons are possible.

First likely you are sending the data over a network, could be that it is maxed out.

Could also be that you have more users in the second scenario and so are getting locking issues.

And stored procedures that take 20 seconds when run from SSMS on a dev box (especially one which does not include the full data set of prod) are unlikely to perform well in porduction with full data sets and many more users. 20 seconds it way too long to a a query in development.

Likely your sp desperately needs to be tuned.