Sql Stored Procedure Execution Time Difference

I have strange issue in my win-form application where I am calling a stored procedure and it takes about 6 seconds to execute. (This stored procedure accepts several parameters inc

Solution 1:

The issue with difference between calling SP directly and from .NET code, maybe due to parameter sniffing. SQL Server maybe caching execution plan that is not optimal for the parameters you're passing from code.

To avoid this try adding WITH RECOMPILE to your SP definition, e.g.

CREATEPROCEDURE MySP (
    ... parameters...
) WITH RECOMPILE

ASBEGIN
   ...

Solution 2:

It could be an issue if your stored procedure expects say nvarchar and you use a varchar. SQL server will accept the parameters but will be forced to do a conversion. Do you have more specifics?