473,543 Members | 2,477 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Temporary Table Concurrency

We have a few existing stored procedures which create a Global Temporary
Table (##), do some work on the table and then delete the table.

The issue we have is that if our Server application is using these is
running and then our Web application fires off, the Temp table is getting
deleted.

My thought was to change the Global Temporary Table to a local temporary
table (# instead of ##) to minimize the amount of change to these
procedures. My question though is in reference to the ASP.NET Web
application and whether the issue with the global table would still be an
issue with the local table.

I wasn't sure how connection pooling would play into this. I know that a
local temporary table is created for each connection and can only be
reference by that connection but i was wondering if in the ASP.NET
application is that if i call the procedure by two separate users in two
separate sessions if it would use the same connection and thus share the
same local temporary table.
Nov 18 '05 #1
5 2990

"Adam W. Saxton" <as*****@intera ctiontech.comNO SPAM> wrote in message
news:eV******** ******@TK2MSFTN GP09.phx.gbl...
We have a few existing stored procedures which create a Global Temporary
Table (##), do some work on the table and then delete the table.

The issue we have is that if our Server application is using these is
running and then our Web application fires off, the Temp table is getting
deleted.

My thought was to change the Global Temporary Table to a local temporary
table (# instead of ##) to minimize the amount of change to these
procedures. My question though is in reference to the ASP.NET Web
application and whether the issue with the global table would still be an
issue with the local table.

I wasn't sure how connection pooling would play into this. I know that a
local temporary table is created for each connection and can only be
reference by that connection but i was wondering if in the ASP.NET
application is that if i call the procedure by two separate users in two
separate sessions if it would use the same connection and thus share the
same local temporary table.


Each user will have exclusive access to the session so long as they hold the
Connection open.

So no user will see another's data, but you might see what another user left
in the temporary table.

So if the clients did

CREATE TABLE #FOO(I INT)
INSERT INTO #FOO(I) VALUES (1)

Then the second client would get an error trying to create #FOO because it
already existed.
But if each client runs

if not object_id('temp db..#FOO') is null DROP TABLE #FOO
CREATE TABLE #FOO(I INT)
INSERT INTO #FOO(I) VALUES (1)

It's ok. You just have to account for other users leaving the temporary
table there.

David
Nov 18 '05 #2
That's what worries me.

If User A creates #FOO and then User B comes in before User A is done would
User B see some data that User A placed in #Foo and also if User A Drops the
table before User B is done, would User B get an error saying that #Foo does
not exists which i would think that would be true, and that is what was
happening with the Global table.
"David Browne" <davidbaxterbro wne no potted me**@hotmail.co m> wrote in
message news:Ov******** ******@TK2MSFTN GP10.phx.gbl...

"Adam W. Saxton" <as*****@intera ctiontech.comNO SPAM> wrote in message
news:eV******** ******@TK2MSFTN GP09.phx.gbl...
We have a few existing stored procedures which create a Global Temporary
Table (##), do some work on the table and then delete the table.

The issue we have is that if our Server application is using these is
running and then our Web application fires off, the Temp table is getting deleted.

My thought was to change the Global Temporary Table to a local temporary
table (# instead of ##) to minimize the amount of change to these
procedures. My question though is in reference to the ASP.NET Web
application and whether the issue with the global table would still be an issue with the local table.

I wasn't sure how connection pooling would play into this. I know that a local temporary table is created for each connection and can only be
reference by that connection but i was wondering if in the ASP.NET
application is that if i call the procedure by two separate users in two
separate sessions if it would use the same connection and thus share the
same local temporary table.

Each user will have exclusive access to the session so long as they hold

the Connection open.

So no user will see another's data, but you might see what another user left in the temporary table.

So if the clients did

CREATE TABLE #FOO(I INT)
INSERT INTO #FOO(I) VALUES (1)

Then the second client would get an error trying to create #FOO because it
already existed.
But if each client runs

if not object_id('temp db..#FOO') is null DROP TABLE #FOO
CREATE TABLE #FOO(I INT)
INSERT INTO #FOO(I) VALUES (1)

It's ok. You just have to account for other users leaving the temporary
table there.

David

Nov 18 '05 #3

"Adam W. Saxton" <as*****@intera ctiontech.comNO SPAM> wrote in message
news:eO******** ******@tk2msftn gp13.phx.gbl...
That's what worries me.

If User A creates #FOO and then User B comes in before User A is done would User B see some data that User A placed in #Foo and also if User A Drops the table before User B is done, would User B get an error saying that #Foo does not exists
No. That would not happen. There will be a seperate #FOO for each session.
which i would think that would be true, and that is what was
happening with the Global table.


That is the difference between regular temporary tables (#) and global
temporary tables (##).

David
Nov 18 '05 #4
this is not correct.

#temp tables are tied to a spid (sqlconnection) . they live until the spid
dies or the spid drops the table.

if you use connection pooling and don't create and drop the #temp table in
one batch (execute), then you can have the problem of two users accessing
the same #temp table (or one deleteing before the other is done).

sqlserver currently has no knowledge of connectiion pooling, future
versions, may impliment a connection reset, so cleanup can be done when the
connection is returned to the pool. this would mean the #temp table would
disappear as soon a conection went into the pool, even on the same page
request.

you can also safely create the #temp in a stored proc, becuase the sp will
delete it at exit, so its never shared.
-- bruce (sqlwork.com)
"David Browne" <davidbaxterbro wne no potted me**@hotmail.co m> wrote in
message news:#c******** ******@tk2msftn gp13.phx.gbl...

"Adam W. Saxton" <as*****@intera ctiontech.comNO SPAM> wrote in message
news:eO******** ******@tk2msftn gp13.phx.gbl...
That's what worries me.

If User A creates #FOO and then User B comes in before User A is done would
User B see some data that User A placed in #Foo and also if User A Drops

the
table before User B is done, would User B get an error saying that #Foo

does
not exists


No. That would not happen. There will be a seperate #FOO for each

session.
which i would think that would be true, and that is what was
happening with the Global table.


That is the difference between regular temporary tables (#) and global
temporary tables (##).

David

Nov 18 '05 #5

"bruce barker" <no***********@ safeco.com> wrote in message
news:et******** ******@TK2MSFTN GP10.phx.gbl...
this is not correct.

#temp tables are tied to a spid (sqlconnection) . they live until the spid
dies or the spid drops the table.

if you use connection pooling and don't create and drop the #temp table in
one batch (execute), then you can have the problem of two users accessing
the same #temp table (or one deleteing before the other is done).


Not quite. You are guaranteed to be accessing the same temp table across
batches, so long as you are using the same SqlConnection object. When you
..Close the SqlConnection, the connection will return to the pool, and a
later user can retrieve that connection and "see" the same temp table. But
as I explained, you just have to account for the possibility that the temp
table may already exist in the session.

So it is safe to:
1. Open a "new" connection.
2. Drop the temp table (if it exists)
3. Create the temp table
4. Use the temp table in any number of batches
4a. (best practice) drop the temp table.
5. Close the connection
David

Nov 18 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
13675
by: Corrine | last post by:
Hi, I am creating a global temporary table that is session-specific. I insert a BLOB into this table, and then select the BLOB from this table into a ResultSet. The ResultSet sees this BLOB object, and I am able to get the binary input stream from this blob. However, when I invoke InputStream.read(byte) on this input stream, I get the...
5
33610
by: Jim Garrison | last post by:
Scenario: 1) Create a GLOBAL TEMPORARY table and populate it with one (1) row. 2) Join that table to another with about 1 million rows. The join condition selects a few hundred rows. Performance: 4 seconds, the system is doing a full-table scan of the second table, and the Explain Plan output
2
4536
by: Calista | last post by:
I have a stored procedure that creates a temporary table. If two people (using the same account) execute the same stored procedure at the same time, will they overwrite / access the temporary table ? Calista.
4
49426
by: gonzal | last post by:
Hi Dose any body know why a temporary table gets deleted after querying it the first time (using SELECT INTO)? When I run the code bellow I'm getting an error message when open the temp table for the second time. Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E37) Invalid object name '#testtable'.
11
16275
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the equivalent to the SQL 'with' using TSQL? If there is not one, what is the TSQL solution to creating a temporary table that is associated with an SQL...
1
6119
by: Mike L. Bell | last post by:
Query: update table1 t1 set end_time = ( select end_time from table2 t2 where t2.key1 = t1.key1 and t2.key2 = t1.key2 ) where exists
7
3263
by: Larry | last post by:
Hi, I have unbelievable problems just to save a record! I make an input to a record in a subform, which has a temporary table as its recordsource. When I am done, and want to save the record, I want to bring the data from the temporary table over to the regular table. But the data isn't in the temporary table yet, it is still
22
18768
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4. The system is client/server, multiusers based. The MDBs are using record locking. Here is part of the code: Dim wkSpace As Workspace, db As...
3
3262
by: Chris | last post by:
All I am cross-posting, as I'm not sure if this is an issue for the data layer or the application layer. If this is unacceptable, can someone let me know so that I don't do this in future. I'm using ASP .NET, framework version 1 and SQL Server 2000. I have an ASPX page with a form for customers to register. When they click on the...
0
7399
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
1
7332
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7677
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5880
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5258
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4884
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3386
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1809
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
626
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.