Performance question for retrieving data for pagination

[wonkty wang]

Hi guys,

I got a general question about pagination. I implemented an interface to retrieve some data from the db and to display them as 20 per page.

This could be done by

Either by retrieving all the data and store them in a data structure like ArrayList for example

Or retrieving them from the database as 20 per time.

My question is which way is better.

Obviously, the former way is better if the retrieved data is heaps to store in ArrayList as the size of ArrayList is hugh which takes up memory.
However, the second way, open too many connections because each clicking on the pagination will be one retrieve to the database also. Is it this overhead?

Can someone please advise?

Thanks.

[steve souza]

In the past I have coded this as a compromise between the 2 extremes. I brought back a buffer of the data. Say 10 pages, and if then i would refresh the buffer if the user went past 10. Which method is the most effective depends on query speed, memory constraints and how much data your user pages through.