Databases Reference
In-Depth Information
Performance Tip
When you cannot avoid retrieving data that generates a large amount of
network traffic, your application can still control the amount of data
being sent from the database to the data provider by limiting the number
of rows sent across the network and reducing the size of each row sent
across the network.
Suppose that you have a GUI-based application, and each screen can display
only 20 rows of data. It's easy to construct a query that may retrieve a million
rows, such as
SELECT * FROM employees
, but it's hard to imagine a scenario
where a query that retrieves a million rows would be useful. Some data providers
allow you to use a
MaxRows
property on the
Command
object. For example, if an
application calls the following command, no query to the Oracle database will
retrieve more than 10,000 rows to the application:
OracleCommand.MaxRows=10000;
Some data providers allow you to limit the bytes of data a connection uses to
retrieve multiple rows. Similarly, some data providers allow you to limit the bytes
of data that can be retrieved from
TEXT
or
IMAGE
columns. For example, with
Microsoft SQL Server and Sybase ASE, you can execute
Set TEXTSIZE n
on any
connection, where
n
is the maximum number of bytes that will be retrieved from
any
TEXT
or
IMAGE
column.
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 will not fit into the on-chip cache, sending and
retrieving certain data types is still expensive. When you are working with data
on a large scale, it's important to select the data type that can be processed most
efficiently. Sending and retrieving certain data types across the network can
increase or decrease network traffic.
