El día lunes, febrero 03, 2020 a las 10:01:04a. m. -0600, Steven Lembark escribió: > On Fri, 31 Jan 2020 19:24:41 +0100 > Matthias Apitz <guru@xxxxxxxxxxx> wrote: > > > Hello, > > > > Since ages, we transfer data between different DBS (Informix, Sybase, > > Oracle, and now PostgreSQL) with our own written tool, based on > > Perl::DBI which produces a CSV like export in a common way, i.e. an > > export of Oracle can be loaded into Sybase and vice versa. Export and > > Import is done row by row, for some tables millions of rows. > > > > We produced a special version of the tool to export the rows into a > > format which understands the PostgreSQL's COPY command and got to know > > that the import into PostgreSQL of the same data with COPY is 50 times > > faster than with Perl::DBI, 2.5 minutes ./. 140 minutes for around 6 > > million rows into an empty table without indexes. > > > > How can COPY do this so fast? > > DBI is a wonderful tool, but not intended for bulk transfer. It > is useful for post-processing queries that extract specific > data in ways that SQL cannot readily handle. > > One big slowdown is the cycle of pull-a-row, push-a-row involves > signiicant latency due to database connections. That limits the > throughput. I should have mentioned this: the export is done on Linux to file and the import with that tool is read from such files. matthias -- Matthias Apitz, ✉ guru@xxxxxxxxxxx, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
