How to implement Connection Pooling

[tyreejp]

I understand the concept behind connection pooling and I also understand how to create a connection pool via the connection string. What I don't have a grasp of is how to implement it. Do I declare a SqlConnection object that is global in scope when my windows service starts and create a new SqlConnection object everytime a new thread is launched, using the exact same connection string that I used on my global SqlConnection object??

tia-

--
James

http://www.microsoftjr.com

 

[JohnYingling]

Provider=DB2OLEDB;Connection Pooling=True;Password=ppt01;User ID=;Default Schema='';Initial Catalog=DSN;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=mss;Network Port=1111;Package Collection=DEV;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/MVS;Persist Security Info=True;

http://www.moldyoldy.com/VBComparedemo

- free online Compare/Diff of snippets

 

[tyreejp]

I'm familiar with the whole connection string portion... it was the other parts I had a question about.

Thanks for the response though.

--
James

http://www.microsoftjr.com

 

[JohnYingling]

Connection pooling is taken care of by the provider with no extra programming except to specify ConnectionPooling=true.

Understand that a connection in the pool is re-used ONLY if the entire connection string in ther request matches a connection string in the pool.

http://www.moldyoldy.com/VBComparedemo

- free online Compare/Diff of snippets

 

[tyreejp]

I think the light bulb just went on in my head. The performance increase is on the SQL Server side. Because I'm using the exact same connection string and I'm specifying that ConnnectionPooling=true, SQL Server is bascially maintaining the pool for me and bypassing the expensive action of opening and closing repeated connections under normal cirumstances. Is my understanding correct now?

--
James

http://www.microsoftjr.com

 

[JohnYingling]

NOt exactly. The connection pool is on your side, keeping connections open to the SQL Server so that a round trip is not requred to open a connection. One problem, might arise. A "lost" connection may not be detected in the Open because there will be no contact with the server until the atual request is sent i.e. there is no exchange with the server on the Open if a pool connection is used.

http://www.moldyoldy.com/VBComparedemo

- free online Compare/Diff of snippets

 

[JohnYingling]

On my system, OLEDB2 to a DB2 on an IBM mainframe, Connection Pooling saved only 16 milliseconds per request and introduced othere intermittent problems.

http://www.moldyoldy.com/VBComparedemo

- free online Compare/Diff of snippets

 

[JohnYingling]

When I say "provider", I mean the client side code of the provider that commnuicates with the server.

http://www.moldyoldy.com/VBComparedemo

- free online Compare/Diff of snippets

 

[tyreejp]

Ok, so then I do need a Connection object declared globally so the threads that are created can all take advantage of it? Or does the mere fact that I'm using the exact same connection string each time I create a new instance of the Connection object (with ConnectionPooling=True set of course) satisfy the whole connection pooling scheme? This is where I'm not understanding.

I would think that I would establish a connection to the db when my service starts (with ConnectionPooling=True set of course) and reference that connection object each time I make a db call. Am I getting warm?

--
James

http://www.microsoftjr.com

 

[chiph]

Or does the mere fact that I'm using the exact same connection string each time I create a new instance of the Connection object

Yes.

Note that your authentication method counts towards "identical-ness". Use SQL Server authentication to make sure the connection string is totally identical. If you use Windows authentication, the client will replace it with your process' credentials, which will almost certainly result in different connection strings, losing the benefit of pooling.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first