Best approach for a sortable table with lots of data

Question

In our web application, search results are displayed in sortable tables. The user can click on any column and sort the result. The problem is several times, the user performs a wide search and gets a lot of returned data. To make sorting easier, you probably need all the results that take a lot of time. Or I can get a few results at a time, but sorting won't actually work well. What is the best practice for displaying sortable tables that can contain a lot of data?

---

Thanks for all the tips. I will definitely go through them.

We use an existing JavaScript structure that has a sortable table; “Many” results mean hundreds. The problem is that our users are on some remote node, and the big delay is the network time to send / receive data from the data center. Sorting data from the database side and sending only one page of results at a time is nice; but when the user clicks on any column heading, another circular movement is performed, which always adds 3-4 seconds.

Well, I think it could be a network command problem :)

Answer 1

Using swap sorting at the database level is the right answer. If your query returns 1000 rows, but you only show the user 10 of them, there is no need to send another 990 over the network.

Here is a mysql example. Let's say you need 10 rows, 21-30, from the "people" table:

  SELECT * FROM people LIMIT 21, 10 

Answer 2

You must be paging on the database server. For instance. on SQL 2005 and SQL 2008 there are swap methods. I would suggest looking at the swap options for any system you're looking at.

Answer 3

What database are you using since SQL 2005 has a good swap option and up using ROW_NUMBER so you can pager on the server. I found this nice one on the blog of Christian Darie

For example, this procedure is used to display products in a category. You simply transfer the number and quantity of products you need on the page, etc.

CREATE PROCEDURE GetProductsInCategory (@CategoryID INT, @DescriptionLength INT, @PageNumber INT, @ProductsPerPage INT, @HowManyProducts INT OUTPUT) AS -- declare a new TABLE variable DECLARE @Products TABLE (RowNumber INT, ProductID INT, Name VARCHAR(50), Description VARCHAR(5000), Price MONEY, Image1FileName VARCHAR(50), Image2FileName VARCHAR(50), OnDepartmentPromotion BIT, OnCatalogPromotion BIT) -- populate the table variable with the complete list of products INSERT INTO @Products SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID), Product.ProductID, Name, SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion FROM Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID WHERE ProductCategory.CategoryID = @CategoryID -- return the total number of products using an OUTPUT variable SELECT @HowManyProducts = COUNT(ProductID) FROM @Products -- extract the requested page of products SELECT ProductID, Name, Description, Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion FROM @Products WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage AND RowNumber <= @PageNumber * @ProductsPerPage 

Answer 4

You can sort on the server. AJAX will eliminate the need for a full upgrade, but there will still be a delay. Parties, databases, as a rule, are sorted very quickly.

Answer 5

In these situations, I use SQL Server-side methods that not only use the database for sorting, but also use custom paging to ONLY return the required records.

It hurts a little at first, but after that the performance is amazing!

Answer 6

How big is the “lot” of data? Hundreds of lines? Thousands?

Sorting can be done painlessly using Mochikit Sortable Tables . However, if the data takes a lot of time to sort (most likely, a second or two [or three!]), You may want to give the user a visual signal that something is happening, and the page does not just freeze. For example, tap the screen (a la Lightbox) and display a “sorting” of the animation or text.