SQL Server runs very slow when called from .NET application

Question

I have SQL call to stored procedure in my ASP.NET application using nHibernate:

GetNamedQuery("MyProc")
.SetString("param1", value1)
.SetString("param2", value2)
...

SQL Server 2005 used here. It runs well on our testing environment, this call takes about 2 seconds to complete. But when we move it to new server it starts to take very long time for this and I got timeout exception in my application.

However, I catch calls in SQL Server Profiler, find that this one runs for 30 sec. But when I copied the same query and just run it on server it completes in 2 sec.

So the question is what can affect working queries from .NET application?

Is it caching the execution plan, and if so, does it speed up after it gets compiled/cached? - would be informative to see all the calling code method and the stored procedure code. — Mark Schultheiss, Aug 14 '13 at 15:28
Have you investigated any suspect of an inefficient nHibernate method/usage here? — Mark Schultheiss, Aug 14 '13 at 15:40
Does the other server have more data or more traffic than the testing environment? — Daniel Schilling, Aug 15 '13 at 22:35

score 3 · Accepted Answer · answered Aug 14 '13 at 15:32

Hands down, the most complete solution(s) to this type of problem is found here, one of the best written pieces on this. IF you're passing parameters into a stored procedure from an external application, one quick hack to this that works 80% of the time is to localize the parameters in the procedure:

CREATE PROCEDURE sp_Test
@VarOne INT, @VarTwo INT
AS
BEGIN

DECLARE @VOne INT, @VTwo INT
SET @VOne = @VarOne
SET @VTwo = @VarTwo

/* Rest of code only uses @VOne and @VTwo for parameters */

END

This is assumes, though, that you have parameters in your application that the stored procedure needs (which it looks like from the brief snippet of code you've posted). Otherwise, the provided link also delineates some other oversights and I highly recommend it to anyone troubleshooting performance from an external application.

Thank you, updating procedure the way you described doesn't work for me. Will investigate article you provided. — Roman, Aug 14 '13 at 17:50

SQL Server runs very slow when called from .NET application

1 Answers1