sproc execution far slower than SQL ad-hoc query equivalent

views:

231

answers:

+2 Q:

sproc execution far slower than SQL ad-hoc query equivalent

I have a sproc that was taking far more time than I expected.

I pulled out the SQL and ran it with just DECLARED variables for the parameters. It ran nearly instantaneously (versus a reliable 8 seconds with the sproc). This is the same SQL on same machine, returning the same data.

How can I figure out and fix what is causing the sproc to be slow?

This is on SQL Server Express.

Thanks much.

+1 A:

This is parameter sniffing. Just change your procedure to do the same thing: copy all of you parameters into local variables and than have the body of your procedure use the loal variables instead.

RBarryYoung 2009-08-21 04:12:41

Thanks RBarryYoung and rexem! Making this change made the sproc perform as expected.

David Montgomery 2009-08-21 05:44:16

Is an app running the stored procedure, or are you running the stored procedure via Query Analyzer?

mrdenny 2009-08-21 04:16:39

+1 A:

In the definition of the stored procedure, check for ANSI_NULLS. Make sure that this is turned on. (SET ANSI_NULLS ON)

By default in the Query Window in SSMS it is turned on. We found a problem where ANSI_NULLS was explicitly set to off and it drastically slowed down the sproc.

2009-08-21 06:04:11

IIRC this comes from indexes that the sproc wants to use being built with a different setting (and thus incompatible)

RBarryYoung 2009-08-22 04:59:19

SETing certain options (including ANSI_NULLS) within a stored proc can cause SP recompilations - most certainly the cause of your performance issues. See http://support.microsoft.com/kb/243586 for more info

Kev Riley 2009-08-23 09:35:09

ansaurus

tags:

views:

answers:

sproc execution far slower than SQL ad-hoc query equivalent

related questions