On Monday, May 14, 2012 11:50:06 AM UTC-4, Anthony wrote: > > >> 1. Your "adviewer/viewads" makes 10 calls to the database. Try to >> optimize this either by writing fewer queries, creating a view, and/or >> only >> selecting fields that you need. Also make sure you get the criteria right >> so that you (ideally) don't have any extra, unneeded rows. >> >> If it's feasible, also consider caching the queries for some amount of > time (assuming the results don't change too frequently). >
This is a good point. For things that may not change a whole lot, I usually set the time_expires=3600 or even None. Give the key a well-defined name, and then when something changes, run cache.ram.clear(key) so that the next query will get the changes. > >> 1. When you absolutely have to load a few thousand rows (or more) in >> a query (you should avoid this whenever possible), then try using >> "db.executesql(query)" to manually execute a hand-crafted SQL query. This >> will always be faster than using the DAL directly. >> >> Note, the difference in speed is due to the fact that the DAL won't be > converting the results set to a Rows object -- so you won't have the > convenience of dealing with DAL Rows and Row objects. If you do > db.executesql(query, > as_dict=True), it will convert to a list of dictionaries (which is still > faster than converting to a Rows object). > >> >> 1. Another point about executesql: The obvious issue is reduced >> portability, but if you are only planning on using PostgreSQL, then you >> can >> hand-craft a SQL query and profile it against PostgreSQL for maximum >> performance. Once you've got it giving only the data you want, then you >> can >> copy and paste that query into executesql. >> >> If you want to use db.executesql() but remain portable, you can still > have the DAL generate the SQL for you by using the ._select() method: > > db.executesql(db(query)._select(...)) > > Obviously in that case you don't get to hand optimize the SQL, but you > still get the speed advantage of not converting the results to a Rows > object (which is only significant for large results sets). > While true, since he is going for performance in a high-traffic environment that requires low-latency, such as a site that serves ads, he would definitely want to hand-craft the SQL for complex and large queries that slow things down. I wouldn't recommend doing it for every query, just the slow ones. > > Anthony > >
