One public connection or not? (ADO.Net)

[dee-u]

I need your opinions guys... Link

Sorry mods/admins for double-posting, just wanted to widen my net, if you deem it unnecessary then you may just delete this thread.

[mendhak]

I tried deleting this thread but I guess I'm no moderator.

[szlamany]

dee-u

it's the database that mostly determines the connection method (global, constantly open, open/close, private). Then as Mendhak said in the other thread - the architecture as well.

MS is really pushing connection pooling for MS SQL server now. 3 years ago that was not the case.

If you are using ACCESS (and I believe you are?) - then it would be very different.

And yes - this question does belong in the DB section

[dee-u]

Can't we use the same pooling strategy for OleDb which is used by MS Access?

[szlamany]

Connection pooling is a server-side action.

Each connection to a SQL database creates a thread - an actual application thread in memory - as if a separate program is running.

The operating system scheduler runs through these threads just like a regular workstation runs through application threads and windows that are all open on that workstation.

I believe - and it's been a while since I read this - that each MS SQL connection thread is 2 MB of memory. Thus the reason for SQL boxes needed huge multi-gig memory setups.

With the advent of web-connected applications (the .Net-if-you-will) you can see how connections to the DB can get extensive - and how having a thousand connection, for instance, could be a problem.

With local WAN/LAN applications - where the number of connections is more reasonable - and more constant - this is not such a big problem.

Connection pooling allows the SQL box to re-use a connection for several "clients" based on them having the exact same username/db-authorization as a connection already in the pool.

The server does not have to expend the effort to create a new connection if one is sitting idle that reflects the needs of the client.

In our systems - where a thousand possible teachers can connect - each with a different username/pw - it's not beneficial to use connection pooling. And even with that many users, usually under 200 are connected at a given moment. A good SQL box can process 200 or less connections with absolutely no effort. With our apps we use a global connection and keep it open for the entire time the user is in the app. It's obvious that there would be no benefit to connection pooling or closing the connection with our situation.

We are developing a Report Writer UI in VB.Net. For this app - which gets data from SQL at the initial load of the report on the design screen and then doesn't talk to the SQL box until the report is written back to the DB - we are closing the connection and re-opening it as needed. We are doing this in a class - with a private (to the class) connection object. We do not expose any of the ADO to the app - it's all buried in the class. That's a program architecture issue - not a "DB connection" issue.

With ACCESS - which does not really process like a SQL SERVER - not creating threads for each connection - there is no such thing as connection pooling. So whether you open or close the connection to ACCESS is really a matter as to whether ACCESS prefers that type of activity - not an ADO.Net issue - but an ACCESS issue.

Have you found any MSDN white-papers regarding closing and re-opening connections to an ACCESS database??

[dee-u]

In our systems - where a thousand possible teachers can connect - each with a different username/pw - it's not beneficial to use connection pooling. And even with that many users, usually under 200 are connected at a given moment. A good SQL box can process 200 or less connections with absolutely no effort. With our apps we use a global connection and keep it open for the entire time the user is in the app. It's obvious that there would be no benefit to connection pooling or closing the connection with our situation.

For instance, if you wanted to process something that would employ a datareader or any of those ado.net objects, would you just call your global connection and set it as your objects connection then open it when you need it and close it afterwards, or are you going to create a new connection object for each need? In the later scenario I thought instantiating a connection object would just grab the connection to the sql server or the available pool hence it is still connection pooling?

Have you found any MSDN white-papers regarding closing and re-opening connections to an ACCESS database??

I 'm not sure but since Access uses tjhe OleDb provider I thought there's also support for pooling...

[dee-u]

With ACCESS - which does not really process like a SQL SERVER - not creating threads for each connection - there is no such thing as connection pooling. So whether you open or close the connection to ACCESS is really a matter as to whether ACCESS prefers that type of activity - not an ADO.Net issue - but an ACCESS issue.

Isn't connection pooling a .Net feature (link) and not a database feature? And from that article they recommend of always closing the connection after each use, is MS Access the exemption?

[szlamany]

Isn't connection pooling a .Net feature (link) and not a database feature? And from that article they recommend of always closing the connection after each use, is MS Access the exemption?

If it wasn't handled by the server or the data provider then how could another user get access to an available connection in the pool?

[dee-u]

If it wasn't handled by the server or the data provider then how could another user get access to an available connection in the pool?

Hmmmnnn... As you said it, isn't that handled by the data provider?

[szlamany]

ACCESS does not have a "server" component - in that light I see connection pooling with ODBC/ACCESS as a "emulation" of server-pooling. The data provider when I'm talking with ADO to my SQL box is talking to a different server - that's where the pool starts making sense to me.

Microsoft offers two types of pooling. Connection pooling is available through ODBC and can be configured by using the ODBC Data Source Administrator, the registry, or the calling application. Resource pooling is available through OLE DB, and can be configured through the application's connection string, the OLE DB API, or the registry.

Here's some links to MSDN (and elsewhere)...

http://support.microsoft.com/default...b;en-us;169470

http://support.microsoft.com/default.aspx?kbid=324686

http://msdn.microsoft.com/library/de...l/pooling2.asp

http://www.15seconds.com/issue/010814.htm

http://pluralsight.com/blogs/keith/a...7/09/1591.aspx

http://www.15seconds.com/issue/010514.htm