Re: reasons for modperl declines? ... pagination

Guido Brugnara <[email protected]> Thu, 8 Aug 2024 16:21:09 +0200
Newsgroups	gmane.comp.apache.mod-perl
Message-ID	<[email protected]>
This is a multi-part message in MIME format.
--------------x3FVg02UVM0SjjJ2Wwvtl4LQ
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Il 08/08/24 15:35, Vincent Veyron ha scritto:
> On Thu, 8 Aug 2024 13:11:18 +0200
> Guido Brugnara<[email protected]>  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


--------------x3FVg02UVM0SjjJ2Wwvtl4LQ
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">Il 08/08/24 15:35, Vincent Veyron ha
      scritto:<br>
    </div>
    <blockquote type="cite"
      cite="mid:[email protected]">
      <pre class="moz-quote-pre" wrap="">On Thu, 8 Aug 2024 13:11:18 +0200
Guido Brugnara <a class="moz-txt-link-rfc2396E" href="mailto:[email protected]">&lt;[email protected]&gt;</a> wrote:

Hi Guido,

</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">
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).

</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
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?

</pre>
    </blockquote>
    <br>
    <span _d-id="85196" class="--l --r sentence_highlight">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.<br _d-id="84052">
    </span>
    <p><span _d-id="85198" class="--l --r sentence_highlight">Then I run
        the queries using a subset of cached PKs like ... </span><span
        _d-id="85201" class="--l sentence_highlight">WHERE IN (pkn,
        pkn+1, pkn+2, ...) that returns all columns, but only che
        current page to display.</span></p>
    <p><span _d-id="85201" class="--l sentence_highlight">Subsequent
        request will use the cached keys until they are in cache, or it
        call [*] with different range .. and so on. <br>
      </span></p>
    <span _d-id="85201" class="--l sentence_highlight">bye<br>
      gdo<br>
    </span>
    <p><br>
    </p>
  </body>
</html>

--------------x3FVg02UVM0SjjJ2Wwvtl4LQ--