Page 1 of 1
Large table not loading
Posted: Sat Jul 10, 2021 12:32 pm
by sputs
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.
Re: Large table not loading
Posted: Sat Jul 10, 2021 3:38 pm
by kev1n
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];
Re: Large table not loading
Posted: Sun Jul 11, 2021 9:08 am
by sputs
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.
Re: Large table not loading
Posted: Sun Jul 11, 2021 10:52 am
by kev1n
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?
Re: Large table not loading
Posted: Sun Jul 11, 2021 11:14 am
by kev1n
I've also tested it with a lookup object. No issues either.
Didn't you apply the patch in the function nuBrowseRows() ?
Re: Large table not loading
Posted: Sun Jul 11, 2021 10:31 pm
by sputs
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.
.
Re: Large table not loading
Posted: Mon Jul 12, 2021 7:58 am
by kev1n
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);
}
}
Re: Large table not loading
Posted: Mon Jul 12, 2021 2:01 pm
by sputs
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'";
Re: Large table not loading
Posted: Tue Jul 13, 2021 2:34 pm
by sputs
Issue solved.
From Kevin: all patches/speed improvements have been incorporated into the official version.