Best Practices for displaying large lists

Question

Are there any best practices for returning large lists of orders to users?

Let me try to outline the problem we are trying to solve. We have a list of customers that have 1-5,000+ orders associated to each. We pull these orders directly from the database and present them to the user is a paginated grid. The view we have is a very simple "select columns from orders" which worked fine when we were first starting but as we are growing, it's causing performance/contention problems. Seems like there are a million and one ways to skin this cat (return only a page worth of data, only return the last 6 months of data, etc.) but like I said before just wondering if there are any resources out there that provide a little more hand holding on how to solve this problem.

We use SQL Server as our transaction database and select the data out in XML format. We then use a mixture of XSLT and Javascript to create our grid. We aren't married to the presentation solution but are married to the database solution.

Answer 1

My experience.

1. Always set default values in the UI for the user that are reasonable. You don't want them clicking "Retrieve" and getting everything.
2. Set a limit to the number of records that can be returned.
3. Only return from the database the records you are going to display.
4. If forward/backward consistencency is important, store the entire results set from the query in a temp table and return just the page you need to display. When paging up/down retrieve the next set from the temp table.
5. Make sure your indexs are covering your queries.
6. Use different queries for different purposes. Think "Open Orders" vs "Closed Orders". These might perfrom much better as different queries instead of one generic query.
7. Set parameter defualts in the stored procedures. Protect your query from a UI that is not setting reasonable limits.

I wish we did all these things.

Answer 2

I'd recommend doing some profiling to find the actual bottlenecks. Perhaps you have access to Visual Studio Profiler? http://msdn.microsoft.com/en-us/magazine/cc337887.aspx There are plenty of good profilers out there.

Otherwise, my first stop would be pagination to bring back less records from the db, which is easier on the connection and the memory footprint. Take a look at this (I'm assuming you're on SQL Server >= 2005) http://www.15seconds.com/issue/070628.htm

Answer 3

I"m not sure from the question exactly what UI problem you are trying to solve.

If it's that the customer can't work with a table that is just one big amorphous blob, then let him sort on the fields: order date, order number, your SKU number, his SKU number maybe, and I guess others,too. He might find it handy to do a multi-column stable sort, too.

If it's that the table headers scroll up and disappears when he scrolls down through his orders, that's more difficult. Read the SO discussion to see if the method there gives a solution you can use.

There is also a JQuery mechanism for keeping the header within the viewport.

HTH

EDIT: plus I'll second @Iain 's answer: do some profiling.
Another EDIT: @Scott Bruns 's answer reminded me that when we started designing the UI, the biggest issue by far was limiting the number of records the user had to look at. So, yes I agree with Scott that you should give the user some way to see only a limited number of records right from the start; that is, before he ever sees a table, he has told you a lot about what he wants to see.

Answer 4

Stupid question, but have you asked the users of your application for input on what records that they would like to see initially?