Databases Reference
In-Depth Information
Performance Tip
One of the easiest ways to improve performance is to limit the amount
of network traffic between the data provider and the database server—
optimally by writing SQL queries that instruct the data provider to
retrieve from the database only the data that the application requires.
Particularly when using a
DataSet
, make sure that your
Select
statements
limit the data that is retrieved by using a
Where
clause. Even when using a
Where
clause, a
Select
statement 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 employ-
ees who are not managers:
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 needs to
display information from support case histories, which each contain a 10MB log
file. Does the user really need to see the entire contents of the file? If not, perfor-
mance would improve if the application displayed only the first 1MB of the log
file.
