Implementing dynamic paged queries - POJOs in Action: Developing Enterprise Applications with Lightweight Frameworks - page 418

Tuning the queries
The fourth and final way to improve the performance of some SQL queries is to
rewrite them to use more efficient constructs. This is a complicated topic that is
described in numerous topics including SQL Tuning [Tow 2003]. Examples of
what you can do to improve the performance of your query include eliminating
SQL functions from the SELECT statement's WHERE clause and replacing a
complex SQL statement with the UNION ALL of two simpler ones. Rewriting the
SQL statement is only possible if the application executes SQL directly. If it queries
the database using the persistence framework's object query mechanism, then you
have much less control over the SQL that is generated.
It is also important to take into account database performance issues when
designing the UI. Examples of UI features that can cause database performance
problems include:

Case-insensitive substring searches, which, even though they appear to offer
the user a lot of flexibility, can be quite inefficient

■

Displaying the number of rows that match the query, which requires the
database process the entire result set even though only the first few rows are
displayed

■

An excessively large number of search and sort options, which requires
many indexes to be defined

■

Ideally, the user interface should only support searches that can be implemented
using efficient database queries.
Now that we have reviewed the different ways to improve the performance of a
SQL SELECT statement, let's look at the implementation of a DAO that uses i BATIS
to execute queries.

11.2 Implementing dynamic paged queries with iBATIS

The frameworks that you can use to implement the persistence layer include i BA-
TIS , Hibernate, and JDO . In this section you will learn how to implement dynamic
paged queries in an application that executes SQL using i BATIS . You will see an
example of how using i BATIS to construct the query simplifies the code signifi-
cantly. In section 11.3 we will see how to use JDO and Hibernate to implement
dynamic paged queries.
In an i BATIS application, the query that retrieves the data displayed by the
search screen is executed by a DAO . The DAO typically defines a find method that
takes parameters that specify the search criteria and the range of rows to return.