Question re:DB package interaction with MySQL

As I understand it, given the following code...


$col_names = array ('col1','col2');
$rand = array(
  array('12341234', '345782'), 
  array('23423', '0983450934')
);

$dsn = 'mysql://root:secretlocalhost/database1';
$db =& DB::connect($dsn);

$sth =
$db->autoPrepare('randtest',$col_names,DB_AUTOQUERY_INS
ERT);
if (DB::isError($sth)) die ($sth->getMessage());
$sth = $db->executeMultiple($sth, $rand);


...DB will create and send two separate INSERT queries.  My
question is, why does DB create two separate queries, when
MySQL's capable of combining multiple INSERTS into a single
statement? (i.e. INSERT INTO randtest VALUES ('12341234',
'345782'), ('23423', '0983450934');)  

I ran a slightly more complex test, with a pre-generated set
of 100,000 rows, and DB took 40-60 seconds to insert,
whereas everything rolled into a single query took a single
second to insert.

It seems the alternate syntax would result in a huge
performance gain here...

-- 
PEAR Development Mailing List (http://pear.php.net/)
To unsubscribe, visit: http://www.php.net/unsub
.php

There is a very slight "gotcha" with the 2nd
insert syntax: it's very easy
to make a query so long that it fails (mysql gives an error
about
"max_packet exceeded" I think) so you have to
make sure you limit the size
of your insert.

-- 
Ben XO

-----Original Message-----
From: Frank F [mailto:frankbooksku.com] 
Sent: 24 February 2006 02:51
To: pear-devlists.php.net
Subject: [PEAR-DEV] Question reB
package interaction with MySQL

As I understand it, given the following code...


$col_names = array ('col1','col2');
$rand = array(
  array('12341234', '345782'), 
  array('23423', '0983450934')
);

$dsn = 'mysql://root:secretlocalhost/database1';
$db =& DB::connect($dsn);

$sth =
$db->autoPrepare('randtest',$col_names,DB_AUTOQUERY_INS
ERT);
if (DB::isError($sth)) die ($sth->getMessage());
$sth = $db->executeMultiple($sth, $rand);


...DB will create and send two separate INSERT queries.  My
question is, why
does DB create two separate queries, when MySQL's capable
of combining
multiple INSERTS into a single statement? (i.e. INSERT INTO
randtest VALUES
('12341234', '345782'), ('23423', '0983450934');)  

I ran a slightly more complex test, with a pre-generated set
of 100,000
rows, and DB took 40-60 seconds to insert, whereas
everything rolled into a
single query took a single second to insert.

It seems the alternate syntax would result in a huge
performance gain
here...

-- 
PEAR Development Mailing List (http://pear.php.net/)
To unsubscribe, visit: http://www.php.net/unsub
.php

-- 
PEAR Development Mailing List (http://pear.php.net/)
To unsubscribe, visit: http://www.php.net/unsub
.php

Hi:

On Thu, Feb 23, 2006 at 06:50:50PM -0800, Frank F wrote:
> 
> ...DB will create and send two separate INSERT queries.
 My question is, 
> why does DB create two separate queries, when MySQL's
capable of combining 
> multiple INSERTS into a single statement? (i.e. INSERT
INTO randtest VALUES 
> ('12341234', '345782'), ('23423',
'0983450934');)

Don't know.  Individidual inserts is the way it was when I
began 
maintaining the package.  Perhaps when DB was first written,
extended 
insert wasn't an option.

What does PEAR's new abstraction layer, MDB2, do?  If it
too does the 
individual inserts, provide a patch to the MDB2 mysql driver
that provides 
extended inserts.

--Dan

-- 
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M
P A N Y
            data intensive web and database programming
                http://www.Analy
sisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f:
718-854-0409

-- 
PEAR Development Mailing List (http://pear.php.net/)
To unsubscribe, visit: http://www.php.net/unsub
.php

Daniel Convissor wrote:
> Hi:
> 
> On Thu, Feb 23, 2006 at 06:50:50PM -0800, Frank F
wrote:
>> ...DB will create and send two separate INSERT
queries.  My question is, 
>> why does DB create two separate queries, when
MySQL's capable of combining 
>> multiple INSERTS into a single statement? (i.e.
INSERT INTO randtest VALUES 
>> ('12341234', '345782'), ('23423',
'0983450934');)

Well you are combining two separate methods. How should
autoPrepare() 
know how many rows you want to insert?

> What does PEAR's new abstraction layer, MDB2, do?  If
it too does the 
> individual inserts, provide a patch to the MDB2 mysql
driver that provides 
> extended inserts.

Nothing special, since there is nothing special to do .. see
above and I 
really do not see what MDB2 should do there. Especially
since the multi 
insert syntax is more or less directly opposed to the entire
prepared 
statement idea from a syntax perspective.

regards,
Lukas

-- 
PEAR Development Mailing List (http://pear.php.net/)
To unsubscribe, visit: http://www.php.net/unsub
.php