SQL Server Query Analyzer Runs Fast - Stored Procedure Runs Slow

Stored procedure runs slow in query analyzer but the same query pasted into query analyzer runs fast. Learn how to avoid parameter sniffing.

You may be a victim of sql server's so called "parameter sniffing"
Read the entire thread. I've seen this crop up on three
separate database servers for entirely different applications
this month.

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

CREATE PROCEDURE dbo.MyProcedure
(
@Param1 int
)
as

declare @MyParam1 int

set @MyParam1 = @Param1

select * from dbo.MyTable where colA = @MyParam1

http://groups.google.com/group/microsoft.public.sqlserver.programming/
browse_thread/thread/7821072440eefb0
/aec98a7789621cf9%23aec98a7789621cf9



Submission Date:  8/4/2006 8:59:11 AM
Submitted By:  Robbe Morris
My Home Page:  http://www.robbemorris.com