Fog Creek Software
Discussion Board




Scrolling in web based applications

We are replacing a Powerbuilder app with web enabled java based system. One area where we are having trouble is supporting scrolling large amounts of data. In the PB app we could send all the data requested to the app and let the user scroll as needed.

In the new app we have to manage the scrolling at the server which currently means we retrieve all the data and send  20 or so rows to the user. When he scrolls we do the same process all over and send the next 20 rows.  The extra work on the database is about 10X.

What solutions are there to control scrolling on web pages?  (We do not want to save any state on the server.)

John McQuilling
Saturday, February 01, 2003

Do you mean database paging?

In some databases like PostgreSQL and MySQL you can retreive certain rows in a query by adding the words LIMIT and OFFSET to the SQL.

Matthew Lock
Saturday, February 01, 2003

or push the data to the client. that's what databinding is for. you can use xml for databinding.

of course you have to specify the problem you're solving. slow data connection so you don't want to send all the data? etc.

mb
Sunday, February 02, 2003

The problem we have is that three months of the data can be 200,000 rows.  We are retriving the data and sorting it before we determine which rows to send back.  We tried just sending all the data back but sometimes the browsers crash with that many rows.

John McQuilling
Sunday, February 02, 2003

As was said above, the LIMIT and OFFSET style keywords are used for this kind of web-based scrolling. 

If your database doesn't have those keywords, then you should be caching the query.  It's probably still going to be painful; since on each request you have load the entire cached query from disk -- but it's better than retrieving all the rows from the database on each request.

Wayne Venables
Sunday, February 02, 2003

Couple of questions about your situation:
Does the data you're needing to scroll change much?
How important is it in your situation that the user see the absolutely most recent version of the data, for example, would it be acceptable if the data displayed were maybe current to within the last minute?

anonQAguy
Monday, February 03, 2003

If your db doesn't support LIMIT and OFFSET check out the TOP command in SQL.

I think only some MS databases support it, but it can be handy for showing the first page of data. Often you will find that most visitors only view the first page of data and so just retreiving the TOP 10 for example can save a lot of database work.

As for storing the state on a web application, you can do that by putting info in the querystrings of the Next and Previous Page buttons. Eg.

<A HREF="query.pl?page=1">Previous</A>

<A HREF="query.pl?page=3">Next</A>

Then build your page (in this case query.pl) to take "page" as a parameter and use that to limit what the page shows.

Matthew Lock
Monday, February 03, 2003

Why do you need to show the user 200,000 rows?  A human cannot possibly work with that amount of data without further filtering it?

The best solution may be to change your user interface to force the user to be specify more filter criteria.

Of course, depending on the project you may not have that kind of flexibility.  In that case as others have suggested using a limit on the query is the best way to go.

Make the common case as painless for the user as possible, and don't worry too much about the extreme cases.  You don't want users paging through tens of thousands of rows, so if it goes slow that may actually be a good thing since it discourages the user from doing it :)

Rob Walker
Monday, February 03, 2003

you can write the data into javascript objects and page to html from there. Performance-wise, this is only good into the low to mid hundreds of records depending on row size.  This also gives you a client-side  column sort.

fool for python
Monday, February 03, 2003

FYI to anyone trying to do recordset paging in MSSQL -

I've developed a Stored Procedure for paging in SQL Server.  You can get it here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19317

Links to other paging options are also covered in this forum thread.  Hope someone finds this useful.

FDWhitlark
Monday, February 03, 2003

*  Recent Topics

*  Fog Creek Home