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 < [email protected]> 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 < >> [email protected]> 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 >>>> >>>> >>
