Anthony, I'm not supposed to retrieve tens os thousands records from a single query in a web app.
I would address this problem to app structure, instead of to DAL. Anyway, we can execute raw SQL statements via DAL, right? -- Vinicius Assef On Fri, Mar 9, 2012 at 4:12 PM, Anthony <abasta...@gmail.com> wrote: > 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. >>> >
