Queries Run Slower as Procedures

Sympton: Stored procedure runs slow in query analyzer but the same query pasted into query analyzer runs fast.

You may be a victim of sql server's so called "parameter sniffing" (also sometimes called parameter spoofing).

The short sample for how to get around this is to adjust your stored procedure to use local variables and assign the parameter values to the local variables. It is silly, but it does consistantly work.

CREATE PROCEDURE dbo.MyProcedure( @parm1 int )
AS   
   DECLARE @myvar1 int
   SET @myvar1 = @parm1

   SELECT *     
     FROM mytable    
    WHERE colA = @myvar1

Apparently, it has to do with the the optimization query plans for stored procedures.

If the procedure is complex enough and contains parameters that the optimizer 'decides' will impact how the query plan is generated, then the procedure takes longer to compile and run.

The method described above is one way to work around this. The above example fools the optimization engine into compiling the procedure and query plan once.

There may be cases where you will need to use the RECOMPILE directive in the stored procedure definition when the parameters actually DO impact query optimization (ie: dynamic where clauses).

More info and other ways to deal with 'Parameter Sniffing' can be found in links below:

http://www.eggheadcafe.com/tutorials/aspnet/353cb16c-3cde-44dd-a264-750c1ce4e423/sql-server-query-analyzer.aspx

http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx

http://www.sqlmag.com/Article/ArticleID/94369/sql_server_94369.html

http://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server/215861