Share via

Connection Use Optimization

  • Article

Establishing a connection uses time and memory on both the client and the server. When you optimize connections, you balance your need for high performance against the resource requirements of your application.

The number of connections used by Visual FoxPro depends on whether you force the closing of unused connections, and how you set the length of the connection idle timeout.

Using Shared Connections

You can use connections exclusively or share a connection. Each method has its benefits. When you use a connection exclusively, your application experiences no contentions for connection resources once a connection is established. If each result set uses an exclusive connection, you can also intermingle asynchronous processing on multiple result sets.

When you use a shared connection, you have one connection for multiple result sets. You must serialize data manipulation operations on the result sets sharing the same connection, and design the application to test the connection for busyness any time conflicts might occur. For information on sharing a connection, see Creating Views.

Controlling Connection Timeouts

If your application doesn't take any action for a long time, you can reduce connection use by setting the IdleTimeout property on the connection. The IdleTimeout property controls the interval of time connections are allowed to idle before they're closed by Visual FoxPro. By default, connections wait indefinitely and are not deactivated until specifically closed by the user.

You set the idle time for a connection definition with the IdleTimeout property of the DBSETPROP( ) function; you can set the IdleTimeout property for an active connection with the SQLSETPROP( ) function.

Visual FoxPro closes connections even if Browse windows and forms displaying remote data are still open, and then automatically reconnects when the connection is needed again. However, Visual FoxPro cannot close a connection if:

  • Results of a query from the server are pending.
  • The connection is in manual transaction mode. You must commit or roll back the transaction and switch to automatic transaction mode before the connection can be closed.

You set the transaction mode for a connection definition with the Transactions property of the DBSETPROP( ) function; you can set the transaction mode for an active connection with the SQLSETPROP( ) function.

Releasing Connections

You can improve performance by closing connections that your application is no longer using. Connections are closed automatically for you when you close a view. If the connection is shared by multiple views, Visual FoxPro closes the connection when the last view using the connection is closed.

You can control the connection for a query manually if you don't want to update the data in a cursor. Use a SQL pass-through query to select the data you need into a local cursor and then close the connection.

See Also

Client/Server Performance Optimization | Speeding Up Data Retrieval | Query and View Acceleration | Performance Improvement on Updates and Deletes | Form Acceleration | Implementing a Client/Server Application | Creating Views