Databases Reference
In-Depth Information
1.
Execute a statement
—The application sends a SQL statement (for
example,
SELECT * FROM table WHERE ...
) to the database server via
the driver.
2.
Fetch rows
—The driver retrieves all the values of all the result columns
from the database server, as it did in the previous example. However, in
this case, the entire employee image is not retrieved from the database
server; instead, a placeholder integer value is retrieved.
3.
Retrieve result values into the application
—When the application
requests data, it is moved from the driver into the application buffers on
a column-by-column basis. If the application requests the contents of
the Picture column, the driver initiates a request to the database server
to retrieve the image of the employee that is identified by the place-
holder value it retrieved. In this scenario, the performance hit associated
with retrieving the image is deferred until the application actually
requests that data.
In general, LOB data types are useful and preferred because they allow effi-
cient use of long data on an as-needed basis. When the intent is to process large
amounts of long data, using LOBs results in extra round trips between the driver
and the database server. For example, in the previous example, the driver had to
initiate an extra request to retrieve the LOB value when it was requested. These
extra round trips usually are somewhat insignificant in the overall performance
of the application because the number of overall round trips needed between the
driver and the database server to return the entire contents of the long data is the
expensive part of the execution.
Although you might prefer to use LOB types, doing so is not always possible
because much of the data used in an enterprise today was not created yesterday.
The majority of data you process was created long before LOB types existed, so
the schema of the tables you use may not include LOB types even if they are sup-
ported by the version of the database system you are using. The coding tech-
niques presented in this section are preferred regardless of the data types defined
in the schema of your tables.
Performance Tip
Design your application to exclude long data from the Select list.
