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