Re: Best way to import data in postgresl (not "COPY")

Hello everyone,

Denis BUCHER a écrit :
> I have a system that must each day import lots of data from another one.
> Our system is in postgresql and we connect to the other via ODBC.
>
> Currently we do something like :
>
> SELECT ... FROM ODBC source
> foreach row {
> INSERT INTO postgresql
> }
>
> The problem is that this method is very slow...
> Does someone has a better suggestion ?

Thanks a lot for the help of everyone !

There are the first results of my tries, it's very interesting !!!

a) ON THE DESTINATION (PHP/Postgresql)

1. Preparing INSERT statements (to Postgres) was already a better idea
2. Then using BEGIN WORK COMMIT improved even more
3. At first I didn't realised I could remove quotes escaping thank to
prepare, this improved a little more
4. Then I found something very interesting : pg_send_execute !
(asynchronous)

Inserted lines : 134297
Required time : 292 seconds ([0] without prepare)
Required time : 253 seconds ([1] with prepare) (13% better)
Required time : 224 seconds ([2] with prepare and BEGIN COMMIT) (12% better)
Required time : 221 seconds [3]removed escaping
Required time : 214 seconds ([4] 4% better)

b) ON THE SOURCE (PHP/ODBC)
5. Believe it or not but changing from PHP ODBC to PHP PDO ODBC
>From : http://us2.php.net/manual/en/ref.uodbc.php
To : http://fr.php.net/manual/en/class.pdostatement.php
...helped a LOT :

Inserted lines : 134297
Required time : 25 seconds ([1] [2] [3] [4] [5] + PDO)

Hope it will help other people !

Thanks a lot again to everyone that help me :-)

Denis