SQL Server Query Analyzer Runs Fast - Stored Procedure Runs Slow |
| Printer Friendly Version |
|
|
| 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 |
|
| My Biography |
| Robbe has been a Microsoft MVP for C# since 2004. He is also the co-founder of EggHeadCafe. Robbe has extensive experience with web technologies, .NET, C#, CTI based applications, system administration, .NET Compact Framework, and data modeling. In his spare time, he blogs from time to time at http://robbemorris.blogspot.com |