Java Reference
In-Depth Information
The problem of generating queries dynamically is not confined to applications
that use
SQL
directly. In section 11.3.1 you will see how
JDO
applications must also
generate queries by concatenating query fragments together. Hibernate, on the
other hand, has criteria queries that provide an object-oriented
API
for construct-
ing queries dynamically.
11.1.3
Improving the performance of SQL queries
Next, let's look at the techniques you can use to improve query performance of a
SELECT
statement. If the database is large, some queries—even simple ones—can
be expensive to execute. For example, consider the following simple
SQL
query,
which retrieves information about the orders that were placed in the past 30 days
and sorts them by external order
ID
:
SELECT o.*, r.name
FROM PLACED_ORDER o, RESTAURANT r
WHERE o.RESTAURANT_ID = r.RESTAURANT_ID
AND o.DELIVERY_TIME > (SYSDATE - 30)
ORDER BY o.ORDER_EXT_ID DESC
Even when the application displays only the first few rows returned by the query,
this query can take several seconds to execute against a large database, which is
unacceptable for an interactive application. In addition, executing this query con-
sumes excessive database server resources, which limits scalability.
You can improve query performance in several ways. Some performance
improvements are done on the database server and do not require code changes.
For example, defining the appropriate indexes can improve performance dramat-
ically without having to change the
SQL
statements. There are, however, other
performance optimizations that require the
SQL
statements to be changed:
Using query optimizer hints
■
Using the
ROWNUM
pseudo column
■
Denormalizing the schema
■
Rewriting queries to avoiding inefficient features
■
If your application uses i
BATIS
or
JDBC
, then changing the
SQL
statements is easy
because you have complete control over them. But, as we will see in section 11.3, if
the
SQL
statements are generated by the persistence framework, then implement-
ing these optimizations can be difficult or even impossible. Let's look at each of
these query optimization techniques.
Search WWH ::
Custom Search