FreeTDS Development Group

[Norman Palardy <norm AT kirais.com>]

> Unless your query winds up using different indexes depending on the
> parameters it's provided, there's no advantage to using "with recompile".

It does, radically different ones, depending on parameters which is the sole
reason we do use with recompile. I know Sybase mentions this behaviour and
why you'd use with recompile in their Performance and Tuning classes.

Without it we get a query plan which is optimal for some parameter values,
and definitely suboptimal for others.

This is one of the big drawbacks to parameterized stored procs. They don't
re-evaluate the query plan based on the parameters automatically unless you
use with recompile.

We use stored procs to hide the implementation of various tables, the
required joins, etc from but they don't necessarily buy us any performance
advantages.

On some of our less active tables a proc can be very fast because the plan
is only created once and simply reused over and over so that whole
compilation step is avoided.