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(at)fastmail(dot)fm

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