On Friday, March 9, 2012 2:18:58 PM UTC-5, viniciusban wrote: > > Anthony, I'm not supposed to retrieve tens os thousands records from a > single query in a web app
It's all a question of what each layer is doing. Let's say you have a database with 100,000 records. You need a summary: select sum(cost) from items where (units*unit_cost - :discount >= cost); where "discount" is an input you received from the web page. You got one line of response, but unless you have an index on (units*unit_cost - cost) AND a smart optimizer, your database will do a full scan. Now, what if your criterion cannot be expressed in SQL at all? You have to do the full scan in the app. It might not even take long. However, you need all the records in your app to do that. > I would address this problem to app structure, instead of to DAL. > For my app, a < 10 line change in the call to select (entirely in app code, though relying on DAL internals), produced x60 speedup. Restructuring the app would have been a much bigger and more risky undertaking. The suggestion breaks that 10 line change into two 5 line changes, one inside DAL, and one outside -- that makes it much more general, and not dependent on DAL internals. > Anyway, we can execute raw SQL statements via DAL, right? > Yes, that's how my speedup is implemented right now (Issue 701<http://code.google.com/p/web2py/issues/detail?id=701>has all the details)
