Re: very slow when writing query to file

Hello.

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

The delay clearly occurs in step 2.
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.

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

The file is being written at an avg 60 KB per second, which is extremely
slow.
Streaming the data from the saved file to a copy took under 2 seconds.

On Mon, Oct 31, 2011 at 10:04, boris pezzatti <boris(dot)pezzatti(at)wsl(dot)ch> wrote:

> Actually I do not have access to the server file system.
> What I can not understand is that:
> 1) if I can execute the query and see the data in the table viewer the
> data are also sent over the network
> 2) how does the execute to file command differ with respect to the point
> 1) ? Is each line retrieved separately and written to the file location?
> Or is written first to a temporary location and then copied?
>
> Maybe I'll have to start looking into the source code ....
> Thanks anyway,
>
> Boris
>
>
>
> On 10/31/2011 12:54 PM, Francisco Leovey wrote:
>
> I propose you output to a file located on the same server as the DB and
> then copy that file to your PC
> IMO your problem is network related.
>
> *From:* boris pezzatti <boris(dot)pezzatti(at)wsl(dot)ch> <boris(dot)pezzatti(at)wsl(dot)ch>
> *To:* Francisco Leovey <fleovey(at)yahoo(dot)com> <fleovey(at)yahoo(dot)com>
> *Cc:* "pgadmin-support(at)postgresql(dot)org" <pgadmin-support(at)postgresql(dot)org>
> <pgadmin-support(at)postgresql(dot)org> <pgadmin-support(at)postgresql(dot)org>
> *Sent:* Sunday, October 30, 2011 6:28 PM
>
> *Subject:* Re: [pgadmin-support] very slow when writing query to file
> **
> No, actually it is on my machine ...** ** ** On 10/29/2011 11:01 PM,
> Francisco Leovey wrote:
>
> Is the file where you write the query output located on the same server
> as the DB?
>
> *From:* boris pezzatti <boris(dot)pezzatti(at)wsl(dot)ch> <boris(dot)pezzatti(at)wsl(dot)ch>
> *To:* Guillaume Lelarge <guillaume(at)lelarge(dot)info> <guillaume(at)lelarge(dot)info>
> *Cc:* pgadmin-support(at)postgresql(dot)org
> *Sent:* Saturday, October 29, 2011 5:44 PM
> *Subject:* Re: [pgadmin-support] very slow when writing query to file
> ** Does anyone have any proposition how I could further test where the
> problem is. This is really a strange behaviour, that I noticed with
> different versions of pgAdmin.** Could a firewall produce a difference
> when querying data visually or for a write to file? (should not ...)** **Thank you,
> ** Boris** ** ** Guillaume Lelarge wrote:** > On Wed, 2011-10-26 at 14:57
> +0200, boris pezzatti wrote:** >> I have a postgresql database 8.3 on a
> server. When querying the data** >> with the pgAdmin sql editor, I can
> get an answer in about 10 s, for** >> 100'000 rows. When I'm pressing the
> button to execute the query to a** >> file it takes more than 1 hour to
> get the query results saved (writes** >> about 10 MB in 45 minutes).** >>
> ** >> Am I doing something wrong?** >> Are there some parameters to set?**>>
> ** > Never heard of such a behaviour before. I'm not sure what happens on*
> * > your computer.** > ** > ** ** ** -- Sent via pgadmin-support mailing
> list (pgadmin-support(at)postgresql(dot)org)** To make changes to your
> subscription:** http://www.postgresql.org/mailpref/pgadmin-support** ** **
>
> **
> -- ** *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(dot)pezzatti(at)wsl(dot)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(dot)pezzatti(at)wsl(dot)ch
> http://www.wsl.ch
>