TheSQLGuru
3/31/2007 1:35:00 AM
Look for some way to avoid caching the plan maybe? I am not an ado.net guru
though. Also, are you using sp_executesql inside the sproc to run the
dynamic sql? Try both ways, with that and with direct exec ().
--
TheSQLGuru
President
Indicium Resources, Inc.
"DCPeterson" <sgtp_usmc@hotmail.com> wrote in message
news:OeOAPNvcHHA.4836@TK2MSFTNGP03.phx.gbl...
> We have a stored procedure that actually generates dynamic SQL and
> executes it. (It's a search function basically, so we don't know ahead of
> time which fields the user will want to search from...)
>
> Anyway, the most common search critera are @StartDate and @EndDate. When
> the stored procedure is called from our .net application I can see that
> the execution plan for the query is the same whether the interval between
> @StartDate and @EndDate is 1 day or 1 year. This means that the 1 year
> iteration is very slow because it's using a bad execution plan.
>
> Now for the interesting part. When the same stored procedure is called
> with the same two sets of parameters from Management Studio, SQL always
> chooses different execution plans and both iterations run very
> effeciently.
>
> Any ideas why SSMS behaves so differently than ADO.Net? Are there
> connection properties that should be set to make ADO.Net behave the same?
> Any help is much appreciated.
>