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

Hello,

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...

More especially that for each row we do :
field1 = ...
field2 = ...
And then
$sql_insert = "INSERT ..." . field1 ....


Does someone has a better suggestion ?

Thanks a lot in advance !

Denis

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

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

That would depend on amount of control you have over the data source.
Your problem may be that the fields you are filtering on the data source
are not indexed and therefore the query would do a full table scan on
the source before returning records to load. Knowing your main slow
down is number of records going between your systems, looking into this
may provide you some additional gains.

Below are three methods I am currently using to pass records between
RDMSs.
1. You can Pull from the data source (the method you are currently
using).
2. You can Push from the data source (in our case we load a temp table
on the receiving machine with just the records we need, once the
temp table has been loaded a process periodically updates the main
table from our temp table and deletes the records from or updates a
flag in the temp table)
3. You can Push records in a text file between the systems using FTP and
programmatically load the records in code (skipping ODBC).

Regardless of the method you use, I you are moving lots of records
between systems you might want to consider staging the data periodically
into a temp table based on some factor (date/time/indicator of your
choosing) that you can then use to load into your production table(s)
using a Select into.

Not knowing where the time factor is impacting you hopefully this gives
you some additional possibilities to consider.

Good luck,

Jeff
=20
-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Denis BUCHER
Sent: Wednesday, July 22, 2009 1:31 PM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Best way to import data in postgresl (not "COPY")

Hello,

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...

More especially that for each row we do :
field1 =3D ...
field2 =3D ...
And then
$sql_insert =3D "INSERT ..." . field1 ....


Does someone has a better suggestion ?

Thanks a lot in advance !

Denis

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

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

You could group the insert into batches using:

INSERT INTO table (field1, field2)
Select value1, value2
Union all Select value1, value2
Union all Select value1, value2
Union all Select value1, value2
Union all Select value1, value2
Union all Select value1, value2;


> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org=20
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Denis BUCHER
> Sent: Wednesday, July 22, 2009 11:31 AM
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Best way to import data in postgresl (not "COPY")
>=20
>=20
> Hello,
>=20
> I have a system that must each day import lots of data from=20
> another one. Our system is in postgresql and we connect to=20
> the other via ODBC.
>=20
> Currently we do something like :
>=20
> SELECT ... FROM ODBC source
> foreach row {
> INSERT INTO postgresql
> }
>=20
> The problem is that this method is very slow...
>=20
> More especially that for each row we do :
> field1 =3D ...
> field2 =3D ...
> And then
> $sql_insert =3D "INSERT ..." . field1 ....
>=20
>=20
> Does someone has a better suggestion ?
>=20
> Thanks a lot in advance !
>=20
> Denis
>=20
> --=20
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:=20
> http://www.postgresql.org/mailpref/pgsql-odbc
>=20

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc