Stored Procedure vs Code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tolcis - 31 Aug 2007 16:32 GMT

Hi All,

I have a stored procedure that if I run it takes over 40 minutes to
complete. I use exec stored_procname and parameters.
However, if I run just the inside of the stored procedure (just the
code) with hard coded parameters it takes under a minute to
completed. Why would exec stored_proc name will take that long as
opposed to the actual code?

Thank you,
T.

Reply to this Message

Adam Machanic - 31 Aug 2007 16:45 GMT

It could be any number of issues. Google "parameter sniffing" and start
there...

Signature

Adam Machanic
SQL Server MVP - http://sqlblog.com

Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220

> Hi All,
>
[quoted text clipped - 7 lines]
> Thank you,
> T.

Reply to this Message

Russell Fields - 31 Aug 2007 16:55 GMT

T.

There are a couple of reasons.

1 - Variables internal to a stored procedure, as opposed to the parameters
passed to the procedure, figure poorly into the optimization. I think the
optimizer assumes a 25% hit rate on variables used in queries. This can
mean that the query will not use an index since it will opt for a table
scan. The parameters on the other hand cause a compilation on the first
call to the procedure when the disired plan is not cached. Which leads to
point 2...

2 - If a stored procedure has with different parameters a radically
different execution plan. E.g. One call will return 10,000 rows and another
call will return 1 row. Once the big plan is adopted, the following calls
will tend to stick with that plan. You can read about this in
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx under
Parameter Sniffing.

RLF

> Hi All,
>
[quoted text clipped - 7 lines]
> Thank you,
> T.

Reply to this Message

TheSQLGuru - 31 Aug 2007 20:06 GMT

Try a test: execute the sproc using the WITH RECOMPILE option. See if it
runs more quickly. This will identify a cached plan issue.

If that fixes the problem, then actually drop and recreate the sproc using
the WITH RECOMPILE option and then it will recompute the query plan
automatically for every execution.

Signature

TheSQLGuru
President
Indicium Resources, Inc.

> Hi All,
>
[quoted text clipped - 7 lines]
> Thank you,
> T.

Reply to this Message