This code takes about 0.065 seconds with mysql_query:
$dateAdded = date('Y-m-d H:i:s');
$lastChanged = $dateAdded;
$startTime = microtime(true);
$result = mysql_query('BEGIN');
for ($i = 0; $i < 100; $i++) {
$email = 'test_ ' . $i . '@gmail.com';
$query = 'INSERT INTO subscribers (list_id, account_id,
email_address, first_name, last_name, date_added, last_changed) ' .
'VALUES (' . 52 . ', ' . 29 . ', \'' .
mysql_real_escape_string($email) . '\', \'' .
mysql_real_escape_string($firstName) .
'\', \'' . mysql_real_escape_string($lastName) . '\', \'' .
mysql_real_escape_string($dateAdded) . '\', \'' .
mysql_real_escape_string($lastChanged) . '\')';
mysql_query($query);
}
$result = mysql_query('COMMIT');
$time = microtime(true) - $startTime;
echo 'Using mysql_query: ' . $time . '<br />';
Hello. I use Zend Framework in my project. I need to insert multiple records
and I found that Zend_Db suprisingly slower thatn my_sql query (several
times), that made me think I did something wrong. Here are two examples.
Zend_Db_Adapter:
$startTime = microtime(true);
$db = Zend_Db_Table::getDefaultAdapter();
$db->beginTransaction();
$dateAdded = date('Y-m-d H:i:s');
$lastChanged = $dateAdded;
foreach ($importDataNamespace->data as $subscriberNum =>
$subscriber)
{
foreach ($fieldsMap as $fieldNumber => $fieldTag) {
if (isset($subscriber[$fieldNumber])) {
$subscriberData[$fieldTag] = $subscriber[$fieldNumber];
} else {
$subscriberData[$fieldTag] = '';
}
}
$query = 'INSERT INTO subscribers (list_id, account_id,
email_address, first_name, last_name, date_added, last_changed) ' .
'VALUES (' . 52 . ', ' . 29 . ', ' .
$db->quote($subscriberData['EMAIL']) . ', ' .
$db->quote($subscriberData['FNAME']) .
', ' . $db->quote($subscriberData['LNAME']) . ', ' .
$db->quote($dateAdded) . ', ' . $db->quote($lastChanged) . ')';
$db->query($query);
}
$db->commit();
$this->view->time = microtime(true) - $startTime;
Example with mysql_query:
$startTime = microtime(true);
$user = 'root';
$password = 'password';
$db = 'database';
$connect = @mysql_connect('localhost',$user,$password) or
die("Failed to connect database");
@mysql_select_db($db) or die("Failed to select database");
$dateAdded = date('Y-m-d H:i:s');
$lastChanged = $dateAdded;
$result = mysql_query('SET autocommit = 0');
foreach ($importDataNamespace->data as $subscriberNum =>
$subscriber)
{
foreach ($fieldsMap as $fieldNumber => $fieldTag) {
if (isset($subscriber[$fieldNumber])) {
$subscriberData[$fieldTag] = $subscriber[$fieldNumber];
} else {
$subscriberData[$fieldTag] = '';
}
}
$query = 'INSERT INTO subscribers (list_id, account_id,
email_address, first_name, last_name, date_added, last_changed) ' .
'VALUES (' . 52 . ', ' . 29 . ', \'' .
mysql_real_escape_string($subscriberData['EMAIL']) . '\', \'' .
mysql_real_escape_string($subscriberData['FNAME']) .
'\', \'' .
mysql_real_escape_string($subscriberData['LNAME']) . '\', \'' . $dateAdded .
'\', \'' . $lastChanged . '\')';
mysql_query($query);
}
$result = mysql_query('SET autocommit = 1');
$result = mysql_query('COMMIT;');
$this->view->time = microtime(true) - $startTime;
In the first case it took 14.8 seconds, in the second 3.7. Could you tell me
why does it happend and what do you do wrong?
If I delete any quote for Zend_Db it took 12 seconds from 14 with quote, but
it's still much more slower than with mysql_query:
$startTime = microtime(true);
$db = Zend_Db_Table::getDefaultAdapter();
$db->beginTransaction();
$dateAdded = date('Y-m-d H:i:s');
$lastChanged = $dateAdded;
foreach ($importDataNamespace->data as $subscriberNum =>
$subscriber)
{
foreach ($fieldsMap as $fieldNumber => $fieldTag) {
if (isset($subscriber[$fieldNumber])) {
$subscriberData[$fieldTag] = $subscriber[$fieldNumber];
} else {
$subscriberData[$fieldTag] = '';
}
}
$query = 'INSERT INTO subscribers (list_id, account_id,
email_address, first_name, last_name, date_added, last_changed) ' .
'VALUES (' . 52 . ', ' . 29 . ', \'' .
$subscriberData['EMAIL'] . '\', \'' . $subscriberData['FNAME'] .
'\', \'' . $subscriberData['LNAME'] . '\', \'' .
$dateAdded . '\', \'' . $lastChanged . '\')';
$db->query($query);
}
$db->commit();
$this->view->time = microtime(true) - $startTime;
Thank you for any information about this issue.
This code takes about 0.065 seconds with mysql_query:
$dateAdded = date('Y-m-d H:i:s');
$lastChanged = $dateAdded;
$startTime = microtime(true);
$result = mysql_query('BEGIN');
for ($i = 0; $i < 100; $i++) {
$email = 'test_ ' . $i . '@gmail.com';
$query = 'INSERT INTO subscribers (list_id, account_id,
email_address, first_name, last_name, date_added, last_changed) ' .
'VALUES (' . 52 . ', ' . 29 . ', \'' .
mysql_real_escape_string($email) . '\', \'' .
mysql_real_escape_string($firstName) .
'\', \'' . mysql_real_escape_string($lastName) . '\', \'' .
mysql_real_escape_string($dateAdded) . '\', \'' .
mysql_real_escape_string($lastChanged) . '\')';
mysql_query($query);
}
$result = mysql_query('COMMIT');
$time = microtime(true) - $startTime;
echo 'Using mysql_query: ' . $time . '<br />';
exit();
Code with benchmark of Zend_Db_Adapter (I didn't even use quote in this
case):
$db = Zend_Db_Table::getDefaultAdapter();
$db->getProfiler()->setEnabled(true);
$profiler = $db->getProfiler();
$startTime = microtime(true);
$db->beginTransaction();
for ($i = 0; $i < 100; $i++)
{
$email = 'test_' . $i . '@gmail.com';
$query = 'INSERT INTO subscribers (list_id, account_id,
email_address, first_name, last_name, date_added, last_changed) ' .
'VALUES (' . 52 . ', ' . 29 . ', \'' . $email . '\', \'' .
$firstName .
'\', \'' . $lastName . '\', \'' . $dateAdded . '\', \'' .
$lastChanged . '\')';
$db->query($query);
}
$db->commit();
$time = microtime(true) - $startTime;
echo 'Time of transaction Zend_Db_Adapter query: ' . $time . '<br />';
echo 'Total time ' . $profiler->getTotalElapsedSecs() . '<br />';
$count = 0;
$totalTime = 0;
foreach ($profiler->getQueryProfiles() as $query) {
$count++;
$elapsedTime = $query->getElapsedSecs();
$totalTime += $elapsedTime;
echo $count . ' ' . $elapsedTime . ' ' . $query->getQuery() . '<br
/>';
}
echo 'Sum time: ' . $totalTime . '<br />';
Hello. I use Zend Framework in my project. I need to insert multiple records
and I found that Zend_Db suprisingly slower thatn my_sql query (several
times), that made me think I did something wrong. Here are two examples.
Zend_Db_Adapter:
$startTime = microtime(true);
$db = Zend_Db_Table::getDefaultAdapter();
$db->beginTransaction();
$dateAdded = date('Y-m-d H:i:s');
$lastChanged = $dateAdded;
foreach ($importDataNamespace->data as $subscriberNum =>
$subscriber)
{
foreach ($fieldsMap as $fieldNumber => $fieldTag) {
if (isset($subscriber[$fieldNumber])) {
$subscriberData[$fieldTag] = $subscriber[$fieldNumber];
} else {
$subscriberData[$fieldTag] = '';
}
}
$query = 'INSERT INTO subscribers (list_id, account_id,
email_address, first_name, last_name, date_added, last_changed) ' .
'VALUES (' . 52 . ', ' . 29 . ', ' .
$db->quote($subscriberData['EMAIL']) . ', ' .
$db->quote($subscriberData['FNAME']) .
', ' . $db->quote($subscriberData['LNAME']) . ', ' .
$db->quote($dateAdded) . ', ' . $db->quote($lastChanged) . ')';
$db->query($query);
}
$db->commit();
$this->view->time = microtime(true) - $startTime;
Example with mysql_query:
$startTime = microtime(true);
$user = 'root';
$password = 'password';
$db = 'database';
$connect = @mysql_connect('localhost',$user,$password) or
die("Failed to connect database");
@mysql_select_db($db) or die("Failed to select database");
$dateAdded = date('Y-m-d H:i:s');
$lastChanged = $dateAdded;
$result = mysql_query('SET autocommit = 0');
foreach ($importDataNamespace->data as $subscriberNum =>
$subscriber)
{
foreach ($fieldsMap as $fieldNumber => $fieldTag) {
if (isset($subscriber[$fieldNumber])) {
$subscriberData[$fieldTag] = $subscriber[$fieldNumber];
} else {
$subscriberData[$fieldTag] = '';
}
}
$query = 'INSERT INTO subscribers (list_id, account_id,
email_address, first_name, last_name, date_added, last_changed) ' .
'VALUES (' . 52 . ', ' . 29 . ', \'' .
mysql_real_escape_string($subscriberData['EMAIL']) . '\', \'' .
mysql_real_escape_string($subscriberData['FNAME']) .
'\', \'' .
mysql_real_escape_string($subscriberData['LNAME']) . '\', \'' . $dateAdded .
'\', \'' . $lastChanged . '\')';
mysql_query($query);
}
$result = mysql_query('SET autocommit = 1');
$result = mysql_query('COMMIT;');
$this->view->time = microtime(true) - $startTime;
In the first case it took 14.8 seconds, in the second 3.7. Could you tell me
why does it happend and what do you do wrong?
If I delete any quote for Zend_Db it took 12 seconds from 14 with quote, but
it's still much more slower than with mysql_query:
$startTime = microtime(true);
$db = Zend_Db_Table::getDefaultAdapter();
$db->beginTransaction();
$dateAdded = date('Y-m-d H:i:s');
$lastChanged = $dateAdded;
foreach ($importDataNamespace->data as $subscriberNum =>
$subscriber)
{
foreach ($fieldsMap as $fieldNumber => $fieldTag) {
if (isset($subscriber[$fieldNumber])) {
$subscriberData[$fieldTag] = $subscriber[$fieldNumber];
} else {
$subscriberData[$fieldTag] = '';
}
}
$query = 'INSERT INTO subscribers (list_id, account_id,
email_address, first_name, last_name, date_added, last_changed) ' .
'VALUES (' . 52 . ', ' . 29 . ', \'' .
$subscriberData['EMAIL'] . '\', \'' . $subscriberData['FNAME'] .
'\', \'' . $subscriberData['LNAME'] . '\', \'' .
$dateAdded . '\', \'' . $lastChanged . '\')';
$db->query($query);
}
$db->commit();
$this->view->time = microtime(true) - $startTime;
Thank you for any information about this issue.
This code takes about 0.065 seconds with mysql_query:
$dateAdded = date('Y-m-d H:i:s');
$lastChanged = $dateAdded;
$startTime = microtime(true);
$result = mysql_query('BEGIN');
for ($i = 0; $i < 100; $i++) {
$email = 'test_ ' . $i . '@gmail.com';
$query = 'INSERT INTO subscribers (list_id, account_id,
email_address, first_name, last_name, date_added, last_changed) ' .
'VALUES (' . 52 . ', ' . 29 . ', \'' .
mysql_real_escape_string($email) . '\', \'' .
mysql_real_escape_string($firstName) .
'\', \'' . mysql_real_escape_string($lastName) . '\', \'' .
mysql_real_escape_string($dateAdded) . '\', \'' .
mysql_real_escape_string($lastChanged) . '\')';
mysql_query($query);
}
$result = mysql_query('COMMIT');
$time = microtime(true) - $startTime;
echo 'Using mysql_query: ' . $time . '<br />';
exit();
Code with benchmark of Zend_Db_Adapter (I didn't even use quote in this
case):
$db = Zend_Db_Table::getDefaultAdapter();
$db->getProfiler()->setEnabled(true);
$profiler = $db->getProfiler();
$startTime = microtime(true);
$db->beginTransaction();
for ($i = 0; $i < 100; $i++)
{
$email = 'test_' . $i . '@gmail.com';
$query = 'INSERT INTO subscribers (list_id, account_id,
email_address, first_name, last_name, date_added, last_changed) ' .
'VALUES (' . 52 . ', ' . 29 . ', \'' . $email . '\', \'' .
$firstName .
'\', \'' . $lastName . '\', \'' . $dateAdded . '\', \'' .
$lastChanged . '\')';
$db->query($query);
}
$db->commit();
$time = microtime(true) - $startTime;
echo 'Time of transaction Zend_Db_Adapter query: ' . $time . '<br />';
echo 'Total time ' . $profiler->getTotalElapsedSecs() . '<br />';
$count = 0;
$totalTime = 0;
foreach ($profiler->getQueryProfiles() as $query) {
$count++;
$elapsedTime = $query->getElapsedSecs();
$totalTime += $elapsedTime;
echo $count . ' ' . $elapsedTime . ' ' . $query->getQuery() . '<br
/>';
}
echo 'Sum time: ' . $totalTime . '<br />';
Here are some results:
Time of transaction Zend_Db_Adapter query: 0.23094701767 s
Total time of all queries 0.0949234962463
Time of connection: 0.00199699401855 connect
Time of begin: 0.000336885452271 s begin
Time of insert 0.000540018081665 INSERT INTO subscribers (list_id,
account_id, email_address, first_name, last_name, date_added, last_changed)
VALUES (52, 29, '[email protected]', 'John', 'Clinton', '2011-01-28
15:25:21', '2011-01-28 15:25:21')
.....
Time of commit 0.0214757919312 s commit
--
View this message in context:
http://zend-framework-community.634137.n4.nabble.com/why-Zend-Db-Adapter-happened-to-be-much-slower-than-mysql-query-tp3241873p3244345.html
Sent from the Zend Framework mailing list archive at Nabble.com.
