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

Thank you Fernando for reproducing this.<br /> I suspect there must be some part of code in the <br /><pre wrap="">*
foreach row * for each column</pre> loops that result inefficient only on some machines or OS's (I'm using
Archlinux).<br/> 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,
changingcolumn separators, text delimiters and encoding.<br /><br /> Could it be possible that the use of e.g. error
catchingin the loops results in poor performance on some systems? (I'm not a c++ programmer ...)<br /><br /><br /><br
/><br/><br /><br /><br /> On 11/01/2011 11:23 AM, Guillaume Lelarge wrote: <blockquote
cite="mid:1320142985.2122.13.camel@localhost.localdomain"type="cite"><pre wrap="">On Mon, 2011-10-31 at 18:26 -0300,
FernandoHevia 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><small><font face="Verdana"><small><b>Boris
Pezzatti<br/> Swiss Federal Research Institute WSL<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>Researchunit Community Ecology<br /> Team Insubric Ecosystems<br /> via Belsoggiorno 22<br />
CH-6500Bellinzona <br /> Switzerland</small></font></small></td><td style="vertical-align: top; text-align:
right;"><small><fontface="Verdana"><small> phone direct ++41 91 821 52 32<br /></small></font><font
face="Verdana"><small>phone</small></font><font face="Verdana"><small>++41 91 821 52 30<br /> fax ++41 91 821 52 39<br
/><aclass="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 />