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

In many SQL language, you have a notion of LIMIT (mysql, ...) or OFFSET (mssql). You can use this kind of thing to limit rows per page

Answer 3

It would not be a good idea, as you are making the browser executable hold all of that.

When I had something like this to do used javascript to render the page, and just made ajax calls to get the next page. There is a slight delay in displaying the next table, as you fetch it, but users are used to that.

If you are showing 100 records/page, use json to pass the data from the server, as javascript can parse it quickly, and then use innerHTML to put the html, as the DOM is much slower in rendering tables.

Answer 4

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.

Answer 5

Depends on the data. 100k int's might not be too bad if you are caching that.

T-SQL has SET @@ROWCOUNT = 100 to limit the amount of records returned.

But to do it right and return the total # of pages, you need a more advanced paging SPROC.

It's a pretty hotly dedated topic and there are many ways to do it.

Here's a sample of an old sproc I wrote

CREATE PROCEDURE Objects_GetPaged
(
    @sort VARCHAR(255),
    @Page INT,
    @RecsPerPage INT,
    @Total INT OUTPUT
)
AS
SET NOCOUNT ON

--Create a temporary table
CREATE TABLE #TempItems
(
    id INT IDENTITY,
    memberid int
)

INSERT INTO #TempItems (memberid) 
SELECT Objects.id 
FROM Objects
ORDER BY CASE @sort WHEN 'Alphabetical' THEN Objects.UserName ELSE NULL END ASC,
      CASE @sort WHEN 'Created'  THEN Objects.Created ELSE NULL END DESC,
      CASE @sort WHEN 'LastLogin' THEN Objects.LastLogin ELSE NULL END DESC


SELECT @Total=COUNT(*) FROM #TempItems

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)


SELECT *
FROM #TempItems 
INNER JOIN Objects ON(Objects.id = #TempItems.id)
WHERE #TempItems.ID > @FirstRec AND #TempItems.ID < @LastRec
ORDER BY #TempItems.Id

Answer 6

1) No, having all the records in memory kind of defeats the point of having a database. Look into having a scrollable result set, that way you can get the functionality you want without having to play with the SQL. You can also adjust how many records are fetched at a time so that you don't load more records than you need.

2) Db connections are expensive to create and destroy but any serious system will pool the connections so the impact on performance won't be that great.

If you want to get a bit more fancy you can do away with pages altogether and just load more records as the user scrolls through the list.

Answer 7

As mentioned by others here, it is not a good idea to store a large list of results in memory. Query for results for each page is certainly a much better approach. To do that you have two options. One is to use whatever the database specific features your DBMS provides for targeting a specific subsection of results from a query. The other approach is to use the generic methods provided by JDBC to achieve the same effect. This keeps your code from being tied to a specific database:

// get a ResultSet from some query
ResultSet results = ...
if (count > 0) {
    results.setFetchSize(count + 1);
    results.setFetchDirection(ResultSet.FETCH_FORWARD);
    results.absolute(count * beginIndex);
}
for (int rowNumber = 0; results.next(); ++rowNumber) {
    if (count > 0 && rowNumber > count) {
        break;
    }
// process the ResultSet below
...
}

Using a library like Spring JDBC or Hibernate can make this even easier.

Answer 8

I would recommend that you choose using CachedRowSet .

A CachedRowSet object is a container for rows of data that caches its rows in memory, which makes it possible to operate without always being connected to its data source.

A CachedRowSet object is a disconnected rowset, which means that it makes use of a connection to its data source only briefly. It connects to its data source while it is reading data to populate itself with rows and again while it is propagating changes back to its underlying data source.

Because a CachedRowSet object stores data in memory, the amount of data that it can contain at any one time is determined by the amount of memory available. To get around this limitation, a CachedRowSet object can retrieve data from a ResultSet object in chunks of data, called pages. To take advantage of this mechanism, an application sets the number of rows to be included in a page using the method setPageSize. In other words, if the page size is set to five, a chunk of five rows of data will be fetched from the data source at one time. An application can also optionally set the maximum number of rows that may be fetched at one time. If the maximum number of rows is set to zero, or no maximum number of rows is set, there is no limit to the number of rows that may be fetched at a time.

After properties have been set, the CachedRowSet object must be populated with data using either the method populate or the method execute. The following lines of code demonstrate using the method populate. Note that this version of the method takes two parameters, a ResultSet handle and the row in the ResultSet object from which to start retrieving rows.

CachedRowSet crs = new CachedRowSetImpl();
crs.setMaxRows(20);
crs.setPageSize(4);
crs.populate(rsHandle, 10);

When this code runs, crs will be populated with four rows from rsHandle starting with the tenth row.

On the similar path, you could build upon a strategy to paginate your data on the JSP and so on and so forth.