Databases Reference
In-Depth Information
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.
Remember that creating a connection is one of the most performance-expensive
operations that an application performs.
Performance Tip
Because of the performance hit your application pays for opening con-
nections, once your application is connected, you should avoid establish-
ing additional connections to gather information about the driver and
the database, such as supported data types or database versions, using
SQLGetInfo
and
SQLGetTypeInfo
. For example, some applications estab-
lish a connection and then call a routine in a separate DLL or shared
library that reconnects and gathers information about the driver and the
database.
How often do databases change their supported data types or database ver-
sion between connections? Because this type of information typically doesn't
change between connections and isn't a large amount of information to store,
you may want to retrieve and cache the information so the application can access
it later.
