We have a Windows VFP 8.0 app that uses persisent connections to SQL Server. Everywhere says to not use persistent connections and use Connection Pooling with SQL Server, that "resources" will be used up.

Can someone give with a list of what these "resources" are I know about Connection Pooling and memory, but our SQL Server server is a 8-way 64-bit processor machine with 64 Gigs of ram running WinServer2003 64-bit with 64-bit SQL Server 2000. There is never more than 51 gigs of ram used.



Re: Persistent Connections...


You may find something about persistent connections in paragraph Connecting to Database Servers at:

You also may find about pooling & persistent connections in the next paragraph.

You may check:

Re: Persistent Connections...

Carl Warner

Maybe I've thought about it all wrong. But, I thought the resources were client side resources that could be used up without connection pooling rather than the possible load on the server side. It just makes sense to not have to re-establish yet another connection on the client side if you can simply re-use one that was already established. It avoids the overhead of firing up yet another one.

INFO: Frequently Asked Questions About ODBC Connection Pooling scid=kb;en-us;169470

Re: Persistent Connections...


I am not sure what "everywhere" means, but generally the issues about persistent connections refer to WEB-based applications where there is no way to limit to the number of users who may try to connect, and no way to tell how long a user will stay connected, or even if they will bother to disconnect.

If you are running a VFP APP over a LAN there is nothing wrong with using persistent connections - in fact it is a significant performance enhancer in this scenario. Establishing a connection to a database is a relatively (in system terms) expensive buisness, and once you have made the connection for a given user, then it makes sense to hang on to it and re-use it for the same user rather than constantly connecting and disconnecting the SAME person every time they try to access data.

The idea behind connection pooling is that rather than estblish a specific connection for each user on demand, you create a fixed number of persistent connections and allocatate them to users as needed. In other words connection pooling is really only relevant only in 'stateless' environments or where the number of users is large, but the number actually connected at any one time is small.

It makes absolutely no sense at all in a 'stateful' environment (such as a VFP Application running over a LAN) where each user connects to the database and works interactively with the data over a long period of time (by "long" I mean more than a single request).