Re: reasons for modperl declines? ... pagination

Guido Brugnara <[email protected]> Sat, 10 Aug 2024 08:26:01 +0200
Newsgroups gmane.comp.apache.mod-perl
Message-ID <[email protected]> 
This is a multi-part message in MIME format.
--------------uI6Q18uIycziHXYvVgHV3xo0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

I apologize that we are using this mail-list dedicated to mod_perl.

Normally the page to be returned to the frontend for display has a few 
rows (tens) and therefore perform Join with a table of keys or use IN 
(...) m but also key = X OR key = Y OR ... makes little difference.
In my case the recordset can be the result of JOIN (even more than one) 
with WHERE and ORDER ... all handled by the framework.

I have not found more efficient method.

gdo

Il 10/08/24 08:11, Mithun Bhattacharya ha scritto:
>
> True - it will not return enough results if it is a sparse matrix. I 
> might be biased because in my work it is ok to give less results if it 
> is significantly faster.
>
> If exact numbers are required and the count is significantly higher 
> then I would suggest keeping the select on primary keys but switch 
> from a IN clause to a temporary table and do a join.
>
>
> On Sat, Aug 10, 2024, 12:54 AM Guido Brugnara <[email protected]> wrote:
>
>     Il 09/08/24 20:28, Mithun Bhattacharya ha scritto:
>>     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
>
>
>     Your method works only if the recordset contains adjacent primary
>     keys.
>
>     This condition happens only if you paginate a table without
>     filters and sorting.
>
>     bye
>     gdo
>
--------------uI6Q18uIycziHXYvVgHV3xo0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><span _d-id="12367" class="--l --r sentence_highlight">I
        apologize that we are using this mail-list dedicated to
        mod_perl. <br>
      </span></p>
    <p _d-id="147002"><span _d-id="151147"
        class="--l --r sentence_highlight">Normally the page to be
        returned to the frontend for display has a few rows (tens) and
        therefore perform Join with a table of keys or use IN (...) m
        but also key = X OR key = Y OR ... </span><span _d-id="151150"
        class="--l --r sentence_highlight">makes little difference.<br
          _d-id="147004">
      </span><span _d-id="151152" class="--l --r sentence_highlight">In
        my case the recordset can be the result of JOIN (even more than
        one) with WHERE and ORDER ... all handled by the framework.</span></p>
    <p _d-id="147002"><span _d-id="151154"
        class="--l sentence_highlight">I have not found more efficient
        method.<br>
        <br>
        gdo<br>
      </span><span _d-id="151155" class="--r sentence_highlight"><br
          _d-id="147008">
      </span></p>
    <p></p>
    <div class="moz-cite-prefix">Il 10/08/24 08:11, Mithun Bhattacharya
      ha scritto:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAHTPV9Lmixd5n8o4KjjzU6sAifmDc_Lxrpeb0gHGzoRX2p2WKg@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="auto">
        <p dir="ltr">True - it will not return enough results if it is a
          sparse matrix. I might be biased because in my work it is ok
          to give less results if it is significantly faster.</p>
        <p dir="ltr">If exact numbers are required and the count is
          significantly higher then I would suggest keeping the select
          on primary keys but switch from a IN clause to a temporary
          table and do a join.</p>
      </div>
      <br>
      <div class="gmail_quote">
        <div dir="ltr" class="gmail_attr">On Sat, Aug 10, 2024, 12:54 AM
          Guido Brugnara &lt;<a href="mailto:[email protected]"
            moz-do-not-send="true" class="moz-txt-link-freetext">[email protected]</a>&gt;
          wrote:<br>
        </div>
        <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
          <div>
            <div>Il 09/08/24 20:28, Mithun Bhattacharya ha scritto:<br>
            </div>
            <blockquote type="cite">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</blockquote>
            <p><br>
            </p>
            <p><span>Your method works only if the recordset contains
                adjacent primary keys.<br>
              </span><span><br>
              </span></p>
            <p><span>This condition happens only if you paginate a table
                without filters and sorting.<br>
                <br>
                bye<br>
                gdo<br>
              </span></p>
          </div>
        </blockquote>
      </div>
    </blockquote>
  </body>
</html>

--------------uI6Q18uIycziHXYvVgHV3xo0--