Hello, Limiting the number of rows or removing the virtual field, it works. So, as far as I understand, this is an issue related to the virtual field defined for voting_center.
It also works if I use db.executesql to get the id's and then db.voting_center(id) to load the voting_center record. I used this last solution since I can't remove the custom field from voting_center. Anyway, I don't understand why this happens, the virtual field is lazy. Before: for reg in db(db.voting_center).select(): ... Now: for (id, ) in db.executesql(db(db.voting_center)._select( db.voting_center.id, orderby='id')): reg = db.voting_center(id) The geometry field is used to saved the location of the voting_center (see postgis) and it is not a custom field. Thanks Regards, Santiago On Sat, Jul 7, 2012 at 11:03 PM, Massimo Di Pierro < massimo.dipie...@gmail.com> wrote: > the DAL db(..).select(...) does two things: > 1) generates and executes the SQL (no overhead) > 2) parses and normalizes the output in a database independent way > > 2) takes time proportional to the number of rows you select. You select > 66000 rows and you tell us it takes 40 minutes? That is more than 30ms for > row. This cannot be explained with 2) only. Something else is going on. > perhaps a virtual field? > > It would help if you could try isolate the problem selecting less and less > columns until you figure out where the time goes. You could also try use > limitby to limit the number of rows to see if the creation of the Rows > object is hitting some memory limit. > > Massimo > > > On Thursday, 5 July 2012 16:08:01 UTC-5, Santiago wrote: >> >> Hello, >> >> Do you know why the same query, takes more than 40 minutes to get >> resolved using DAL and less than a second using raw SQL ? >> >> This is the code using DAL >> >> db(db.voting_center).select() >> >> This is the code using raw SQL >> >> db.executesql('SELECT voting_center.id, voting_center.election, >> voting_center.full_id_indra, voting_center.latitude, >> voting_center.longitude, voting_center.geometry, voting_center.circuit, >> voting_center.name, voting_center.identification, voting_center.cue, >> voting_center.annex, voting_center.source, voting_center.external_id, >> voting_center.locality, voting_center.address, voting_center.provisional, >> voting_center.definitive FROM voting_center WHERE (voting_center.id > >> 0);') >> >> electoral-dev=# select count(1) from voting_center; >> count >> ------- >> 66442 >> (1 row) >> >> Column | Type | >> Modifiers | Storage | Description >> ----------------+-------------**-----------+------------------** >> ------------------------------**------------+----------+------**------- >> id | integer | not null default >> nextval('voting_center_id_seq'**::regclass) | plain | >> election | integer | not null >> | plain | >> latitude | character varying(512) | >> | extended | >> longitude | character varying(512) | >> | extended | >> circuit | integer | >> | plain | >> identification | character varying(512) | not null >> | extended | >> cue | character varying(512) | >> | extended | >> annex | character varying(512) | >> | extended | >> source | character varying(512) | >> | extended | >> external_id | character varying(512) | >> | extended | >> locality | character varying(512) | >> | extended | >> address | character varying(512) | >> | extended | >> provisional | integer | >> | plain | >> definitive | integer | >> | plain | >> geometry | geometry | >> | main | >> name | character varying(512) | not null >> | extended | >> full_id_indra | character varying(12) | >> | extended | >> >> Thanks in advance >> Santiago >> >>