I do not understand. Why are you doing this?
for (id, ) in db.executesql(db(db.voting_center)._select(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, 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
>>>
>>>
>
