Hmm, not sure what's going on. What happens if you remove the virtual field?
On Thursday, July 5, 2012 5:25:55 PM UTC-4, Santiago wrote:
>
> 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 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
>>>
>>>
>
