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