ASE Database forum - Re: Scope of Temp tables

Author

Re: Scope of Temp tables

James Cook

2005-04-29, 11:26 am

"Michael Peppler" <mpeppler@peppler.org> wrote in message
news:pan.2004.12.08.18.35.50.455163@peppler.org...
> On Wed, 08 Dec 2004 10:11:12 -0800, Citi wrote:
>
>
> The only way to do this is to create the temp table before the proc is
> called. Alternatively, a temp table is available in procs that are called
> *from* the proc that created it.

How do you pre-create the temporary table?

I have a stored proc that creates the temporary table I need. I plan to
execute this stored proc as soon as I create a connection with the database.

The stored proc that uses the temporary table contains the "insert into
#results select...", however I can't even get Sybase to compile the stored
proc. It chokes and reports:

Server Message: Number 208, Severity 16
Server 'SYBASEN20', Procedure 'ELLIS_Get_Proj_Muni
cipalities', Line 37:
#results not found. Specify owner.objectname or use sp_help to check whether
the object exists (sp_help may produce lots of output).

Unless I put the "create table #results..." in the stored proc, I can't even
get the DDL to compile.

Using Sybase 12.5.0.3 on AIX, SQL Advantage to execute DDL.

Carl Kayser

2005-04-29, 1:26 pm

create table #temp ....
go

create proc1 (subroutine which uses #temp)
go

drop table #temp
go

create proc2 (driver)
create table #temp (EXACT definition as above create table statement)
....
use #temp, call proc2, etc.
go

"James Cook" <noone> wrote in message news:42725e0d$1@foru
ms-1-dub...
> "Michael Peppler" <mpeppler@peppler.org> wrote in message
> news:pan.2004.12.08.18.35.50.455163@peppler.org...
called[color=darkred
]
>
> How do you pre-create the temporary table?
>
> I have a stored proc that creates the temporary table I need. I plan to
> execute this stored proc as soon as I create a connection with the
database.
>
> The stored proc that uses the temporary table contains the "insert into
> #results select...", however I can't even get Sybase to compile the stored
> proc. It chokes and reports:
>
> Server Message: Number 208, Severity 16
> Server 'SYBASEN20', Procedure 'ELLIS_Get_Proj_Muni
cipalities', Line 37:
> #results not found. Specify owner.objectname or use sp_help to check
whether
> the object exists (sp_help may produce lots of output).
>
> Unless I put the "create table #results..." in the stored proc, I can't
even
> get the DDL to compile.
>
> Using Sybase 12.5.0.3 on AIX, SQL Advantage to execute DDL.
>
>

James Cook

2005-05-02, 11:23 am

Thanks for the example Kayser.

I'm working inside of an application context, and when I execute Stored
Procs that contain DDL we experience unwanted blocking in TEMPDB. It
currently looks like this:

initializeConnection
Pool();

tx.begin();
conn = pool.getConnection();
....execute stored proc... (sp creates and drops temp table #results)
pool. returnConnection(con
n);
tx.end();

I thought it would be a good idea to eliminate the creation and drop of the
temp table inside the stored proc. This should remove the time it takes to
create and drop the temp table, and reduce the blobking in TEMPDB. After
reading the Sybase docs, I learned that a temporary table exists for the
curation of a "session". I'm a bit nebulous about what Sybase considers a
session, but I was hoping it was the same as the lifecycle of a connection.
After all, your example seems to imply that a temp table hangs around
outside the scope of a single transaction. So I tried,

initializeConnection
Pool();
-- create #results using each connection in the pool.

tx.begin();
conn = pool.getConnection();
....execute stored proc... (sp uses temp table #results)
pool. returnConnection(con
n);
tx.end();

However when I try this approach, The stored proc cannot find the temporary
table. It appears to have already been removed up by Sybase. I can only
assume by this experiement that a temporary table does not have the same
lifecycle as a database connection, although it appears to have a lifecycle
beyond a single database transaction. I'm not aware of any lifecycle between
these two. Are my assumptions flawed?

"Carl Kayser" <kayser_c@bls.gov> wrote in message
news:42726e82$1@foru
ms-1-dub...
>
> create table #temp ....
> go
>
> create proc1 (subroutine which uses #temp)
> go
>
> drop table #temp
> go
>
> create proc2 (driver)
> create table #temp (EXACT definition as above create table statement)
> ....
> use #temp, call proc2, etc.
> go
>
>
>
> "James Cook" <noone> wrote in message news:42725e0d$1@foru
ms-1-dub...
> called
> database.
> whether
> even
>
>

James Cook

2005-05-02, 1:24 pm

Bad form to answer my own question, but I was making an obvious mistake. I
created the temporary tables when a database connection is initially
established, however I was invoking a stored procedure to create several
temporary tables. A temporary table created in the scope of a stored proc
will be automatically dropped (by Sybase) at the conclusion of the stored
proc.

By changing the creation of the temp tables into a series of batch SQL
statements, they are created one time; at connection initialization. And
these temporary tables continue to be available as long as the connection is
alive. Now the stored procs no longer have to be created (or dropped) by the
stored procs that use them and the tempdb blocking has been eliminated.

The only drawback is that all of the temp tables must be created when the
connection is initialized. This means it is possible for temp tables to be
created unnecessarily, however because we use long lived connections in a
pooling mechanism, nearly all connections will need these temp tables at one
time or another over the span of a day or week or longer.

"James Cook" <noone> wrote in message news:4276585e$1@foru
ms-2-dub...
> Thanks for the example Kayser.
>
> I'm working inside of an application context, and when I execute Stored
> Procs that contain DDL we experience unwanted blocking in TEMPDB. It
> currently looks like this:
>
> initializeConnection
Pool();
>
> tx.begin();
> conn = pool.getConnection();
> ...execute stored proc... (sp creates and drops temp table #results)
> pool. returnConnection(con
n);
> tx.end();
>
> I thought it would be a good idea to eliminate the creation and drop of
> the temp table inside the stored proc. This should remove the time it
> takes to create and drop the temp table, and reduce the blobking in
> TEMPDB. After reading the Sybase docs, I learned that a temporary table
> exists for the curation of a "session". I'm a bit nebulous about what
> Sybase considers a session, but I was hoping it was the same as the
> lifecycle of a connection. After all, your example seems to imply that a
> temp table hangs around outside the scope of a single transaction. So I
> tried,
>
> initializeConnection
Pool();
> -- create #results using each connection in the pool.
>
> tx.begin();
> conn = pool.getConnection();
> ...execute stored proc... (sp uses temp table #results)
> pool. returnConnection(con
n);
> tx.end();
>
> However when I try this approach, The stored proc cannot find the
> temporary table. It appears to have already been removed up by Sybase. I
> can only assume by this experiement that a temporary table does not have
> the same lifecycle as a database connection, although it appears to have a
> lifecycle beyond a single database transaction. I'm not aware of any
> lifecycle between these two. Are my assumptions flawed?
>
>
> "Carl Kayser" <kayser_c@bls.gov> wrote in message
> news:42726e82$1@foru
ms-1-dub...
>
>

Sponsored Links