Re: [fw-db] large data insert (performance)

tfk wrote:
>
> ...
> Also, since Zend_Db will not cater your use case, I'd suggest you
> write the query yourself:
> INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
>
> I'm assuming you use MySQL? I'm not sure about the other RDBMS. I
> don't know for sure anymore, but I think that is what MySQL calls
> "extended inserts". Haven't been able to find the manual page for it,
> but that should give you an idea.
>
> Till
I think this is also known as "compound insert" and part of standard SQL-92.
According to http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts
Wikipedia , this is supported by "DB2, PostgreSQL (since version 8.2),
MySQL, and H2". Just yesterday I wrote a (quick and dirty) method to solve
this problem. As I had no time to test it extensively yet, I would be glad
if you could try this and tell me if you ran into any problems.

One comment to begin with:
- $this->getAdapter() returns the used Zend_Db-adapter

/**
* Inserts multiple rows into the database. For
* databases implementing the compound insert statement
* according to SQL-92 (DB2, PostgreSQL > 8.2, MySQL),
* this happens in a single query. For other databases,
* all rows are inserted separately.
*
* The first parameter is an array containing the
* columns for which data should be inserted. The second
* parameter is an array of arrays containing the values
* for these columns.
* The values (= the data) are escaped for the query;
* you can specify the data type of a column using the
* Zend_Db constants by setting the column name as the
* array key and the data type as the array value.
*
* <code>
* $columns = array('name',
* 'postcount' => Zend_Db::INT_TYPE);
* $data = array(array('John Dorian', 4),
* array('Percival Cox', 12));
*
* $this->insertMultiple($columns,$data);
* </code>
*
* @param array $columns The column names
* @param array $data The values
* @return void
*/
public function insertMultiple(array $columns,array $data)
{
/*
* Check if the used database adapter is supported
* by the method.
*/
$db = $this->getAdapter();
$dbConfig = $db->getConfig();
$dbType = strtolower($dbConfig['adapter']);
$supportedSql92 = array('db2','mysqli','pdo_mysql','pdo_pgsql');
$insertMethod = null;
if (in_array($dbType,$supportedSql92)) {
if ($dbType === 'pdo_pgsql' &&
!version_compare($db->getServerVersion(), '8.2', '>=')) {
throw new Exception('PostgreSQL '.$db->getServerVersion().'
does not support the compound insert statement according to SQL-92.');
}
// Adapter supports the SQL-92 standard.
$insertMethod = 'sql92';
} else {
// Adapter does not support SQL-92.
$insertMethod = 'seperately';
}

// Array for column data types.
$types = array_fill(0, count($columns), null);

// Scan the column array for type declarations.
$i = 0;
foreach ($columns as $key => &$value) {
if (is_string($key)) {
// $key = column, $value = type. Remember the data type for later
and set the column as value.
$types[$i] = $value;
$value = $db->quoteIdentifier($key);
} else {
$value = $db->quoteIdentifier($value);
}
$i++;
}
unset($value);

if ($insertMethod === 'sql92') {
/**
* Build the query according to SQL-92.
*/
$query = 'INSERT INTO '.$db->quoteIdentifier($this->_name).'
('.implode(',',$columns).') VALUES ';

// Quote every value of every row with the correct type
declaration.
$rowsSql = array();
foreach ($data as $row) {
$rowValues = array();
$i = 0;
foreach ($row as $value) {
$rowValues[] = $db->quote($value,$types[$i]);
$i++;
}
$rowsSql[] = '('.implode(',',$rowValues).')';
}

$query .= implode(',',$rowsSql);
$db->query($query);
} else {
/**
* Or do all inserts seperately.
*/
$query = 'INSERT INTO '.$db->quoteIdentifier($this->_name).'
('.implode(',',$columns).') VALUES ';

foreach ($data as $row) {
$valuesSql = '';
$i = 0;
foreach ($row as $value) {
$rowValues[] = $db->quote($value,$types[$i]);
$i++;
}
$valuesSql = '('.implode(',',$rowValues).')';
$db->query($query.$valuesSql);
}
}
}
--
View this message in context: http://www.nabble.com/large-data-insert-%28performance%29-tp22120411p22376207.html
Sent from the Zend DB mailing list archive at Nabble.com.