Databases Reference
In-Depth Information
Limiting the Amount of Data Returned
One of the easiest ways to improve performance is to limit the amount of net-
work traffic between the database driver and the database server—one way is to
write SQL queries that instruct the driver to retrieve from the database and
return to the application only the data that the application requires. However,
some applications need to use SQL queries that generate a lot of traffic. For
example, consider an application that needs to display information from support
case histories, which each contain a 10MB log file. But, does the user really need
to see the entire contents of the file? If not, performance would improve if the
application displayed only the first 1MB of the log file.
Performance Tip
When you cannot avoid returning data that generates a lot of network
traffic, control the amount of data being sent from the database to the
driver by doing the following:
• Limiting the number of rows sent across the network
• Reducing the size of each row sent across the network
You can do this by using the methods or functions of the API you work
with. For example, in JDBC, use
setMaxRows()
to limit the number of
rows a query returns. In ODBC, call
SQLSetStmtAttr()
with the
SQL_ATTR_MAX_LENGTH
option to limit the number of bytes of data
returned for a column value.
Choosing the Right Data Type
Advances in processor technology have brought significant improvements to the
way that operations, such as floating-point math, are handled. However, when
the active portion of your application does not fit into on-chip cache, retrieving
and returning certain data types is expensive. When you are working with data
on a large scale, select the data type that can be processed most efficiently.
Retrieving and returning certain data types across the network can increase or
decrease network traffic. Table 2-1 lists the fastest to the slowest data types to
process and explains why.
