Using advanced query techniques - iBATIS in Action - page 130

G

the getOrderItemList mapped statement is executed to get the value of the
orderItemList property in the result map ResultOrderInfoMap .
In spite of the convenience this functionality offers, two issues can arise. First,
the creation of lists with many objects in them can turn into a massive memory
consumer. Second, this approach can cause major database I/O problems very
quickly due to a phenomenon known as the “N+1 Selects” problem, which we will
talk about in a moment. The i BATIS framework provides solutions to each of those
problems, but nothing can solve both at once.

C

Database I/O
Database I/O is one measure of how your database is being used, and is one of the
major bottlenecks in database performance. When reading or writing to a data-
base, the data has to be transferred from disk to memory or from memory to disk,
which are expensive operations in terms of time. Avoiding database I/O with cach-
ing will make your application faster, but this strategy can be problematic if not
used with caution. For more information on when and how to use the caching
mechanisms that i BATIS provides, see chapter 10.
To illustrate the database I/O problems you can encounter when using related
data, imagine a case where you have 1,000 Account s, each related to 1,000 Order s that
have 25 OrderItem s each. If you try to load all of that into memory, it would result
in the execution of over 1,000,000 SQL statements (one for the accounts, 1,000 for
the orders, and 1,000,000 for the order items) and the creation of around 25 million
Java objects—doing this will certainly get you a slap on the wrist from your system
administrators!

Looking at the N+1 Selects problem
The N+1 Selects problem is caused by trying to load child records that are related
to a list of parent records. So, if you run one query to get the parent records, and
there are some number, N , of them, then you have to run N more queries to get
the child records for the parent records, resulting in “N+1 Selects.”

Solutions to these problems
Lazy loading (which we cover in more detail in section 6.2.2) can mitigate some of
the memory problem by breaking the loading process into smaller, more manage-
able pieces. However, it still leaves the database I/O problem, because in the worst
case it will still hit your database just as hard as the non-lazy version did since it
still uses the N+1 Selects approach (which we'll solve in section 6.2.3) as it loads
the data. When we solve the N+1 Selects problem to reduce the database I/O ,