Alternative ADO DB Connection Strategies
Different strategies for establishing an ADO database connection can be used in a client/server application . Those stratgies are each examined below.
1. Let each control (or ADO control) establish its own connection to the database
The default method for establishing a connection (used by VB beginners) is to specify the database source on each control. This method is very expensive to both the client application and the server. Establishing a connection to the database is a comparatively slow operation and so each client application form with control(s) will open more slowly since it must establish a new connection. Establishing separate connections is doubly hard on the database server. The database server has to spend a lot of time establishing/dropping database connections (slowing down data retrieval). Also, when the there are multiple controls open in the application, then multiple database connections must be maintained. Each database connection requires a good amount of server memory so maintaining several database connections per client is much harder on the server’s memory than maintaining a single database connection per client.
Microsoft’s own documentation states: "Be aware that the ADO control is a comparatively expensive method of creating connections, using at least two connections for the first control, and one more for each subsequent control."
2. Let each form establish its own connection to the database
A slight improvement on the first method is to establish one database connection per open form. Since some forms may have multiple controls, this technique may use fewer connections than the first method. However, it has most of the same drawbacks of the first methods (slow form startup, multiple connections per client application, etc.)
3. Establish an Application-level Connection
The preferred method is to establish an application-level connection and then re-use that connection across forms, controls and command objects. The application establishes the database connection once during startup so the connection performance penalty is not "paid" time and time again. The load on the database server is much smaller because fewer connections need to be managed (better server memory usage). Also, the server spends much less time establishing/dropping connections and is therefore able to provide better data retrieval and update response times.
4. More sophisticated techniques
Visual Basic provides more sophisticated techniques for managing connections including connection pooling. Connection pooling techniques are particularly important for web-based applications where a database connection must be acquired with each database access. Certain high-use client/server applications can also benefit from connection pooling.
... back to ADO Topics
Copyright © Woodger Computing Inc.