Re: memory issue for cur.execute

Il giorno lun, 05/01/2009 alle 12.40 -0600, Randall Smith ha scritto:
> Federico Di Gregorio wrote:
> > Il giorno lun, 05/01/2009 alle 11.51 -0600, Randall Smith ha scritto:
> >> When issuing a select on a large table, I'm seeing high memory
> >> consumption before fetching any records (just during cur.execute).
> > 
> > It's psycopg default behaviour. It will fetch everything from the
> > backend during the execute() call. If you want to use a server-side
> > cursor and fetch only a few rows at a time use a named cursor:
> > 
> > cur = con.cursor("myname")
> > cur.execute('select * from my_large_table')
> > 
> > federico
> 
> Alright.  That makes sense.  There's still a large memory (and CPU)
> footprint for fetchall.  I'm guessing that's a conversion to Python
> types? 

Yes. fetchall() fetches all rows and convert them all, so memory and CPU
usage goes up.

>  Here is a test I did.  I put in a limit to make sure I didn't
> swap.  The result was:
> 
> time for execute: 9.44527792931
> time for fetchall:  5.81065797806
> RAM used for execute: 205 MB
> RAM used for fetchall: 265 MB
> Total RAM used: 470 MB
> 
> I tested again, naming the cursor (without fetching results) and it
> worked as you said it would, but the rowcount returned -1.  Is there a
> way to know how many rows were selected on the server without fetching
> them all?

Currently there is no way native to psycopg. You can probably scroll the
cursor to the end and read the number of moved columns but I am not sure
if it will work.

federico

-- 
Federico Di Gregorio                         http://people.initd.org/fog
Debian GNU/Linux Developer                                [email protected]
INIT.D Developer                                           fog-NGVKUo/i/[email protected]
  Qu'est ce que la folie? Juste un sentiment de liberté si
   fort qu'on en oublie ce qui nous rattache au monde... -- J. de Loctra

_______________________________________________
Psycopg mailing list
Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/psycopg

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkliW9IACgkQvcCgrgZGjevt8gCffUdZC7DnK7uQApQLNjvb2bJL
x2oAn1FKfQQB7oZmNm5+2W/Dxd9tXoJ1
=XJ7v
-----END PGP SIGNATURE-----