Databases Reference
In-Depth Information
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'
Sometimes applications need to use SQL queries that generate a large
amount of network traffic. For example, consider an application that displays
information from support case histories, which each contains a 10MB log file.
Does the user really need to see the entire contents of the log file? If not, perfor-
mance would improve if the application displayed only the first 1MB of the log
file.
