how many of those fields are references?
what is the geometry field? is that a custom field in web2py? parsing
that might be taking some time.....
On Friday, July 6, 2012 5:37:47 PM UTC-7, Anthony wrote:
>
> 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
>>>>
>>>>
>>
