Re: very slow when writing query to file - Mailing list pgadmin-support

<br /> or maybe the retrieved data in RAM are somehow lazy bound ... ? <br /><br /><br /><br /> On 11/02/2011 08:58 AM,
borispezzatti wrote: <blockquote cite="mid:4EB0F81E.70708@wsl.ch" type="cite"> Thank you Fernando for reproducing
this.<br/> I suspect there must be some part of code in the <br /><pre wrap="">* for each row * for each column</pre>
loopsthat result inefficient only on some machines or OS's (I'm using Archlinux).<br /> In fact the extra time I and
Fernandoget can not only be attributed to adding commas, " and line feeds. The same file which I retrived in more than
onehour (32Mb), could be saved with openoffice in 10 seconds, changing column separators, text delimiters and
encoding.<br/><br /> Could it be possible that the use of e.g. error catching in the loops results in poor performance
onsome systems? (I'm not a c++ programmer ...)<br /><br /><br /><br /><br /><br /><br /><br /> On 11/01/2011 11:23 AM,
GuillaumeLelarge wrote: <blockquote cite="mid:1320142985.2122.13.camel@localhost.localdomain" type="cite"><pre
wrap="">OnMon, 2011-10-31 at 18:26 -0300, Fernando Hevia wrote:
 
</pre><blockquote type="cite"><pre wrap="">[...]
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

</pre></blockquote><pre wrap="">That's right.

</pre><blockquote type="cite"><pre wrap="">The delay clearly occurs in step 2.
</pre></blockquote><pre wrap="">That's also right.

</pre><blockquote type="cite"><pre wrap="">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.

</pre></blockquote><pre wrap="">Nope, step 2 does a lot of work.

</pre><blockquote type="cite"><pre wrap="">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).

</pre></blockquote><pre wrap="">I don't have the same numbers:

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

</pre><blockquote type="cite"><pre wrap="">The file is being written at an avg 60 KB per second, which is extremely
slow.
</pre></blockquote><pre wrap="">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
itto 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.


</pre></blockquote><br /><div class="moz-signature">-- <br /></div></blockquote><small><font
face="Verdana"><small><b>BorisPezzatti<br /> Swiss Federal Research Institute WSL<br
/></b></small></font></small><tableborder="0" cellpadding="2" cellspacing="2" style="text-align: left; width:
400px;"><tbody><tr><tdstyle="vertical-align: top;"><small><font face="Verdana"><small>Research unit Community
Ecology<br/> Team Insubric Ecosystems<br /> via Belsoggiorno 22<br /> CH-6500 Bellinzona <br />
Switzerland</small></font></small></td><tdstyle="vertical-align: top; text-align: right;"><small><font
face="Verdana"><small>phone direct ++41 91 821 52 32<br /></small></font><font face="Verdana"><small>phone
</small></font><fontface="Verdana"><small>++41 91 821 52 30<br /> fax ++41 91 821 52 39<br /><a
class="moz-txt-link-abbreviated"href="mailto:boris.pezzatti@wsl.ch" moz-do-not-send="true">boris.pezzatti@wsl.ch</a><br
/><aeudora="autourl" href="http://www.wsl.ch/"
moz-do-not-send="true">http://www.wsl.ch</a></small></font></small></td></tr></tbody></table><br/><br /><div
class="moz-signature">--<br /></div><small><font face="Verdana"><small><b>Boris Pezzatti<br /> Swiss Federal Research
InstituteWSL<br /></b></small></font></small><table border="0" cellpadding="2" cellspacing="2" style="text-align: left;
width:400px;"><tbody><tr><td style="vertical-align: top;"><small><font face="Verdana"><small>Research unit Community
Ecology<br/> Team Insubric Ecosystems<br /> via Belsoggiorno 22<br /> CH-6500 Bellinzona <br />
Switzerland</small></font></small></td><tdstyle="vertical-align: top; text-align: right;"><small><font
face="Verdana"><small>phone direct ++41 91 821 52 32<br /></small></font><font face="Verdana"><small>phone
</small></font><fontface="Verdana"><small>++41 91 821 52 30<br /> fax ++41 91 821 52 39<br /><a
class="moz-txt-link-abbreviated"href="mailto:boris.pezzatti@wsl.ch">boris.pezzatti@wsl.ch</a><br /><a eudora="autourl"
href="http://www.wsl.ch/">http://www.wsl.ch</a></small></font></small></td></tr></tbody></table><br/>