mcm made some initial attempts and indicated about a 25% improvement, with
the expectation that some further improvement could be made with more
effort:
https://groups.google.com/forum/#!topic/web2py-developers/Tp6HXsSf7lk/discussion.
However, it sounded like we wouldn't be likely to get enough improvement to
substantially help with large selects (i.e., tens of thousands of rows) --
for that case, we probably need something like what nick name is proposing
(which will actually be easier to implement, anyway).
Anthony
On Friday, March 9, 2012 1:48:06 PM UTC-5, spiffytech wrote:
>
> Before we add special features to the DAL, has anyone profiled the DAL
> with queries and data similar to nick name's to see if there's any
> low-hanging fruit we can tackle to speed up the normal select()?
>
> nick name, can you please provide your table schema so we can test
> performance on data sets similar to yours?
>
>
> On Thursday, February 9, 2012 1:51:47 PM UTC-5, nick name wrote:
>>
>> One of my controllers need to go through a lot of records to provide a
>> meaningful answer -- as in, 60k records.
>>
>> Just loading them from the database takes about 100ms
>> (db.executesql("select * from table order by id;")); Doing the same through
>> DAL takes over 6 seconds. I realize that the DAL does do a lot of
>> additional work, which in general is helpful -- but I can do without all
>> the parsing / Rows() generation for this.
>>
>> What do people here think about adding a db.rawselect(...), which is a
>> slim rapper for db.executesql(db._select()) .... that wraps everything with
>> a named tuple? It solves most of the speed problem when it is needed, but
>> still maintains a lot of the features of the SQL DAL processing.
>>
>>
On Friday, March 9, 2012 1:48:06 PM UTC-5, spiffytech wrote:
>
> Before we add special features to the DAL, has anyone profiled the DAL
> with queries and data similar to nick name's to see if there's any
> low-hanging fruit we can tackle to speed up the normal select()?
>
> nick name, can you please provide your table schema so we can test
> performance on data sets similar to yours?
>
>
> On Thursday, February 9, 2012 1:51:47 PM UTC-5, nick name wrote:
>>
>> One of my controllers need to go through a lot of records to provide a
>> meaningful answer -- as in, 60k records.
>>
>> Just loading them from the database takes about 100ms
>> (db.executesql("select * from table order by id;")); Doing the same through
>> DAL takes over 6 seconds. I realize that the DAL does do a lot of
>> additional work, which in general is helpful -- but I can do without all
>> the parsing / Rows() generation for this.
>>
>> What do people here think about adding a db.rawselect(...), which is a
>> slim rapper for db.executesql(db._select()) .... that wraps everything with
>> a named tuple? It solves most of the speed problem when it is needed, but
>> still maintains a lot of the features of the SQL DAL processing.
>>
>>
