Re: Problem with very big result array !

Hi Nicolas, I like your attitude - no task is too big for PHP! I would test to see if the slowdown at the end is caused by freeing of variables or maybe the PHP heap is very fragmented. Try manually to unset($bigvar1); unset($bigvar2); and see if that is the major cause of the slowdown. Also are they any PEAR destructors being activated? Bye, John Nicolas Guilhot <nguilhot@METabolic-EXplorer.com> wrote in message news:MPECKANLEPNEDGHPAOBEEEOFCHAA.nguilhot@METabolic-EXplorer.com... > 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. > > > file://-------------- > // populate.php > file://-------------- > <?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"; > ?> > file://------------- > file://End of populate.php > file://------------- > > > > file://------------- > file://query.php > file://------------- > <?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"; > ?> > file://-------------- > file://End of query.php > file://-------------- > > > >