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
>>
>>
