A better datagrid with Ajax and OpenRico
by Mike Levin SEO & Datamaster, 07/26/2005
Ajax and rapid re-querying of the original SQL source makes new data paging and scrolling user interfaces possible to the average web developer. Some developers, such as Richard Cowin see this, and advocate keeping the client thin. I believe this is implying that we forgo a web browser’s ability to cache XML data locally, be manipulated such as sorted and edited, then re-uploaded to the original source – sometimes requiring complex win/loss programming to see whose updates take. Better to work on all the live data, all the time. The point is countered here with an argument that in some cases, sophisticated Web clients can be more responsive by getting rid of latency issues.
The advantages of doing away with cached record sets either at the client or the server is huge. But it requires such a shift in thinking, and is so anti-SQL in nature, that I doubt many people will overcome it. When I first tackled the topic, I encountered the primary key sorting problem. The difficulty arises from sorting on anything that is not a unique value, because gaps and dupes can occur. You never have to think about it when you use APIs that cache SQL results, then implement their own paging. It’s a really sticky problem because of hierarchical sorting and the lack of a “from Y number of records starting from X” interface. Alternative cursor-based API’s to SQL are no good, because they all keep open connections and create temporary tables and kill scaling. I exhaustively worked out the solution, with the help of a mentor. The “Top” keyword provides part of the answer (implementations vary per SQL platform). But an actual answer is buried in the documentation of my previous attempt. Its surprising how little discussion there is regarding paging through SQL data without creating caching or temp tables.
API such tools as Microsoft’s Active Data Object (ADO), which provides the very un-SQL-like behavior of paging through results, kill application-scaling. Why? Because the data is cached on a per-user basis. If it goes on a server, then it’s the number of records cached times the number of concurrent users. It can bog down the server resources. So, ASP.Net’s solution was disconnected record sets cached locally (in the Web Browser). But this has its own problems, not the least of which are complexity, browser-dependence, platform dependence for both the server and development environment. Ajax provides a better way. Some people argue that because you don’t have a cached subset of your data, implementing features such as select all on a list of items becomes more worth it. But I think it is a worthwhile trade-off.