Please do the tests I asked. Time time: rows = db(db.voting_center).select(...)
where ... is a list of fields. Start with one field and add one field at the time. What timing do you get? You may have a virtual field that causes the slow down. This is an effect of your code or an effect of low memory. Hard to say without more details. Massimo On Tuesday, 10 July 2012 11:42:40 UTC-5, Santiago wrote: > > Because the same query, using raw sql is resolved in seconds. > > The reason I do reg = db.voting_center(id) is because I need the > voting_center object. > > If I do this: > > for reg in db(db.voting_center).select(): > > It takes a lot of time to get resolved (40 mins, maybe more ...). > > On Tue, Jul 10, 2012 at 1:33 PM, Massimo Di Pierro < > massimo.dipie...@gmail.com> wrote: > >> I do not understand. Why are you doing this? >> >> for (id, ) in db.executesql(db(db.voting_**center)._select(db.voting_** >> center.id <http://db.voting_center.id/>, orderby='id')): >> reg = db.voting_center(id) >> >> Thats's N+1 select()s where N is the number of records. >> >> On Tuesday, 10 July 2012 07:21:37 UTC-5, Santiago wrote: >>> >>> 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 <http://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 >>>>> >>>>> >>> >
