Databases Reference
In-Depth Information
How does using statement pooling affect whether you use a statement or a
prepared statement?
•
If you are using statement pooling and a SQL statement will only be exe-
cuted once, use a statement, which is not placed in the statement pool. This
avoids the overhead associated with finding that statement in the pool.
•
If a SQL statement will be executed infrequently but may be executed multi-
ple times during the life of a statement pool, use a prepared statement.
Under similar circumstances without statement pooling, use a statement.
For example, if you have some statements that are executed every 30 minutes
or so (infrequently), the statement pool is configured for a maximum of 200
statements, and the pool never gets full, use a prepared statement.
To retrieve data efficiently, do the following:
•
Return only the data you need. Read “Retrieving Long Data,” page 31.
•
Choose the most efficient way to return the data. Read “Limiting the
Amount of Data Returned,” page 34, and “Choosing the Right Data Type,”
page 34.
•
Avoid scrolling through the data. Read “Using Scrollable Cursors,” page 36.
•
Tune your database middleware to reduce the amount of information that is
communicated between the database driver and the database. Read “The
Network,” page 44.
For specific API code examples, read the chapter for the standards-based API
that you work with:
•
For ODBC users, see Chapter 5.
•
For JDBC users, see Chapter 6.
•
For ADO.NET users, see Chapter 7.
Understanding When the Driver Retrieves Data
You might think that if your application executes a query and then fetches one
row of the results, the database driver only retrieves that one row. However, in
most cases, that is not true; the driver retrieves many rows of data (a block of
data) but returns only one row to the application. This is why the first fetch your
