Superior Ajax Datagrid Paging

by Mike Levin SEO & Datamaster, 07/10/2006

So, I blogged about the Ajax Datagrid in HitTail both in the HitTail and Mike-Levin.com blogs. So far, there have only been three “groups” that have sort of spoken of the sticky wicket that is SQL + statelessness + arbitrary sorting + paging. And that is Richard Cowin and Bill Scott of OpenRico fame. They (or at least Bill) proclaims death to paging, in which they delve mostly into the issues of showing data zoom by as the user scrolls. Fascinating stuff, and I’m going to have to incorporate that into HitTail eventually. But I posted my question on how to combine those four factors to no avail.

The second is Scott Mitchell of 4GuysFromRolla, who touches on the topic in his ASP.NET Data Web Controls book, but again, tantalizingly mentions then brushes past the issue. I strongly get the impression that this is a solved problem, so long as you’re willing to use APIs that create open connections with cursors OR you use one-column sorting on a column with a unique constraint. But the entire system breaks down when you attempt to sort on say, FirstName, where many people have the same first name, and you therefore have a dilemma that no one will understand until you try it.

And that brings me to the third person who spoke of this issue to me, Gerard Bucas, of Great Valley Product fame during the heyday of the Amiga Computer. He was once the head of Commodore engineering until he got fed up at Commodore’s lagging pace of innovation, and started his own company. Since then, he took over Scala Multimedia, where I worked with him for my final 2 years there. Under his tutelage, I attempted to port my asp-based agile framework to ASP.NET, incorporating his philosophies. Unfortunately, the two ended up being mutually exclusive, which I did not have enough wisdom to understand until two years in. Gerard worked his magic on the Magic Framework with an underlying database that was all about indexes, with which his philosophies perfectly meshed. And it was very un-SQL-like.

Yet, the database principles he believes in can totally be used through the SQL API. It’s just tricky, and incompatible with most “frameworks” (such as .NET). But once you learn the trick, you get power that Web+SQL developers are unaccustomed-to. In fact, the difficulty of using these programming techniques leads to dependence on alternative API’s that are almost always inserted between SQL and the application layer. The API’s have the responsibility of grabbing a set of data and letting you page through it. But this is full of problems. First, you’re working on a subset of data. Second, you’re tying up resources on both the database and webserver. Typically, the webserver unbeknownst to the developer opens a persistent connection to SQL with a cursor interface, totally ruining any hope of the app being able to support thousands of simultaneous users. .NET tries to solve this with “disconnected” recordsets. But there is a better way.

Well, Gerard had an approach that he used before SQL was so popular, or the Web for that matter. And he insisted to me that it was possible under SQL and on the Web. I didn’t think so. And even if it was, it was so contrary to the way the rest of the world did this stuff, that it couldn’t be right. It called for an overly-chatty data channel and excessive page reloads (I thought). Well, Gerard kept after me until I worked my way through the problem, and reproduced his database navigation methods under SQL. He forced me to seek out and use Ajax-like techniques before Ajax (JSRS). And I have since updated it with the modern libraries. I can now page (or scroll) through SQL data using arbitrary sorting columns, even non-unique columns like FirstName, with no duplicates or gaps in paging, and with no more state being maintained than a tiny client-side cookie or JavaScript variable.

Combining this with Ajax then is the final step to astounding performance in paging through massive amounts of data on the Web. Even Google cops out at about 100 pages of results x 10 items per page (1,000). That’s a remarkably small dataset considering how much data is in the Google indexes. What if you could go on paging forever? Well, that’s basically what I’ve accomplished. And without any detrimental impact on performance. We’re currently navigating millions of records with the same performance as if it were dozens. And I have total belief that it will scale up to billions. And as soon as I see a drop in performance, I just throw a few more servers into the database cluster.

We’re using this for HitTail, a perfect application, because it allows real-time browsing of your search hits, like looking at a log file. But I can imagine many diverse applications in areas where Web developers have shied away from such solutions due to “old-school” wisdom. If you page forward and back, and there’s more data at the top, you’ll see it instantly. The data you’re surfing is the live data. Well, the combination of Ajax, cheap database clustering, and my (Gerard’s) radically alternative way of paging through data, it is not only doable, but provides superior performance with the real-time live data.

This is heavily on my mind now, because I’m reading James Surowiecki’s Wisdom of Crowds. Applications like Wikipedia show off this wisdom on the Internet. But when you throw Broadband, Ajax  and Social Networking into the picture, a whole new breed of group wisdom applications are now possible where you see the collaborative decisions of the world made right underneath of you, in real-time. I’m using the constant assault of data that comes from log file and search hit tracking as the fire in which this new application of technology is being forged. But it certainly isn’t going to stop with HitTailing.