Databases Reference
In-Depth Information
Blob.getBytes(1,1000)
to retrieve only the first 1000 bytes of a 3MB Blob
value. You may assume that only 1000 bytes are retrieved from the database. If
the driver emulates this functionality, the reality is that the entire 3MB Blob value
is retrieved across the network and cached, which slows performance.
If your application executes a query that retrieves five rows when it needs only
two, application performance suffers, especially if the unnecessary rows include
long data.
Performance Tip
One of the easiest ways to improve performance is to limit the amount of
network traffic between the driver and the database server—optimally by
writing SQL queries that instruct the driver to retrieve from the database
only the data that the application requires.
Make sure that your
Select
statements use a
Where
clause to limit the
amount of data that is retrieved. Even when using a
Where
clause, a
Select
state-
ment that does not adequately restrict its request could retrieve hundreds of
rows of data. For example, if you want data from the employees table for each
manager hired in recent years, your application could execute the following
statement, and subsequently, filter out the rows of employees who are not man-
agers:
SELECT * FROM employees
WHERE hiredate > 2000
However, suppose the employees table contains a column that stores pho-
tographs of each employee. In this case, retrieving extra rows is extremely expen-
sive to your application performance. Let the database filter the request for you
and avoid sending extra data that you don't need across the network. The follow-
ing query uses a better approach, limiting the data retrieved and improving per-
formance:
SELECT * FROM employees
WHERE hiredate > 2003 and job_title='Manager'
