My "real" application has inserts and does other various forms of
processing in the loops which cannot be done from pure sql via join,
etc. The test program I attached was just the minimum required to see
the performance issue. I agree that PHP is probably a little slower
that perl in loops, but not 5 seconds vs. several hours.
Kevin
On Aug 22, 2004, at 9:49 PM, John Holmes wrote:
Kevin Wormington wrote:
I have ran into an interesting query performance problem that occurs
with both 4.3.7 and 5.0.1 against a MySQL 4.1 database. The test
program (attached below) works, but performance is extremely sloooow,
a perl version of the test program completes in less than 5 seconds
with 80k records in table 1 and 138k records in table 2. The PHP
program is taking something like 1 to 2 seconds per record. This
only seems to occur when you have a query nested inside of another.
My test program attached uses mysqli, but mysql functions yield the
same result. Any ideas would be appreciated.
<?
$link = mysqli_connect("localhost","test","test","db");
$qry = "select a,b from table1";
$result = mysqli_query($link,$qry);
if (!$result) { echo mysql_error() . "\n"; }
while ($data = mysqli_fetch_array($result, MYSQLI_BOTH)) {
$sql = "select * from table2 where common=".$data[0];
$result2 = mysqli_query($link,$sql);
if (!$result2) { echo mysql_error() . "\n"; }
while ($data2 = mysqli_fetch_array($result2,MYSQLI_BOTH)) {
$linenum++;
}
mysqli_free_result($result2);
}
mysqli_free_result($result);
mysqli_close($link);
Is there some fundamental reason you're using nested queries instead
of a JOIN? PHP is generally slower in loops compared to Perl from the
comparisons I've seen, but the real issue is why are you using nested
queries to begin with...
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals – www.phparch.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php