p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

aspdotnet_website_programming thread: Connection pool and user info


Message #1 by "Witold Iwaniec" <wiwaniec@n...> on Tue, 21 Jan 2003 14:48:27
Hi all

I wonder if an application can use an already opened connection but pass 
credentials of the application user?

I would like my web application to use a connection pool to increase 
performance by reducing opening and closing DB connections. So when my 
application server starts up it would create pool of connections 
connecting as APP_USER user. Then when user JDOE runs my application, the 
application would use one of the already opened connections, but SQL 
Server would know that it should use JDOE's rights and any changes, also 
saved in an audit table, would be made as JDOE. Of course JDOE would also 
be a database user but the application would not have to open new 
connection for JDOE.

BTW Is the subject of connection pooling covered in the book?

Thanks

Witold 
Message #2 by "Martin Budar" <martin@b...> on Wed, 22 Jan 2003 21:17:46 +0100
Hi Witold,

I think you can re-use only connections with the same parameters, 
including user credentials. Why are you trying to open connections in 
advance (during application start)? Did you encounter some performance 
problems?

In every book I read was recommended to close SQL Server connections as 
soon as possible to release server resources. I follow this rule and I 
never have had any problems with performance - but I'm running intranet 
application for about 300 users only.

You can find some more info about connection pooling e.g. here:
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/vbcon/
html/vburfintroductiontoadoconnectiondesigntools.asp
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/cpguid
e/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

Martin

Message #3 by "Witold Iwaniec" <wiwaniec@n...> on Wed, 22 Jan 2003 16:53:28 -0400
Martin

Thanks for the reply.
I didn't have any performance problems but opening and closing a connection
takes some time. While it may not be too long for the user, connection pool
allows you use already opened connections and get the response faster. I
have done it and could see the difference.

Even in case with 300 users - if all of them use the application at the same
time, there are 300 connections opened and closed while each of them may be
used just for a fraction of the time.
I would like to open connections in advance - when the web server starts
there or a user uses the application for the first time, the delay may not
matter too much but after that the connections would be ready. When the next
users starts the application, there will be a connection waiting for them. I
don't need to open many connections at startup - maybe just 1 or 2
connections and reuse them or add more when needed.

In smaller applications it may not matter but I think it is part of learning
the technonogy.

Thanks for the links

Witold



-----Original Message-----
From: Martin Budar [mailto:martin@b...]
Sent: 22 January 2003 4:18 PM
To: Website Programming with ASP.NET
Subject: [aspdotnet_website_programming] Re: connection pool and user info


Hi Witold,

I think you can re-use only connections with the same parameters, including
user credentials. Why are you trying to open connections in advance (during
application start)? Did you encounter some performance problems?

In every book I read was recommended to close SQL Server connections as soon
as possible to release server resources. I follow this rule and I never have
had any problems with performance - but I'm running intranet application for
about 300 users only.

You can find some more info about connection pooling e.g. here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vburfintroductiontoadoconnectiondesigntools.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconconnectionpoolingforsqlservernetdataprovider.asp

Martin


---
Professional Design Patterns in VB.NET:
Building Adaptable Applications

Want to know how design patterns bring reusable
design and adaptabilty to your applications? How
to recognize the need for a design pattern
solution? How to select, design, and implement
the right patterns? How parts of the .NET Framework
(like the .NET Data Providers and .NET Remoting)
take advantage of design patterns? This book
presents a practical approach to using design
patterns in VB.NET, by focusing on the relevance
of design patterns in the different tiers of a
distributed n-tier architecture.

http://www.wrox.com/books/1861006985.htm


Message #4 by "Martin Budar" <martin@b...> on Thu, 23 Jan 2003 10:01:16
Hi Witold

maybe I don't understand exactly what do you mean with term "connection 
pool". In ASP and ASP.NET, when you open connection for the first time, it 
is added to automaticaly created connection pool. When you call 
mySqlConnection.Close() method, the connection isn't closed in fact, it  
stays at this pool for some time and you can reuse it next time. Are you 
talking about that case or would you like to create your connection pool, 
e.g. during Application_Start or Session_Start events? In your situation 
it would be better to store connection object in Session variable, because 
of different login credentials for every user. But you would need to open 
connection for every user and it would take a lot of resources in case of 
heavy-load application (thousands of users)...

Martin
 
Message #5 by "Witold Iwaniec" <wiwaniec@n...> on Fri, 24 Jan 2003 09:08:17 -0400
Martin

Thanks for the reply.

I may be wrong but if I open a connection for the first time, it is added to
the connection pool and can be reused. But if I try to connect as another
user, there will not be available connection with this exact connect string
so new connection will be created and added to the pool.

Also you are correct - I would like to create my own connection pool. ASP
and ASP.NET will handle connection pools for you you but I am looking at
using it with other technologies. For example in a client-server application
I could have a middle tier that handles connections so instead of 200 - 300
users (which is not unusual with our products) opening 200 - 300 connections
to the server I could have a middle tier with maybe only 5 connections which
would require much less resources on the server. Even in a web application,
if there is 500 different users, there will be 500 connections whether I
create it manually or ASP.NET does it for me. I mean each user wil have
her/his own connection opened but the DB server will have 500 connections
opened. So a connection manager with ability to use existing connection and
"switch" the user would be good.

I think I asked the wrong question - I am wondering if I could use existing
connection and "switch" the user. I was told by experts that it can be done
with Oracle 9i and wonder if it can be done with SQL Server. I assumed it
would be used most often in web applications so asked it on web related
list.

Maybe, with the hardware available today, the differences are measured only
in computer time. The user keystrokes can take longer than opening a
connection but I am just curious...

Thanks

Witold



-----Original Message-----
From: Martin Budar [mailto:martin@b...]
Sent: 23 January 2003 10:01 AM
To: Website Programming with ASP.NET
Subject: [aspdotnet_website_programming] Re: connection pool and user
info


Hi Witold

maybe I don't understand exactly what do you mean with term "connection
pool". In ASP and ASP.NET, when you open connection for the first time, it
is added to automaticaly created connection pool. When you call
mySqlConnection.Close() method, the connection isn't closed in fact, it
stays at this pool for some time and you can reuse it next time. Are you
talking about that case or would you like to create your connection pool,
e.g. during Application_Start or Session_Start events? In your situation
it would be better to store connection object in Session variable, because
of different login credentials for every user. But you would need to open
connection for every user and it would take a lot of resources in case of
heavy-load application (thousands of users)...

Martin

---
Professional Design Patterns in VB.NET:
Building Adaptable Applications

Want to know how design patterns bring reusable
design and adaptabilty to your applications? How
to recognize the need for a design pattern
solution? How to select, design, and implement
the right patterns? How parts of the .NET Framework
(like the .NET Data Providers and .NET Remoting)
take advantage of design patterns? This book
presents a practical approach to using design
patterns in VB.NET, by focusing on the relevance
of design patterns in the different tiers of a
distributed n-tier architecture.

http://www.wrox.com/books/1861006985.htm


Message #6 by "Martin Budar" <martin@b...> on Sat, 25 Jan 2003 08:58:32
Hi Witold,

it sounds like interesting future (changing user credentials on opened 
connection). But I've never heard it's been possible in MS SQL server. The 
question is how much resources (processor time etc.) consume changing 
users comparing with opening new connection...

Concerning client/server (not web based) applications I believe it could 
be often usefull to open new connection for each user, because of 
transactions, table locking and state management. I think that SQL server 
could be capable to handle 300 connections without problems - but I'm not 
expert in this area so I can only guess...

Martin
Message #7 by "Ken Schaefer" <ken@a...> on Tue, 28 Jan 2003 11:16:27 +1100
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Witold Iwaniec" <wiwaniec@n...>
Subject: [aspdotnet_website_programming] Re: connection pool and user info


: I may be wrong but if I open a connection for the first time, it is added
to
: the connection pool and can be reused. But if I try to connect as another
: user, there will not be available connection with this exact connect
string
: so new connection will be created and added to the pool.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Actually, a new pool with be created, and the connection added to the 2nd
pool.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Also you are correct - I would like to create my own connection pool. ASP
: and ASP.NET will handle connection pools for you you but I am looking at
: using it with other technologies. For example in a client-server
application
: I could have a middle tier that handles connections so instead of 200 -
300
: users (which is not unusual with our products) opening 200 - 300
connections
: to the server I could have a middle tier with maybe only 5 connections
which
: would require much less resources on the server. Even in a web
application,
: if there is 500 different users
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There will only be 500 connections if you are:
a) using impersonation
-and-
b) you are not using IIS anonymous access.

If you are using impersonation then all DB access will be under the aspnet
account. If you are using IIS anonymous access (and impersonation) then
everything will be done under then IIS anonymous access account.

However, what you want to do (have a single pool) is possible under SQL
Server. In SQL Server Books Online, look up "Application Roles" in the
index. You can use an application role to supplant the Windows user context
with the application context. All users would be using this context to
access the database, so there would only be 1 pool, and 1 set of
connections.

Cheers
Ken

Message #8 by "Witold Iwaniec" <wiwaniec@n...> on Tue, 28 Jan 2003 08:46:52 -0400
Ken

Thanks for the info, I will look into the Application Roles.

Witold



-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 27 January 2003 8:16 PM
To: Website Programming with ASP.NET
Subject: [aspdotnet_website_programming] Re: connection pool and user
info


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Witold Iwaniec" <wiwaniec@n...>
Subject: [aspdotnet_website_programming] Re: connection pool and user info


: I may be wrong but if I open a connection for the first time, it is added
to
: the connection pool and can be reused. But if I try to connect as another
: user, there will not be available connection with this exact connect
string
: so new connection will be created and added to the pool.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Actually, a new pool with be created, and the connection added to the 2nd
pool.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Also you are correct - I would like to create my own connection pool. ASP
: and ASP.NET will handle connection pools for you you but I am looking at
: using it with other technologies. For example in a client-server
application
: I could have a middle tier that handles connections so instead of 200 -
300
: users (which is not unusual with our products) opening 200 - 300
connections
: to the server I could have a middle tier with maybe only 5 connections
which
: would require much less resources on the server. Even in a web
application,
: if there is 500 different users
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There will only be 500 connections if you are:
a) using impersonation
-and-
b) you are not using IIS anonymous access.

If you are using impersonation then all DB access will be under the aspnet
account. If you are using IIS anonymous access (and impersonation) then
everything will be done under then IIS anonymous access account.

However, what you want to do (have a single pool) is possible under SQL
Server. In SQL Server Books Online, look up "Application Roles" in the
index. You can use an application role to supplant the Windows user context
with the application context. All users would be using this context to
access the database, so there would only be 1 pool, and 1 set of
connections.

Cheers
Ken


---
Professional Design Patterns in VB.NET:
Building Adaptable Applications

Want to know how design patterns bring reusable
design and adaptabilty to your applications? How
to recognize the need for a design pattern
solution? How to select, design, and implement
the right patterns? How parts of the .NET Framework
(like the .NET Data Providers and .NET Remoting)
take advantage of design patterns? This book
presents a practical approach to using design
patterns in VB.NET, by focusing on the relevance
of design patterns in the different tiers of a
distributed n-tier architecture.

http://www.wrox.com/books/1861006985.htm



  Return to Index