Il 08/08/24 15:35, Vincent Veyron ha scritto:
On Thu, 8 Aug 2024 13:11:18 +0200
Guido Brugnara<g...@leader.it> wrote:
Hi Guido,
Special feature of the project is the use of HTML::Mason also for SQL
code generation templates for efficient paging of very large recordset
(even millions of rows).
Excuse me for hijacking this thread, but I'm curious how this works?
I have this problem in my accounting application, which I solved by adding
OFFSET $offset LIMIT 1000
to the query, plus links in the web page that point to the next/previous offset
(LIMIT is set at 1000 for browsers, which slow down after that).
It works fine for a few thousand rows, it serves the required slice very fast;
but I do have to fetch the whole recordset every time so I suspect fetching a
million rows to only return 1000 won't be efficient.
So, how do you a page an ordered recordset of a million rows? do you use a
cursor?
I evaluate the query but returning only Primary Keys [*] using
OFFSET/LIMIT limiting to a multiple of rows to display but not too
large, which I save in cache.
Then I run the queries using a subset of cached PKs like ... WHERE IN
(pkn, pkn+1, pkn+2, ...) that returns all columns, but only che current
page to display.
Subsequent request will use the cached keys until they are in cache, or
it call [*] with different range .. and so on.
bye
gdo
