Connection-Pooling Problem

[#1]

Hello,

i have a problem with my connection-pooling in my sa-12 server. In my programm is often open and close connections (for nearly every query). After a while, i get the error message that the maximum of poolsize is reached. if i take a look in Sybase Central, there are a lot of Connections with the type: "CONNECTPOOLCACHE".

If i add CPOOL=No to my connection-string, nothing changed. There are also a lot of connections.

Does anybody else had this problem and got a solution?

Thank you!

[VolkerBarth · Accepted solution]

What API are you using? I'm asking since the .NET Data Provider does its own connection pooling (client-side) by default and does not use the SQL Anywhere connection pooling facility which can be used via the CPOOL connection parameter.

Note: Both ways of connection pooling allow to specify the maximum amount of pooled connections, cf. "Max Pool Size=" for .NET and the "MaxCached=" modifier for the CPOOL parameter.

So what kind of connection pooling do you use?

[Former Member · Accepted solution]

Perfect, the .Net Connection-Pooling was the problem. What pooling would you prefer? Do you have an idea why: POOLING=TRUE;Max Pool Size=3; is not working? The program keeps more than 3 connections in the pool.

[VolkerBarth · Accepted solution]

Sorry, I can't tell any preferance. Note that .NET connection pooling may use several separate pools (AFAIK, based on distinct connection parameters) - are you sure you are using only one?

[Former Member · Accepted solution]

Yes, because if i set POOLING=FALSE, the pool is always empty.

[jack_schueler · Accepted solution]

Setting POOLING=FALSE does not tell you whether you have unique connection strings. For example, these two connection strings create 2 pools because the string is not identical.

UID=user1;PWD=user1;Max Pool Size=5;Pooling=true;ConnectionName=POOL 1

UID=user1;PWD=user1;Max Pool Size=5;Pooling=true;ConnectionName=POOL 2

[jack_schueler · Accepted solution]

Re: error message that the maximum of poolsize is reached

It might help to know what the actual message text was.

[VolkerBarth · Accepted solution]

So the rules for connection pooling are different for .NET pooling and SQL Anywhere pooling? - I'm asking as for the latter, a difference in CON name only is said to allow a connection to be re-used. To cite the 12.0.1 docs:

The connection name can be unique each time, but all other connection parameters must be identical for a cached connection to be reused.