Sproc execution is much slower than ad-hoc SQL equivalent

I have a sproc that was taking much longer than I expected.

I pulled out the SQL and ran it with only DECLARED variables for the parameters. It worked almost instantaneously (compared to the reliable 8 seconds with sproc). This is the same SQL on the same machine, returning the same data.

How can I identify and fix what is causing the slow sproc?

It's on SQL Server Express.

Many thanks.

+2


source to share


3 answers


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



+1


source


In the stored procedure definition, check ANSI_NULLS

. Make sure it is on. ( SET ANSI_NULLS ON

)



It is enabled by default in the query window in SSMS. We found an issue that ANSI_NULLS

was explicitly disabled and it was slowing down sproc dramatically.

+1


source


Is the application executing a stored procedure, or are you executing the stored procedure through Query Analyzer?

0


source







All Articles