[Q] PDO use to bind arrays for insert

Hi,
I appologize in advance, if this is not the right place
to ask (but I would appreciate then a pointer where I could ask)

I am using PHP 5.2.6 and Postgres 8.3 both on windows right now.

Wanted to know if there is a way to pass via PDO (because
that's how I connect now to Postgres)

an array of values to insert at once

for example:

I have a PHP class:

cwork
$work_id;
$work_desc;


I have, say, an array of 50 objects of type cwork

arr_work

I would like to insert all items in the array at once
so that I would do one trip to the database.

instead of performing insert SQL in a loop for each item.

In oracle such functionality is called array inserts or bulk
insert (sometimes). And in C++/OCI the interface is to basically
specifying a character array of values (with byte offsets)
for eacho of the fields.

So there would be an array of work_id, and array of work_desc
and both of those of the same length would have been specified
and bound to the bind variables

Thanks in advance,
VSP



--
V S P
toreason@fastmail.fm

--
http://www.fastmail.fm - Or how I learned to stop worrying and
love email again


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: [Q] PDO use to bind arrays for insert

V S P wrote:
> Hi,
> I appologize in advance, if this is not the right place
> to ask (but I would appreciate then a pointer where I could ask)
>
> I am using PHP 5.2.6 and Postgres 8.3 both on windows right now.
>
> Wanted to know if there is a way to pass via PDO (because
> that's how I connect now to Postgres)
>
> an array of values to insert at once
>
> for example:
>
> I have a PHP class:
>
> cwork
> $work_id;
> $work_desc;
>
>
> I have, say, an array of 50 objects of type cwork
>
> arr_work
>
> I would like to insert all items in the array at once
> so that I would do one trip to the database.

PDO doesn't let you send multiple queries in the one request.

Since you're connecting to postgres, you can use this to do a bulk-insert:

http://www.php.net/manual/en/function.pg-put-line.php

Though it's not part of pdo, and also postgres 'copy from stdin' is all
or nothing - so if you have one line of bad data in there, none of it's
committed.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: [Q] PDO use to bind arrays for insert

Hi Kim,
thank you for the reply
Yes, I thought I can use prepared statements to optimize
the insert speed.

I was just looking for a further optimization
(because prepared inserts still cause a network trip
for every insert (but SQL is not parsed everytime because
it is prepared))

In otherwords what I wanted (conseptually is)

insert into work_items (work_id, work_desc) values
(:bound_ar_of_work_ids, :bound_ar_of_work_desc)

bindParameter(":bound_ar_of_work_ids", ar_of_work_ids, PDO::ARRAY_INT);

bindParameter(":bound_ar_of_work_desc",ar_of_work_desc,PDO:: ARRAY_STRING);


I understand that I could simply generate the
string such as

insert into work_items (work_id, work_desc)
values (
("id_val1", "desc_val1"),
("id_val2", "desc_val2"),
("id_val3","desc_val3")
....
);

But the problem with the above that it will not be prepared
-- so the SQL engine will be parsing it
(also some SQL engines besides parsing, may assign a
Optimizer plan to prepared queries, not sure if PG does that)


I think Chris @ dmagick pointed to a raw string functionality
that -- so I will need to figure out if
a) it actually saves on parsing
b) if it will work with PG connection pooling (which I am not
using yet, but will in the future).


> $stmt->bindParam (':work_desc', $work_desc, PDO::PARAM_STR);
>
> for ($i = 0; $i < count($my_array); $i++) {
>
> $work_desc = $my_array[$i];
>
> $stmt->execute();
>
> }
>
> Kim Lesmer
--
V S P
toreason@fastmail.fm

--
http://www.fastmail.fm - The professional email service


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: [Q] PDO use to bind arrays for insert

On Sun, 2008-11-16 at 22:01 -0500, V S P wrote:
>
> I think Chris @ dmagick pointed to a raw string functionality
> that -- so I will need to figure out if
> a) it actually saves on parsing

Using the COPY functionality in PostgreSQL does save on parsing,
although an insert of many rows in a single insert statement doesn't
spend any significant amount of it's time parsing the input, and the
query planning only happens once.

If you are really concerned about the performance impacts of an insert,
you might be better advised to feed the row data to a separate process
which handled the actual inserts.


> b) if it will work with PG connection pooling (which I am not
> using yet, but will in the future).

There's no reason why COPY shouldn't work with connection pooling,
unless your connection pooling is trying to understand the statements
deeply on the way through, without understanding PostgreSQL. I can't
think of why someone would write connection pooling that way though!

Certainly it will work fine with pgpool, which is what I would recommend
for connection pooling with PostgreSQL as it is written by someone on
the core team.

Cheers,
Andrew McMillan.

------------------------------------------------------------ ------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
You have a deep interest in all that is artistic.
------------------------------------------------------------ ------------



--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: [Q] PDO use to bind arrays for insert

Hi,
thank you Andrew.

do you know if query planning happens only once
for the prepared Insert statements
where data is fed from within a client loop

foreach ...
execute prepared insert
end

and if it happens once -- is it on the first insert?

And the last question -- with pgpool
do prepared statements last between
different client connections?

that is If I have say apache setup with 5 processes
(and therefore PHP can be invoked within 5 worker processes
simultaneously)

would the Prepared statements last across invocations

thank you




> Using the COPY functionality in PostgreSQL does save on parsing,
> although an insert of many rows in a single insert statement doesn't
> spend any significant amount of it's time parsing the input, and the
> query planning only happens once.
>
> If you are really concerned about the performance impacts of an insert,
> you might be better advised to feed the row data to a separate process
> which handled the actual inserts.
>
>
> > b) if it will work with PG connection pooling (which I am not
> > using yet, but will in the future).
>
> There's no reason why COPY shouldn't work with connection pooling,
> unless your connection pooling is trying to understand the statements
> deeply on the way through, without understanding PostgreSQL. I can't
> think of why someone would write connection pooling that way though!
>
> Certainly it will work fine with pgpool, which is what I would recommend
> for connection pooling with PostgreSQL as it is written by someone on
> the core team.
>
> Cheers,
> Andrew McMillan.
--
V S P
toreason@fastmail.fm

--
http://www.fastmail.fm - Does exactly what it says on the tin


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: [Q] PDO use to bind arrays for insert

On Sun, 2008-11-16 at 23:26 -0500, V S P wrote:
> Hi,
> thank you Andrew.
>
> do you know if query planning happens only once
> for the prepared Insert statements
> where data is fed from within a client loop
>
> foreach ...
> execute prepared insert
> end
>
> and if it happens once -- is it on the first insert?

Yes, it happens once on the first one.

>
> And the last question -- with pgpool
> do prepared statements last between
> different client connections?
>
> that is If I have say apache setup with 5 processes
> (and therefore PHP can be invoked within 5 worker processes
> simultaneously)
>
> would the Prepared statements last across invocations

Prepared statements are per connection, and the actions taken at the
close of a connection to pgpool to ready the pool for connection from a
different client will very likely deallocate any prepared statements.
The developers are usually actively trying to minimise pollution of
state from one connection to the next, but persistence of prepared
statements might be a different kettle of fish...

Read here for more information. In paricular note that PREPARE has it's
greatest impact when used on complex SQL - i.e. probably not INSERT
statements:

http://www.postgresql.org/docs/8.3/interactive/sql-prepare.h tml

If you really need performance on a stream of inserts it probably is
better to feed them to a separate daemon to specifically handle them
serially, otherwise any peak load on your website is going to also peak
load your DB server. If you can stream the inserts separately you can
potentially shift that load. Obviously no good if your inserts aren't
blind, but when there are large streams of them they often are only
looked at statistically at a later time.

Your questions here are really getting beyond the scope of the pgsql-PHP
list though, and you might want to try asking them on pgsql-general or
something where you will have a wider resource of PostgreSQL experts...

Cheers,
Andrew McMillan.

------------------------------------------------------------ ------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
"... all the modern inconveniences ..."
-- Mark Twain
------------------------------------------------------------ ------------



--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: [Q] PDO use to bind arrays for insert

Hi,
thank you very much
the article you referenced is what I was looking for.




>
> Prepared statements are per connection, and the actions taken at the
> close of a connection to pgpool to ready the pool for connection from a
> different client will very likely deallocate any prepared statements.
> The developers are usually actively trying to minimise pollution of
> state from one connection to the next, but persistence of prepared
> statements might be a different kettle of fish...
>
> Read here for more information. In paricular note that PREPARE has it's
> greatest impact when used on complex SQL - i.e. probably not INSERT
> statements:
>
> http://www.postgresql.org/docs/8.3/interactive/sql-prepare.h tml
--
V S P
toreason@fastmail.fm

--
http://www.fastmail.fm - A no graphics, no pop-ups email service


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php