Background
For many years the Spirit interface has used a very cute feature of Microsoft Internet Explorer called Databinding to manage the display of large-ish datasets of say >500 rows.
Databinding basically allows you to bring a dataset into an html page as an xml object. You then bind the xml to a single row table and an ActiveX component takes care of rendering one row for each tag of XML and fills each cell with the value of a child cell.
On the whole this works pretty well; you can nest tables to produce groupings, use XSLT to do client-side sorting of the XML ( which propogates automatically to the table ) and paginating your data's pretty straight forward....
... but it is a bit limited. Why? Well partly the size of the data partly because of the parsing overhead of XML but largely because it's IE only.
Alternatives
So what's the alternative? You have to manage the binding of the data yourself! And that's what this article is really about!
Below is the first alpha of the data-table we're building to replace XML databinding in Spirit and one it's in a reasonable state we intend to properly release it for others to use.
Other motivations
This is also not just about the client-side model. There are other really important considerations at play here. Our motivations are as follows:
- Minimise work: do as little work as possible to produce the best possible result...
- Database load: minimise the work the database has to do in order to serve the requirements of representation of [large] sets of data to the client. Particularly things like sorts ( ORDER BY ) where table scans happen.
- Bandwidth/speed: minimise the sheer volume of data being shifted across the network.
- Natural cacheing: maximise the potential of cacheing. Under HTTP standard client-side cacheing, under HTTPS the potential for cacheing serverside but outside the application and particularly away from the database.
- User experience: maximise the user experience, in terms of speed, visibility and usability of data. Personally I don't think "Showing 1-50 of 123'456 results" is good enough much of the time... so allowing the user to quickly pan across the dataset, sort, group, add/remove columns etc. But not at the expense of the above three points.
- Security & functionality: We mustn't forget either functionality ( record-level-locking, record-updating ) or security ( access to records ) in everything we're doing..
Basic Premise
The basic premise for building the table is as follows:
- What records are we showing? Load the recordset, but as little as possible detail about them, in as fast a time possible. So basically just pull the ID's out of the database.
- What range are we looking at? Load the data, as simply as possible also, for the records we're actually looking at.
We then wait for user interaction before doing anything else.
- Scroll: When the user scrolls, we re-asses the range we're looking at at grab any records we're missing.
- Sort: When the user wants the data sorted we ask the database to re-assess the list of IDs we've got ( because only the database has full visability of the criterea of the sort )
Example
Here's an example. Just hit "Go" to load a few thousand record dataset.. [ I wouldn't try it in Opera of Safari just yet! ;-) ]
I've been having a bit of fun because our own CSS on this page is interfering! There's an isolated and extended demo here.
You can also, if you like sort by forename or surname, change the fontsize to bigger or smaller, make the table bigger, or grow the size of a column...
... lastly, if you're using Firefox - try gently reloading the page.... you should reap the benefit of 304s...
There's obiously a lot still to do. This is merely a proof of concept, but we think it's starting to show potential as a replacement for IE specific databinding...
Write a comment.