pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: V S P <toreason@fastmail.fm>
To: knl@bitflop.com
Cc: pgsql-php@postgresql.org
Subject: Re: [Q] PDO use to bind arrays for insert
Date: Sun, 16 Nov 2008 22:01:13 -0500
Message-ID: <1226890873.32288.1285117971@webmail.messagingengine.com> (raw)
In-Reply-To: <20081117002630.d86160fd.knl@bitflop.com>
References: <1226803530.24888.1284994985@webmail.messagingengine.com>
	<492093A5.7040606@gmail.com>
	<20081117002630.d86160fd.knl@bitflop.com>

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




view thread (7+ messages)  latest in thread

Message-ID: <1226890873.32288.1285117971@webmail.messagingengine.com>
Permalink:  ../1226890873.32288.1285117971@webmail.messagingengine.com/
Also on:    postgresql.org/message-id/1226890873.32288.1285117971@webmail.messagingengine.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: toreason@fastmail.fm, knl@bitflop.com
  Subject: Re: [Q] PDO use to bind arrays for insert
  In-Reply-To: <1226890873.32288.1285117971@webmail.messagingengine.com>
  References: <1226803530.24888.1284994985@webmail.messagingengine.com> <492093A5.7040606@gmail.com> <20081117002630.d86160fd.knl@bitflop.com> <1226890873.32288.1285117971@webmail.messagingengine.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox