From: | Denis BUCHER <dbucherml(at)hsolutions(dot)ch> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Best way to import data in postgresl (not "COPY") |
Date: | 2009-07-24 20:43:06 |
Message-ID: | 4A6A1CDA.8080606@hsolutions.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Hunsberger | 2009-07-24 21:13:00 | Find difference between two Text fields |
Previous Message | MS | 2009-07-24 20:40:07 | Re: Very slow joins |