Re: very slow when writing query to file

... or (I promise this is the last guess)  each row is appended to the file in the the loop, and on some OS's there is a problem keeping open the file (so that each time the file must be opened again ...  adding a lot of extra time). Maybe creating all the "virtual file" in a variable and storing all at once (or let say in chunks of 100 Mb) could solve the problem ...






On 11/02/2011 09:10 AM, boris pezzatti wrote:

or maybe the retrieved data in RAM are somehow lazy bound ... ?



On 11/02/2011 08:58 AM, boris pezzatti wrote:

Thank you Fernando for reproducing this.
I suspect there must be some part of code in the

* for each row * for each column

loops that result inefficient only on some machines or OS's (I'm using Archlinux).
In fact the extra time I and Fernando get can not only be attributed to adding commas, " and line feeds. The same file which I retrived in more than one hour (32Mb), could be saved with openoffice in 10 seconds, changing column separators, text delimiters and encoding.

Could it be possible that the use of e.g. error catching in the loops results in poor performance on some systems? (I'm not a c++ programmer ...)







On 11/01/2011 11:23 AM, Guillaume Lelarge wrote:

On Mon, 2011-10-31 at 18:26 -0300, Fernando Hevia wrote:

[...]
I could reproduce the issue in a fresh Windows 7 install with no other apps
running other than pgAdmin v1.14.0.
>From what I could see, the execute-to-file function runs in 2 stages:
1. Rows are retrieved from DB server to RAM
2. Rows are written from RAM to file

That's right.

The delay clearly occurs in step 2.

That's also right.

While with small datasets (<2000) the writing to disk delay is barely
perceivable, when the number of rows is incremented (>10k) it is quite
distinctive how step 1 keeps completing in the expected time frame but step
2 takes much much longer. In any case it should be the other way around.

Nope, step 2 does a lot of work.

With 10k rows (2.3 MB) it took 3.5 seconds to retrieve data from DB and 40
seconds to write the file to an SATA 7200 disk with write-through cache.
With 100k rows (23 MB) the DB retrieve went for 35 seconds while the file
writing part took over 4 minutes (didn't wait for it to finish).

I don't have the same numbers:

*    1000 : 1s
*   10000 : 1s
*  200000 : 7s
* 1000000 : 18s

The file is being written at an avg 60 KB per second, which is extremely
slow.

If the only thing pgAdmin does was writing, I would agree. But,
actually, it does a lot more things:

* for each row * for each column   * adds the column separator, if needed   * grabs one cell's value   * quotes the value, if needed (which also means doubling the quote     if it's within the value) * adds the line separator * converts it to the encoding, if needed * writes it to the file

That could take some time.

I searched if there were some parts that took much longer than others,
but failed to find one.

--

Boris Pezzatti
Swiss Federal Research Institute WSL

Research unit Community Ecology Team Insubric Ecosystems via Belsoggiorno 22 CH-6500 Bellinzona Switzerland

phone direct ++41 91 821 52 32 phone ++41 91 821 52 30 fax ++41 91 821 52 39 boris.pezzatti@wsl.ch http://www.wsl.ch

Boris Pezzatti
Swiss Federal Research Institute WSL

Research unit Community Ecology Team Insubric Ecosystems via Belsoggiorno 22 CH-6500 Bellinzona Switzerland

phone direct ++41 91 821 52 32 phone ++41 91 821 52 30 fax ++41 91 821 52 39 boris.pezzatti@wsl.ch http://www.wsl.ch

Boris Pezzatti
Swiss Federal Research Institute WSL

Research unit Community Ecology Team Insubric Ecosystems via Belsoggiorno 22 CH-6500 Bellinzona Switzerland

phone direct ++41 91 821 52 32 phone ++41 91 821 52 30 fax ++41 91 821 52 39 boris.pezzatti@wsl.ch http://www.wsl.ch