Display 100000 records on browser / multiple pages

Question

I would like to display 100000 records on browser / multiple pages with minimal impact on memory. ie Per page 100 records. I would like to move page back and forth. My doubts are
1. Can I maintain all the record inside the memory ? Is this good Idea ?

2) Can I make database connection/query for ever page ? If so how do write a query?

Could anyone please help me..

Answer 1

It's generally not a good idea to maintain so much records in memory. If the application is accessed by several users at the same time, the memory impact will be huge.

I don't know what DBMS are you using, but in MySQL and several others, you can rely on the DB for pagination with a query such as:

SELECT * FROM MyTable
LIMIT 0, 100

The first number after limit is the offset (how many records it will skip) and the second is the number of records it will fetch.

Bear in mind that this is SQL does not have the same syntax on every DB (some don't even support it).

Answer 2

I would not hold the data in memory (either in the browser or in the serving application). Instead I'd page through the results using SQL.

How you do this can be database-specific. See here for one example in MySql. Mechanisms will exist for other databases.