SQL Pagination discussion

[barrymavin]

I was talking with Hank a few days ago about pagination in various SQL databases as I'm planning to add pagination support into the Lianja grids so they can better handle millions of records (the Cloud Server already does this so it's desktop I'm referring to here).

Both Lianja and MySQL support the LIMIT clause.



select * from customers limit 0,50
select * from customers limit 50,50
select * from customers limit 100,50


PostgreSQL uses LIMIT/OFFSET. Lianja also supports this syntax.



select * from customers limit 50 offset 0
select * from customers limit 50 offset 50
select * from customers limit 50 offset 100


In the PostgreSQL documentation I noticed the following statement:



The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET can be inefficient.


In Lianja you can SET SMARTQUERY ON and it will extract rows that are cached by rowid. This improves performance in heavily loaded systems as the query is only re-evaluated if the tables referenced in the query have been updated since the query was initially executed. The nature of many web-based systems (and tables that are primarily static such as postal codes or currencies) are what I call WORM (Write Once Read Many), and thats what SMARTQUERY is good at handling.

So we then moved onto a discussion about MSSQL which I find very strange that such a popular database on Windows Server has such poor pagination support.

Hank gave me this link which I think you may find interesting.
http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/

Seems like a lot of work to satisfy a simple requirement. I'll let you draw your own conclusions.

Additionally we had some discussions about MSSQL Express and discovered that it only uses one CPU no matter how many you have in the machine and my understanding is that it is single threaded. Additionally it will use a maximum of 1GB of memory. It may be fine for development but it's not going to scale very well or handle 100's of concurrent users very well.

[HankFay]

I would add to the discussion my observation that pagination in a business app would be rarely useful for a business app that is properly (best practices) designed.

What we find is that power users (the ones who have to incorporate large amounts of data in decision-making -- in our instance buyers handling purchases for hundreds of stores on multiple geographic sites) do NOT want to look at large amounts of data; they want to look at an accurate summarization, and then drill down -- into a small amount of data.

So this is a feature with a limited audience, it seems to me. If the feature is limited to those backends that support it, so be it. Supporting SQL2012 makes sense, since the db supports doing so (in response to the need of web apps, btw). What we will tell customers is the simple truth: SQL Server doesn't support pagination in a reasonable manner until SQL2012.

Having queried these same customers, two things are apparent. First, the mobile apps they are requesting are not big data apps: they are useful, limited purpose apps (which we are currently creating in Lianja, hitting their existing data in MS SQL), where pagination is not required. Second, their heavy data hitters do all their work from the desktop, in Corporate HQ, and will continue to do so. And even with that, we have _never_, in the 25 years the company has been in business, had a request to make browses of huge data available. There's no business purpose to it.

Thanks for all the working you are doing in accommodating user's needs. I do think this is one place where a line has to be drawn, and that doing so will not limit the writing of business apps in Lianja.

thanks,

Hank