Re: reasons for modperl declines? ... pagination

--0000000000004682cb061f444ffa
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

First one is expensive and s cond is inefficient. Instead get the max and
min and split the numbers into appropriate batch. So if it is 345 to 25636
then 345 - 1345, 1346-2345 and so on and so forth. Why is the second querry
inefficient because the IN clause is much slower than doing a between on
the primary key

On Thu, Aug 8, 2024, 9:23=E2=80=AFAM Guido Brugnara <[email protected]> wrote:

> Il 08/08/24 15:35, Vincent Veyron ha scritto:
>
> On Thu, 8 Aug 2024 13:11:18 +0200
> Guido Brugnara <[email protected]> <[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 addin=
g
>
> 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 fe=
tching 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 i=
t
> call [*] with different range .. and so on.
> bye
> gdo
>
>
>

--0000000000004682cb061f444ffa
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<p dir=3D"ltr">First one is expensive and s cond is inefficient. Instead ge=
t the max and min and split the numbers into appropriate batch. So if it is=
 345 to 25636 then 345 - 1345, 1346-2345 and so on and so forth. Why is the=
 second querry inefficient because the IN clause is much slower than doing =
a between on the primary key </p>
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu=
, Aug 8, 2024, 9:23=E2=80=AFAM Guido Brugnara &lt;<a href=3D"mailto:gdo@lea=
der.it">[email protected]</a>&gt; wrote:<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex=
"><u></u>

 =20
   =20
 =20
  <div>
    <div>Il 08/08/24 15:35, Vincent Veyron ha
      scritto:<br>
    </div>
    <blockquote type=3D"cite">
      <pre>On Thu, 8 Aug 2024 13:11:18 +0200
Guido Brugnara <a href=3D"mailto:[email protected]" target=3D"_blank" rel=3D"no=
referrer">&lt;[email protected]&gt;</a> wrote:

Hi Guido,

</pre>
      <blockquote type=3D"cite">
        <pre>
Special feature of the project is the use of HTML::Mason also for SQL=20
code generation templates for efficient paging of very large recordset=20
(even millions of rows).

</pre>
      </blockquote>
      <pre>
Excuse me for hijacking this thread, but I&#39;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 of=
fset (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 fa=
st; but I do have to fetch the whole recordset every time so I suspect fetc=
hing a million rows to only return 1000 won&#39;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>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>
    </span>
    <p><span>Then I run
        the queries using a subset of cached PKs like ... </span><span>WHER=
E IN (pkn,
        pkn+1, pkn+2, ...) that returns all columns, but only che
        current page to display.</span></p>
    <p><span>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>bye<br>
      gdo<br>
    </span>
    <p><br>
    </p>
  </div>

</blockquote></div>

--0000000000004682cb061f444ffa--