Databases Reference
In-Depth Information
In streaming protocol databases, only one request can be processed at a time over
a single connection; other requests on the same connection must wait for the
preceding request to complete. Sybase ASE, Microsoft SQL Server, and MySQL
are examples of streaming protocol databases.
In contrast, when connecting to cursor-based protocol databases, the driver
tells the database server when to work and how much data to retrieve. Several
cursors can use the network, each working in small slices of time. Oracle and
DB2 are examples of cursor-based protocol databases. For a more detailed expla-
nation of
streaming versus cursor-based protocol databases,
see “One
Connection for Multiple Statements,” page 16.
The advantage of using one connection for multiple statements is that it
reduces the overhead of establishing multiple connections, while allowing multi-
ple statements to access the database. The overhead is reduced on both the data-
base server and client machines. The disadvantage is that the application may
have to wait to execute a statement until the single connection is available. See
“One Connection for Multiple Statements,” page 16, for guidelines on using this
model of connection management.
Each physical connection to the database consumes a substantial amount of
memory on both the client and database server.
Performance Tip
Remember to close connections immediately after your application is fin-
ished with them—don't wait for the garbage collector to close them for
you. This is particularly important if your application uses connection
pooling so that connections are returned to the connection pool immedi-
ately for other users to use.
For Java applications, the JVM uses garbage collection to automatically
identify and reclaim memory allocated to objects that are no longer in use. If you
wait for the garbage collector to clean up connections no longer in use, memory
is tied up longer than necessary. Regardless of whether you use connection pool-
ing, always remember to explicitly close connections as soon as the user no
longer needs them so that connections will release the memory allocated for
them.
