Large table not loading

Questions related to using nuBuilder Forte.

Large table not loading

Unread postby sputs » Sat Jul 10, 2021 8:02 pm

Hi,

I'm having an issue with a table that is +3M records.

The view loads in less than one second in phpmyadmin.
In nubuilder it doesn't load. Nubuilder hangs with no error. When selecting one record with a where clause, it shows up in the table view but takes +60 seconds. Also here phpmyadmin shows it in under 1 second.

Any ideas on how to solve?

Thank you.
sputs
 
Posts: 22
Joined: Mon Feb 08, 2021 4:37 am

Re: Large table not loading

Unread postby kev1n » Sat Jul 10, 2021 11:08 pm

Hi,

You can try this patch:

Replace these two lines in nuform.php
Code: Select all
$t = nuRunQuery($s);   
$l = db_fetch_row($t);


with

Code: Select all
$t = nuRunQuery('SELECT COUNT(*) FROM ('. $s . ') nuTCount');
$rowData = db_fetch_row($t)[0];
kev1n
nuBuilder Team
 
Posts: 2012
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Large table not loading

Unread postby sputs » Sun Jul 11, 2021 4:38 pm

Hi Kevin,

I've implemented the change in function nuGetLookupValues
Running latest master. Still same behaviour. Apparently it is linked to the size of the table. If I select a subset it just works.

Code: Select all
        $s                      = nuReplaceHashVariables($s);
        $t = nuRunQuery('SELECT COUNT(*) FROM ('. $s . ') nuTCount');
        $rowData = db_fetch_row($t)[0];

        $f                      = nuObjKey($_POST['nuSTATE'],'prefix','') . $O->id;


I can place the table on ftp if you're interested in taking a look.


Since I don't need to load the entire table, is it possible to create a subform on 3 keys? I've set up the form with a Run button (on an edit form). I'm not quite sure on how to make the Filter work.
sputs
 
Posts: 22
Joined: Mon Feb 08, 2021 4:37 am

Re: Large table not loading

Unread postby kev1n » Sun Jul 11, 2021 6:22 pm

My patch was just for the Browse Form and using a dummy DB with about 3 millions, It loaded just fine.

If I understand you correctly, it doesn't work if a lookup object is used?
kev1n
nuBuilder Team
 
Posts: 2012
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Large table not loading

Unread postby kev1n » Sun Jul 11, 2021 6:44 pm

I've also tested it with a lookup object. No issues either.
Didn't you apply the patch in the function nuBrowseRows() ?
kev1n
nuBuilder Team
 
Posts: 2012
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Large table not loading

Unread postby sputs » Mon Jul 12, 2021 6:01 am

It's when making a basic form. no lookup. It executes the query below and then hangs.:
Code: Select all
SELECT Seq_id,Text,Datetime,Country,disease,category
FROM twitter_test
WHERE 1

If I make the number of records smaller with a where clause, it works.

This is the one I'd like to actually fix:
The one with the lookup is quite slow but works. In the process list I can see that it spends a lot of time on this (not defined by me anywhere):
Code: Select all
Select * FROM `twitter` WHERE `TweetId` = ''


While it should only be doing this (Browse>SQL):
Code: Select all
SELECT `Datetime`, Text FROM twitter
WHERE category = #category# AND disease = #disease# AND Country = "#Country#" AND `Datetime` >= "#date#"


Attached is a screenshot of the Browse definition. In Main I have TweetId as primary key. I don't quite understand why/where it executes the first query. If I could get rid of the first one somehow, it'll be lightning fast.
.
Attachments
Screenshot from 2021-07-11 22-33-46.png
Screenshot of the Browse definition
Screenshot from 2021-07-11 22-33-46.png (45.39 KiB) Viewed 289 times
sputs
 
Posts: 22
Joined: Mon Feb 08, 2021 4:37 am

Re: Large table not loading

Unread postby kev1n » Mon Jul 12, 2021 3:28 pm

sputs wrote:I can place the table on ftp if you're interested in taking a look.


Yes please, do so. Since I'm not able to replicate the issue with my table that also contains 3 millions of rows.

sputs wrote:This is the one I'd like to actually fix:
The one with the lookup is quite slow but works. In the process list I can see that it spends a lot of time on this (not defined by me anywhere):
Code: Select all
Select * FROM `twitter` WHERE `TweetId` = ''



Replace nuSetHashList() in nucommon.php with this one. This will eliminate that query.
Code: Select all
function nuSetHashList($p){

   $fid      = addslashes(nuObjKey($p,'form_id'));
   $rid      = addslashes(nuObjKey($p,'record_id'));

   $r         = array();

   if ($fid != '') {
      $s         = "SELECT sfo_table, sfo_primary_key FROM zzzzsys_form WHERE zzzzsys_form_id = '$fid'";
      $t         = nuRunQuery($s);

      if (db_num_rows($t) > 0) {

            $R         = db_fetch_object($t);
            $h         = array();

            if($p['call_type'] == 'getform'){

               if(trim($R->sfo_table) != ''){

                  $s      = "SELECT * FROM $R->sfo_table WHERE $R->sfo_primary_key = '$rid'";

                  $t      = nuRunQuery($s);
                  $f      = db_fetch_object($t);

                  if(is_object($f) ){

                     foreach ($f as $fld => $value ){                           //-- This Edit Form's Object Values
                        $r[$fld] = addslashes($value);
                     }

                  }
               }

            }

         }
   }

   foreach ($p as $key => $value){                                          //-- The 'opener' Form's properties

      if(gettype($value) == 'string' or is_numeric ($value)){
         $h[$key]         = addslashes($value);
      }else{
         $h[$key]         = '';
      }

   }

   if(isset($p['hash']) && gettype($p['hash']) == 'array'){

      foreach ($p['hash'] as $key => $value){                                 //-- The 'opener' Form's hash variables

         if(gettype($value) == 'string' or is_numeric ($value)){
            $h[$key]         = addslashes($value);
         }else{
            $h[$key]         = '';
         }

      }

   }

   $h['PREVIOUS_RECORD_ID']   = addslashes($rid);
   $h['RECORD_ID']            = addslashes($rid);
   $h['FORM_ID']            = addslashes($fid);
   $h['SUBFORM_ID']         = addslashes(nuObjKey($_POST['nuSTATE'],'object_id'));
   $h['ID']               = addslashes(nuObjKey($_POST['nuSTATE'],'primary_key'));
   $h['CODE']               = addslashes(nuObjKey($_POST['nuSTATE'],'code'));

   $cj = array();
   $cq = "SELECT sss_hashcookies FROM zzzzsys_session WHERE LENGTH(sss_hashcookies) > 0 AND zzzzsys_session_id = ? ";
   $ct = nuRunQuery($cq, array(
      $_SESSION['nubuilder_session_data']['SESSION_ID']
   ));
   $cr = db_fetch_object($ct);

   $A = nuGetUserAccess();
   if (db_num_rows($ct) > 0) {
      $cj = json_decode($cr->sss_hashcookies, true);
      return array_merge($cj, $h, $r, $A);
   } else {
      return array_merge($h, $r, $A);
   }

}
kev1n
nuBuilder Team
 
Posts: 2012
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Large table not loading

Unread postby sputs » Mon Jul 12, 2021 9:31 pm

Hi Kevin,

PM sent.

I replaced the hash function but it's still executing
Code: Select all
$s      = "SELECT * FROM $R->sfo_table WHERE $R->sfo_primary_key = '$rid'";
sputs
 
Posts: 22
Joined: Mon Feb 08, 2021 4:37 am

Re: Large table not loading

Unread postby sputs » Tue Jul 13, 2021 10:04 pm

Issue solved.
From Kevin: all patches/speed improvements have been incorporated into the official version.
sputs
 
Posts: 22
Joined: Mon Feb 08, 2021 4:37 am


Return to General

Who is online

Users browsing this forum: No registered users and 23 guests

cron