I ran into this same problem with Hive Engine Tools and now Hive Analytics.
The easy solution is fetch by ID then use last id rather than offset pagination.
Right now Hive Analytics just uses offset, but when I refactor it, it will pull entire list then use server side sorting from cache.
Thanks for mentioning this method. It sped up the smaller tokens tremendously and moderately improved the mid-sized queries, the large token counts still take a long time, but it is substantially better.
Right now Hive Analytics just does offset and gets the top 500 by balance, but if you sort by staked, it will only sort among those 500 user which are not the top 500 staked. This will be resolved later as the main point was for balance rich list.
Another solution is making your own API against mongo, which is what I was planning on doing for some complex reports.
Absolutely, for the reporting you plan on doing that would be the best method, because then you can get real creative with the queries vs api calls. My simple richlist view doesn't need that level of control.
My plan is to pull the ENTIRE token list into redis cache, then can sort from there.
