"with recompile" relevant in this case?

[Q Vincent Yin]

Suppose a stored proc references a #temp table. The caller creates the temp
table before calling the proc, and drops it after calling the proc. Then
it's guaranteed that the proc will do re-resolution every time it is called.

In that case, for efficiency purpose, would it be helpful to "create proc
with recompile"? My guess is that it's irrelevant since a re-resolution
automatically implies a recompilation. But maybe someone has a more
in-depth view?

--

Q Vincent Yin | Repeat
um...@mctrf.mb.ca | delete(next->bug);
| Until 0 = 1;

[Anne Mahoney]

jgl...@world.std.com (Jerry J Glenn) wrote:
> Interestingly enough, procedures that create their own temp tables
>within seem to have their "query tree" replaced rather than appended to
>during the re-resolve phase. Which is good for log space and
>sysprocedures table lock contention, but still not much of a savings in
>performance.

Jerry,

How did you come to find this?
We are having problems with stored procedures using temporary
tables which regularly get 703 errors (SQL Server 4.9.2).
I thought the only way to get the query tree rebuilt
rather than appended to was to drop and re-create the
stored procedure.
We are using a 'create table #table' statement within the stored
procedure to create the temporary table. Do you know of
a better way? Perhaps 'select into #table' is better?

- Anne Mahoney
State Street Bank
alm%i...@clipper.ssb.com

[Jerry J Glenn]

I've found that re-resolution of a procedure or trigger is an act where
the "query tree" is appended with the new object ID(s) in the
sysprocedures table. Re-compiling a procedure, on the other hand, is an
act on the "query plan", which is the "query tree" read from disk and
then optimized. The "with recompile" will have no effect on either
execution time or log space needed to record the new "query tree" if a
procedure is using temp tables created outside of the procedure since the
re-resolve will cause a re-compile every time; unless that procedure
happens to still be in procedure cache, not being used by anyone else,
and the temp table has not been dropped/recreated in the meantime.

Interestingly enough, procedures that create their own temp tables
within seem to have their "query tree" replaced rather than appended to
during the re-resolve phase. Which is good for log space and
sysprocedures table lock contention, but still not much of a savings in
performance.

I'm not sure I understand what Sybase has in mind for using temp
tables. We have pretty much discontinued using temp tables in our
applications.
-Jerry