Display 100,000 entries in a browser / multiple pages

Question

I would like to display 100,000 entries in a browser / multiple pages with minimal memory impact. those. for 100 entries. I would like to move the page back and forth. My doubts: 1. Can I save the entire record in memory? Is that a good idea?

2) Can I make a database connection / request to the eternal page? If so, how to write a request?

Can anyone help me ..

Answer 1

As a rule, it is not recommended to store as many records in memory. If the application is simultaneously accessible to several users, the memory effect will be huge.

I do not know which DBMS you use, but in MySQL and some others you can rely on the database for pagination using a query, for example:

SELECT * FROM MyTable LIMIT 0, 100 

The first number after the limit is the offset (the number of records that it will skip), and the second is the number of records that it will retrieve.

Keep in mind that this SQL does not have the same syntax for each database (some do not even support it).

Answer 2

I will not store data in memory (in a browser or in a serving application). Instead, I view the results using SQL.

How you do this depends on the specific database. See here for one example in MySql. Mechanisms will exist for other databases.

Answer 3

1) No, all records in memory hit the point of the database. Look for a scrollable result set so you can get the functionality you need without having to play with SQL. You can also configure how many records are selected at a time so that you do not upload more records than you need.

2) Db connections are expensive to create and destroy, but any serious system combines connections, so the impact on performance will not be that big.

If you want to get a little more imagination, you can completely delete the pages and just load more entries when the user scrolls through the list.

Answer 4

It will not be a good idea, since you are doing the browser executable all this.

When I had something like this to use javascript to render the page, and just made ajax calls to get the next page. There is a slight delay in displaying the following table when you retrieve it, but users are using it.

If you are showing 100 entries / pages, use json to transfer data from the server, since javascript can parse it quickly, and then use innerHTML to host html, since the DOM is much slower in rendering tables.

Answer 5

As already mentioned, it’s nice to keep a large list of results in memory. Querying for results for each page is certainly much better. You have two options for this. One of them is to use any database-specific functions that the DBMS provides for targeting a specific subsection of the query results. Another approach is to use the common methods provided by JDBC to achieve the same effect. This does not allow you to bind your code 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 6

In many SQL languages, you have the concept of LIMIT (mysql, ...) or OFFSET (mssql). You can use such things to limit the lines on the page.

Answer 7

It depends on the data. 100k int might not be too bad if you cache this.

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

But in order to do it right and return the total number of pages, you need a more advanced SPROC swap.

This is a pretty hot topic, and there are many ways to do this.

Here is a sample of the 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 8

I would recommend you choose CachedRowSet.

The CachedRowSet object is a container for data rows that caches its rows in memory, which allows you to work without a permanent connection to the data source.

The CachedRowSet object is an unrelated set of rows, which means that it uses a short connection to the data source. It connects to the data source while it reads the data to fill itself with rows and again while it passes the changes back to the original data source.

Because the CachedRowSet object stores data in memory, the amount of data it can hold at any given time is determined by the amount of available memory. To get around this limitation, a CachedRowSet object can retrieve data from a ResultSet in pieces of data called pages. To use this mechanism, the application sets the number of rows that will be included in the page using the setPageSize method. In other words, if the page size is set to five, a fragment of five rows of data will be extracted from the data source. An application can also set the maximum number of rows that can be selected at a time. If the maximum number of rows is set to zero or the maximum number of rows is not specified, then the number of rows that can be selected at a time is not limited.

After the properties have been set, the CachedRowSet object must be populated with data using either the method or the execute method. The following lines of code demonstrate the use of this method. Note that this version of the method accepts two parameters: the ResultSet descriptor and the string in the ResultSet object from which the string search begins.

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

When this code is run, crs will be populated with four lines from rsHandle, starting from the tenth line.

On the same path, you can rely on a strategy to break down your data into JSPs, etc. etc.