How to display database query results of 100,000 rows or more using HTML?

Question

We are rewriting the site used by one of our customers. User traffic on it is very low, less than 100 unique visitors per week. This is basically a simple interface to their data in our databases. It allows them to query and filter different data sets.

We are rewriting the site in Python, reusing the same Oracle database in which the data is currently included. The current version is written in an old, old version of Coldfusion. One of the things Coldfusion does well is that it displays a ton of database records on one page. It is capable of displaying hundreds of thousands of lines simultaneously without browser crashes. It uses a Java applet, and it looks like the contents of the strings are possibly compressed and passed through HTML or something like that. HTML has a large data block, but it is not displayed - it is simply displayed using the Java applet.

I tried several Javascript solutions, but they all depend on whether the data will be present in the HTML table or something like that. This makes browsers freeze and shut down.

Does anyone know any solutions to this situation? Our client loves the ability to scroll through all this data without clicking the "next page" link.

Thanks Ryan

Answer 1

I did what you describe using the following (which works very well):

jQuery datatables

This allows you to do “scroll selection”, so you can turn off pagination arrows in favor of “forever” scroll.

Answer 2

Try jquery scrolling.

Instead of scrolling the image, you need to scroll through the data.

You must enter data in divs instead of images.

http://www.smoothdivscroll.com/#quickdemo

It should work. I wish.

In any case, you have a great client :-)

Something related to your Q

http://www.9lessons.info/2009/07/load-data-while-scroll-with-jquery-php.html

http://api.jquery.com/scroll/

Answer 3

I am using Open Rico LiveGrid in a project to display a table with thousands of rows per page as an endless scroll table. So far, it has worked very well. The table retrieves data by query when scrolling through rows. Parameters are sent as simple GET parameters, and the answer you need to create on the server is plain XML. It should be possible to implement a data backend for Rico LiveGrid in Python.

Answer 4

Most people in this case will use the framework. The best documented and most popular structure in Python is Django. It has good database support (including Oracle), and you will have the easiest time to get help using it, since there is such an active Django community.

You can try other frameworks, but if you are tied to Python, I would recommend Django.

Of course, Jython (if this is an option) will make your work very simple. You can use the existing Java structure and use Jython to create the interface (and continue to use the Java applet and the Java classes and Java server).

The memory problem is interesting; I would be interested to know what you came up with.

Answer 5

Have you tried jqGrid ? Sometimes it can be a buggy, but overall it is one of the best JavaScript grids. It is quite effective when working with large data sets. It also has a feature whereby the grid retrieves data asynchronously in chunks, but allows continuous scrolling. It just asks for more data when the user scrolls to it.

Answer 6

I did something similar before and successfully executed a YUI data table in conjunction with Django

http://developer.yahoo.com/yui/datatable/

This gives you column sorting, pagination, scrolling, etc. It also allows the use of various data sources, such as JSON or XML.