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