Large table not loading

[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.

[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];

[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.

[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?

[kev1n]

I've also tested it with a lookup object. No issues either.
Didn't you apply the patch in the function nuBrowseRows() ?

[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.
.

[kev1n]

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.

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);
	}

}

[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'";

[sputs]

Issue solved.
From Kevin: all patches/speed improvements have been incorporated into the official version.