Thursday, December 01, 2005

Implementing the Value List Page Iterator Pattern

UPDATE: Brad Long's Article is now found here.

I have come across a situation that warrants the use of the "Value List Page Iterator" pattern. First off, the official name is the "Value List Handler," previously known as the "Page-by-Page Iterator." The pattern is part of the Core J2EE Patterns from Sun. I have chosen to use the name "Value List Page Iterator" because the other two names miss the main points: iterating over a value list grouped into pages. The context is situations where a query can return more results than can be presented on one screen, the solution is to allow the user to page through results, like with your typical Web search engine.

The code part of the pattern is simple enough, and the data access is nothing fancy--as long as the query is pretty quick, and/or it is acceptable for the app to keep all the results in memory. In my case, the query takes minutes and can return potentially millions of results. I need something that "slices" the data from the db into manageable chunks. Brad Long of Oracle has written up a pretty comprehensive treatment of database slicing strategies and offers his own variant of the pattern. Brad nails down the best way to do a slicing query, which I have tweaked:

SELECT *
FROM (SELECT ROWNUM RNUM, INLINE_VIEW.* FROM

(

your-select

your-where

and key-or-order-by-field > lower-boundary

[order by order-by-field]

) INLINE_VIEW

)
WHERE RNUM < max-range


My current problem is that the row-limited (sliced) query takes just as long as the full query. So unless I can figure out some way to effectively cache the query in the database, I am still faced with either sticking all of the results in memory, or taking the performance hit of executing the query multiple times. I am also thinking about a hybrid solution, where I guess at the most likely initial pages and get those at once, then re-execute the query if the user navigates outside the initial results.

More to come...