Hi Patrick,

It certainly looks like you are getting different query plans (you can verify this by selecting "Show Actual Execution Plan" in the Query menu or press ctrl+K before you execute the query).

It might be that the cached plan for this procedure was compiled with "atypical" parameters (or vice versa) so you are getting a plan that is not optimal for the parameters you are passing whereas your select is not using a cached plan (so there is no "Parameter Sniffing" going on).

You can read more about Parameter Sniffing in Bart Duncan's blog: http://blogs.msdn.com/bartd/archive/2006/07/27/wide-vs-narrow-plans.aspx.

You can easily verify this by doing the following (do this off-hours, it throws out all cached plans from the procedure cache):

DBCC FREEPROCCACHE -- empty procedure cache

go

SET STATISTICS TIME ON

go

run your sproc here with SomeParams -- will put a new plan in cache

-- note the output from SET STATISTICS IO ON

run your sproc here with TheSameParamsAsAbove -- will use the newly cached plan

compare the result from SET STATISTICS IO ON (http://support.microsoft.com/kb/65181 "INFO: Interpretation of SET STATISTICS TIME ON Results" explains the output).

If this is what is happening, you have a few options. One you have already discovered yourself, some others are described in the above blog.

If it is not this, it could be that compilation time is higher (SET STATISTICS IO will tell you that as well), there is a slight overhead for stored procedures but usually not at all to the degree you are talking about.

Oh, and run a UPDATE STATISTICS on the tables/indexes involved (or sp_updatestats on the entire database) before you start. You should probably do this off-hours as well if your tables/indexes are large.

HTH!

/Elisabeth

.