db.executesql(db(db.voting_center)._select()) takes only a few seconds It has one virtual field, but it is lazy. I think it is not necessary, but I'm pasting the code below, is case you want to take a look.
class VotingCenterVirtualField(object): def delete_results(self): def lazy(self=self): r1 = r2 = r3 = True if self.voting_center.definitive: r = db.result(self.voting_center.definitive) log_delete(r.as_dict()) r1 = r.delete_results() and r.delete_record() if self.voting_center.provisional: r = db.result(self.voting_center.provisional) log_delete(r.as_dict()) r2 = r.delete_results() and r.delete_record() for ps in self.voting_center.polling_station.select(): r3 = r3 and db.polling_station(ps.id).delete_results() return r1 and r2 and r3 return lazy Thanks, Regards Santiago On Thu, Jul 5, 2012 at 6:17 PM, Anthony <abasta...@gmail.com> wrote: > After executing the query, the DAL processes the returned results in order > to create a Rows object. That process takes longer the more fields and the > more rows there are, but 40 minutes still sounds excessive for 66K rows. > How long does it take if you do > db.executesql(db(db.voting_center)._select()), which will run the SQL > generated by the DAL but will not parse the results into a Rows object? > What does your db.voting_center table definition look like? Any virtual > fields (in particular, that do additional queries)? > > Anthony > > > On Thursday, July 5, 2012 5:08:01 PM UTC-4, 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 >> >> > On Thursday, July 5, 2012 5:08:01 PM UTC-4, 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 >> >>