What prevents you from doing this iteratively, (not loading all 150,000
rows into memory, but processing each row sequentially)?


-----Original Message-----
From: Nicolas Guilhot [mailto:[EMAIL PROTECTED]] 
Sent: Monday, October 29, 2001 11:44 AM
To: Php General MailingList
Subject: [PHP] Problem with very big array !


Hi guys,

I have an sql query returning a result of nearly 150 000 rows. I use the
PEAR DB::getAll method to retrieve this result as an array which is very
big
indeed (about 300Mb). I can't use the MySQL LIMIT statement because I
need
to do sorting stuff on the result array.
My concern is that even after processing the last line of the script,
the
process continue running for about 20~30 minutes (I am watching running
processes using the unix command 'top'). For information, querying the
database, sorting the array and displaying results with a pager system
only
takes 3 minutes.

I have made a short example that reproduces the problem. If you could
test
it (IT WILL USE ABOUT 120Mb OF RAM) and tell me if you've got the same
problem it would be nice. You need to create a test database with the
following table :

CREATE TABLE test (
  id mediumint(20) NOT NULL,
  text varchar(255) NOT NULL default '',
  PRIMARY KEY  (id)
)

Then populate it with 150 000 rows using the populate.php script.
Finally, execute the query.php script and use top to see if the process
stops running quickly. For me it takes a lot of time to end (more than
20
minutes).
If you empty the database table, remove one character from the string in
the
insertInto function and populate it again, it should work correctly.

Thanks for any answer !!

PS : I am using PHP 4.06, MySQL 3.23.41 and Apache on a bi-processor
linux
box with 1Gb RAM.


//--------------
// populate.php
//--------------
<?php
require_once("DB.php");

function getmicrotime()
{
  list($usec, $sec) = explode(" ",microtime());
  return ((float)$usec + (float)$sec);
}

function insertInto(&$db)
{
  for ($cpt = 0; $cpt < 150000; $cpt++)
  {
    $result = $db->query("INSERT INTO test VALUES('$cpt', 'This is the
very
big test n $cpt. With more text. This time it will take more memory,
and
more time to execute. This is very strange. I do not understand why
???????????????')");
  }
}


$db = DB::connect("mysql://user:password@localhost/test");
$time_start = getmicrotime();

insertInto($db);

$time_end = getmicrotime();
$time = $time_end - $time_start;
echo "Execution time : $time seconds";
?>
//-------------
//End of populate.php
//-------------



//-------------
//query.php
//-------------
<?php
require_once("DB.php");

function getmicrotime()
{
  list($usec, $sec) = explode(" ",microtime());
  return ((float)$usec + (float)$sec);
}

$db = DB::connect("mysql://user:password@localhost/test");
$time_start = getmicrotime();

$result = $db->getAll("SELECT * from test");

$time_end = getmicrotime();
$time = $time_end - $time_start;
echo "Execution time : $time seconds";
?>
//--------------
//End of query.php
//--------------


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to