Question Re: Connection Pooling in Technology Q&A

Connection Pooling

Former Member — Wed, 16 Feb 2011 17:22:32 GMT

I am using SQL Anywhere 11 and have my database running as a service on Windows XP.

The docs only mention connection pooling for the .Net Provider. I am connecting from PowerBuilder using ODBC.

I will have multiple instances of the app connecting at the time using the same exact connection parameters.

Can this be done?

Re: Connection Pooling

Breck_Carter — Wed, 16 Feb 2011 19:03:04 GMT

Not sure where "connection pooling" comes into play... you can have as many instances of an application connecting that you want, all at the same time, subject to licensing restrictions.

Re: Connection Pooling

VolkerBarth — Thu, 17 Feb 2011 09:01:44 GMT

I'm not sure but I guess connection pooling as a general product feature has been introduced with SA 12.

Cf. the new CPOOL connection parameter.

I don't know whether the general ODBC connection pooling (cf. the ODBC Admin's according tab) works with SQL Anywhere (and if so, how well it works).

Re: Connection Pooling

MCMartin — Thu, 17 Feb 2011 09:16:34 GMT

The complete connection string has to be identical. It includes the APPINFO part in which the process id and thread id is mentioned. So in reality connection pooling is only possible if the connection attempt comes from the same thread in the same process.

EDIT: According to this answer by Ian, the above statement is not completely true:

The generated portion of the AppInfo string is ignored when determining if two connection strings are the same for connection pooling. If a user specified AppInfo parameter is specified, that is included in the comparison.

Re: Connection Pooling

VolkerBarth — Thu, 17 Feb 2011 10:39:10 GMT

I've been not aware of that important restriction (but haven't used pooling, either). - If so, and given the typical synchronous execution of DB requests - why would one single thread ever use several connections to the same database at the same time (except for differentiating between read-only and read/write access)? - I guess then the built-in pooling is primarily useful for applications that use their own thread pool for database access: Then each thread might re-use its "own" pooled connection when the thread gets re-activated. - Or do I have missed the point?

Re: Connection Pooling

Breck_Carter — Thu, 17 Feb 2011 10:42:09 GMT

@Volker: Application servers on the web, that's who... with a zillion web connections sharing a few database connections.

Re: Connection Pooling

VolkerBarth — Thu, 17 Feb 2011 11:00:10 GMT

@Breck: Yes, that's my conclusion. - Don't we all wrote those kind of apps:)

Re: Connection Pooling

Former Member — Thu, 17 Feb 2011 14:01:11 GMT

Actually my app could be considered a web app server. It is a web server plugin that runs PowerBuilder code. IIS will have a separate thread for each browser request and the PB code will use the same connection string for every request.

Re: Connection Pooling

Former Member — Thu, 17 Feb 2011 14:08:22 GMT

I just checked and the default for AppInfo is empty string. Also by default an unused connection is closed if unused for 60 seconds. So a lightly used app would probably not use a pooled connection.

Re: Connection Pooling

Former Member — Thu, 17 Feb 2011 14:10:39 GMT

I never noticed the Pooling tab in ODBC Admin. Thanks for pointing it out.