many records: create business objects 1 by 1 or all at the same time?

Question

I have a table with about 10.000 records (plus detail data in 11 tables). I want to display the data in a browsable form (1st, previous & next, last buttons).

I thought about retrieving all the records, putting the data into a collection of business objects and binding the form to the collection. Thinking about it, it came to my mind that it could take a while and could possibly result in a lot of memory being used ...

So maybe I should just retrieve the first record and get the next one when requested? What Do you think?

Answer 1

If the data is used frequently, but does not change often, you could consider caching it on the application level. If you retrieve the data on form level, a probably good compromise between speed and memory consumption would be to get e.g. 11 records (current, 5 previous and 5 next), and if the user goes past the retrieved records, get the next 5 again etc.

Answer 2

If you're not using an OR/M like nHibernate or iBatis, let me suggest that now. I use nHibernate - but I hear great things about iBatis. They have batching and lazy loading built in and configurable. Plus, the next time a situation like this arises, you'll have this functionality in minutes thanks to a pre-established flexible data access layer.

If you're not going to go the route of an OR/M, I'd suggest one-by-one fetching until performance becomes an issue - why complicate things unnecessarily upfront? If performance becomes an issue, then consider batch fetching and caching.