Connection Pooling in ASP

When connections are pooled in ASP to an SQL backend, is the connection to
the SQL server considered to be one connection?

Here's why I ask: The asp page I am working on will need to build a
temporary table in the SQL database. If two people try to retrieve the same
page at the same time, will they both be accessing the same temp table? (Not
what I want).

Here's the whole issue. I want to create a table of requested dates and
times for a scheduling program and compare that using a join to the already
existing schedule. The previous developers wrote the aSP page to loop
through each desired date and then check to see if there was a conflict with
that date. I prefer to make a table of all the requested dates and then join
that to the existing scheduled dates and check for matches.

Thanks,
John

Re: Connection Pooling in ASP

John Beschler wrote:
> When connections are pooled in ASP to an SQL backend, is the
> connection to the SQL server considered to be one connection?
>
> Here's why I ask: The asp page I am working on will need to build a
> temporary table in the SQL database. If two people try to retrieve
> the same page at the same time, will they both be accessing the same
> temp table? (Not what I want).
>
> Here's the whole issue. I want to create a table of requested dates
> and times for a scheduling program and compare that using a join to
> the already existing schedule. The previous developers wrote the aSP
> page to loop through each desired date and then check to see if there
> was a conflict with that date. I prefer to make a table of all the
> requested dates and then join that to the existing scheduled dates
> and check for matches.
>
> Thanks,
> John

Here is some info about connection/session/resource pooling (the name varies
depending on the technology ODBC vs OLE DB):
http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2 .asp

A pooled connection is a single connection. However, two users cannot be
using the same connection at the same time. So in your scenario, the users
would have to be using separate temp tables.

There is a gotcha: if you do not drop the table when you are finished with
it, it can persist for several users utilizing the same connection
consecutively:

The first user opens a connection - since no connections exist in the pool,
a new one is spawned.
The temp table is created for the user and used
The user "closes" and destroys the connection, releasing it to the pool
While the connection is in the pool, it really remains open for 60 seconds.
User2 opens a connection less than 60 seconds after the first connection was
released to the pool, so he gets the same connection that was used by User1
Since the temp table was not dropped, it still exists. If your code does not
drop and recreate the table at this point, User2 will see User1's data in
the temp table

That's why dropping temp tables as soon as they are no longer needed is
considered good programming practice. Like this:

The first user opens a connection - since no connections exist in the pool,
a new one is spawned.
The temp table is created for the user and used.
The temp table is dropped
The user "closes" and destroys the connection, releasing it to the pool
While the connection is in the pool, it really remains open for 60 seconds.
User2 opens a connection less than 60 seconds after the first connection was
released to the pool, so he gets the same connection that was used by User1
The temp table is created for the user and used.
The temp table is dropped
etc.


HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Connection Pooling in ASP

Bob,

That answers my question perfectly.

Thanks very much!

"Bob Barrows [MVP]" wrote:

> John Beschler wrote:
> > When connections are pooled in ASP to an SQL backend, is the
> > connection to the SQL server considered to be one connection?
> >
> > Here's why I ask: The asp page I am working on will need to build a
> > temporary table in the SQL database. If two people try to retrieve
> > the same page at the same time, will they both be accessing the same
> > temp table? (Not what I want).
> >
> > Here's the whole issue. I want to create a table of requested dates
> > and times for a scheduling program and compare that using a join to
> > the already existing schedule. The previous developers wrote the aSP
> > page to loop through each desired date and then check to see if there
> > was a conflict with that date. I prefer to make a table of all the
> > requested dates and then join that to the existing scheduled dates
> > and check for matches.
> >
> > Thanks,
> > John
>
> Here is some info about connection/session/resource pooling (the name varies
> depending on the technology ODBC vs OLE DB):
> http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2 .asp
>
> A pooled connection is a single connection. However, two users cannot be
> using the same connection at the same time. So in your scenario, the users
> would have to be using separate temp tables.
>
> There is a gotcha: if you do not drop the table when you are finished with
> it, it can persist for several users utilizing the same connection
> consecutively:
>
> The first user opens a connection - since no connections exist in the pool,
> a new one is spawned.
> The temp table is created for the user and used
> The user "closes" and destroys the connection, releasing it to the pool
> While the connection is in the pool, it really remains open for 60 seconds.
> User2 opens a connection less than 60 seconds after the first connection was
> released to the pool, so he gets the same connection that was used by User1
> Since the temp table was not dropped, it still exists. If your code does not
> drop and recreate the table at this point, User2 will see User1's data in
> the temp table
>
> That's why dropping temp tables as soon as they are no longer needed is
> considered good programming practice. Like this:
>
> The first user opens a connection - since no connections exist in the pool,
> a new one is spawned.
> The temp table is created for the user and used.
> The temp table is dropped
> The user "closes" and destroys the connection, releasing it to the pool
> While the connection is in the pool, it really remains open for 60 seconds.
> User2 opens a connection less than 60 seconds after the first connection was
> released to the pool, so he gets the same connection that was used by User1
> The temp table is created for the user and used.
> The temp table is dropped
> etc.
>
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Connection Pooling in ASP

Do you mind one more question? This is really for SQL, but I thought you
moght know the answer. Is there a way to test for the existence of a
temporary table in SQL?

Thanks,
John


"Bob Barrows [MVP]" wrote:

> John Beschler wrote:
> > When connections are pooled in ASP to an SQL backend, is the
> > connection to the SQL server considered to be one connection?
> >
> > Here's why I ask: The asp page I am working on will need to build a
> > temporary table in the SQL database. If two people try to retrieve
> > the same page at the same time, will they both be accessing the same
> > temp table? (Not what I want).
> >
> > Here's the whole issue. I want to create a table of requested dates
> > and times for a scheduling program and compare that using a join to
> > the already existing schedule. The previous developers wrote the aSP
> > page to loop through each desired date and then check to see if there
> > was a conflict with that date. I prefer to make a table of all the
> > requested dates and then join that to the existing scheduled dates
> > and check for matches.
> >
> > Thanks,
> > John
>
> Here is some info about connection/session/resource pooling (the name varies
> depending on the technology ODBC vs OLE DB):
> http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2 .asp
>
> A pooled connection is a single connection. However, two users cannot be
> using the same connection at the same time. So in your scenario, the users
> would have to be using separate temp tables.
>
> There is a gotcha: if you do not drop the table when you are finished with
> it, it can persist for several users utilizing the same connection
> consecutively:
>
> The first user opens a connection - since no connections exist in the pool,
> a new one is spawned.
> The temp table is created for the user and used
> The user "closes" and destroys the connection, releasing it to the pool
> While the connection is in the pool, it really remains open for 60 seconds.
> User2 opens a connection less than 60 seconds after the first connection was
> released to the pool, so he gets the same connection that was used by User1
> Since the temp table was not dropped, it still exists. If your code does not
> drop and recreate the table at this point, User2 will see User1's data in
> the temp table
>
> That's why dropping temp tables as soon as they are no longer needed is
> considered good programming practice. Like this:
>
> The first user opens a connection - since no connections exist in the pool,
> a new one is spawned.
> The temp table is created for the user and used.
> The temp table is dropped
> The user "closes" and destroys the connection, releasing it to the pool
> While the connection is in the pool, it really remains open for 60 seconds.
> User2 opens a connection less than 60 seconds after the first connection was
> released to the pool, so he gets the same connection that was used by User1
> The temp table is created for the user and used.
> The temp table is dropped
> etc.
>
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Connection Pooling in ASP

Try this script in QA (assumes you have northwind database installed):

if object_id('tempdb..#tmp') is not null
begin
print '#tmp exists - dropping it now'
drop table #tmp
print '#tmp does not exist - creating it now'
select * into #tmp from northwind..region
end
else
begin
print '#tmp does not exist - creating it now'
select * into #tmp from northwind..region
end
if object_id('tempdb..#tmp') is not null
begin
print '#tmp exists - dropping it now'
drop table #tmp
end
else
print '#tmp does not exist'


Bob Barrows

John Beschler wrote:
> Do you mind one more question? This is really for SQL, but I thought
> you moght know the answer. Is there a way to test for the existence
> of a temporary table in SQL?
>
> Thanks,
> John
>
>
> "Bob Barrows [MVP]" wrote:
>
>> John Beschler wrote:
>>> When connections are pooled in ASP to an SQL backend, is the
>>> connection to the SQL server considered to be one connection?
>>>
>>> Here's why I ask: The asp page I am working on will need to build a
>>> temporary table in the SQL database. If two people try to retrieve
>>> the same page at the same time, will they both be accessing the same
>>> temp table? (Not what I want).
>>>
>>> Here's the whole issue. I want to create a table of requested dates
>>> and times for a scheduling program and compare that using a join to
>>> the already existing schedule. The previous developers wrote the
>>> aSP page to loop through each desired date and then check to see if
>>> there was a conflict with that date. I prefer to make a table of
>>> all the requested dates and then join that to the existing
>>> scheduled dates and check for matches.
>>>
>>> Thanks,
>>> John
>>
>> Here is some info about connection/session/resource pooling (the
>> name varies depending on the technology ODBC vs OLE DB):
>> http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2 .asp
>>
>> A pooled connection is a single connection. However, two users
>> cannot be using the same connection at the same time. So in your
>> scenario, the users would have to be using separate temp tables.
>>
>> There is a gotcha: if you do not drop the table when you are
>> finished with it, it can persist for several users utilizing the
>> same connection consecutively:
>>
>> The first user opens a connection - since no connections exist in
>> the pool, a new one is spawned.
>> The temp table is created for the user and used
>> The user "closes" and destroys the connection, releasing it to the
>> pool While the connection is in the pool, it really remains open for
>> 60 seconds. User2 opens a connection less than 60 seconds after the
>> first connection was released to the pool, so he gets the same
>> connection that was used by User1 Since the temp table was not
>> dropped, it still exists. If your code does not drop and recreate
>> the table at this point, User2 will see User1's data in the temp
>> table
>>
>> That's why dropping temp tables as soon as they are no longer needed
>> is considered good programming practice. Like this:
>>
>> The first user opens a connection - since no connections exist in
>> the pool, a new one is spawned.
>> The temp table is created for the user and used.
>> The temp table is dropped
>> The user "closes" and destroys the connection, releasing it to the
>> pool While the connection is in the pool, it really remains open for
>> 60 seconds. User2 opens a connection less than 60 seconds after the
>> first connection was released to the pool, so he gets the same
>> connection that was used by User1 The temp table is created for the
>> user and used.
>> The temp table is dropped
>> etc.
>>
>>
>> HTH,
>> Bob Barrows
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.