Any way to force a recompile on a statement within a User Defined Function?

Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> > Consider the following:
> >
> > TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> > There is a clustered index on (c1, c3), nonclustered index on (c2)
> >
> > The UDF contains a SELECT statement of the form:
> > select c1 from TableA where c2 = @.InputParm
> >
> > TableA contains about 300000 rows and there are some values for column
c2
> > that have a cardinality of over 30000. So obviously if this value gets
> > passed into the UDF, a Clustered Index Scan is probably best. On the
> other
> > hand if a parm gets passed for a value that has a few corresponding rows
> for
> > c2 then it is best that the nonclustered index on c2 be used.
> >
> > The problem is that when the UDF is first invoked with a parm that has a
> > high number of corresponding rows for c2, the execution plan uses a
> > clustered index SCAN and all subsuquent calls use this same execution
> plan.
> > Ideally I would like to have this UDF defined to recompile each call so
> that
> > I get the best execution plan for each call.
> >
> > If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> > (doesn't appear to be valid for functions), or I would use dynamic SQL
> > (since Functions cannot access temp tables I cannot use this approach
> > either).
> >
> > Any ideas would be appreciated.
> >
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>