Which adapter are you using? Mysqli or Pdo_Mysql?
The only thing that perhaps I see that is an immediate difference is
that you are using quote() that does userland quoting in some instances
(depending on the adapter you are using). Multiply that by 1000 and
perhaps that might be where you are getting the discrepancy.
-ralph
On 1/27/11 4:23 AM, Oleg_201080 wrote:
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.
I have a tables subscribers and I try to insert many rows in it.
With Zend_Db_Adapter it took 14.8 seconds to insert 10000 rows in
subscribers tables.
$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;
With mysql_query it took 3.8 seconds:
$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;
