note 77107 modified in function.mysqli-prepare by felipe
| From: | felipe@php.net | Date: | Mon, 10 Dec 2007 17:26:56 +0000 |
| Subject: | note 77107 modified in function.mysqli-prepare by felipe | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-133164@lists.php.net to get a copy of this message | ||
Performance note to those who wonder. I performed a test where first of all inserted about 30,000
posts with one PK:id and a varchar(20), where the varchar data was md5-hash for the current iterator
value just to fill with some data.
The test was performed on a dedicated ubuntu 7.04 server with apache2/php5/mysql5.0 running on
Athlon 64 - 3000+ with 512MB of RAM. The queries where tested with a for-loop from 0 to 30000 first
with:
<?php
for ( $i = 0; $i <= 30000; ++$i )
{
$result = $mysqli->query("SELECT * FROM test WHERE id = $i");
$row = $result->fetch_row();
echo $row[0]; //prints id
}
?>
which gave a page-load time of about 3.3seconds avarage, then with this loop:
<?php
$stmt = $mysqli->prepare("SELECT * FROM test WHERE id = ?");
for ( $i = 0; $i <= 30000; ++$i )
{
$stmt->bind_param("i", $i);
$stmt->execute();
$stmt->bind_result($id, $md5);
$stmt->fetch();
echo $id;
}
$stmt->close();
?>
and the avarage page-load was lowered by 1.3sec, which means about 2.0 sec avarage! Guess the
performance difference could be even greater on a more complex/larger table and more complex
SQL-queries.
--was--
Performance note to those who wonder. I performed a test where first of all inserted about 30,000
posts with one PK:id and a varchar(20), where the varchar data was md5-hash for the current iterator
value just to fill with some data.
The test was performed on a dedicated ubuntu 7.04 server with apache2/php5/mysql2.9 running on
Athlon 64 - 3000+ with 512MB of RAM. The queries where tested with a for-loop from 0 to 30000 first
with:
<?php
for ( $i = 0; $i <= 30000; ++$i )
{
$result = $mysqli->query("SELECT * FROM test WHERE id = $i");
$row = $result->fetch_row();
echo $row[0]; //prints id
}
?>
which gave a page-load time of about 3.3seconds avarage, then with this loop:
<?php
$stmt = $mysqli->prepare("SELECT * FROM test WHERE id = ?");
for ( $i = 0; $i <= 30000; ++$i )
{
$stmt->bind_param("i", $i);
$stmt->execute();
$stmt->bind_result($id, $md5);
$stmt->fetch();
echo $id;
}
$stmt->close();
?>
and the avarage page-load was lowered by 1.3sec, which means about 2.0 sec avarage! Guess the
performance difference could be even greater on a more complex/larger table and more complex
SQL-queries.
http://php.net/manual/en/function.mysqli-prepare.php