As I'm a relentless DBA after all, and I enjoy facts over estimates, and I live for these kind of things....I concocted a script that benches different methods. pyDAL interaction has been left out of the picture, as it'll probably affect estimates. Raw T-SQL under the hood, psycopg2 driver.
Every test case is based on 1000 iterations: fresh db, vacuumed and defragmented indexes. speed_executesql1() does a simple join, then distincts the results (would have been the easiest answer) select distinct news_categories.id, news_categories.name from news , news_categories where news.category = news_categories.id speed_executesql2() does a subselect - as advised here - select news_categories.id, news_categories.name from news_categories where news_categories.id in ( select distinct category from news ) speed_executesql3() does the "two queries thingy" select distinct category from news then parses the results, then sends select news_categories.id, news_categories.name from news_categories where id IN (%s) The resultsets are always identical.................. I estimated a set that covers from 25% to 75% the "distribution" of categories (for the less accustomed to "database tuners", it means sets that have only 25% of the categories used by at least one news, to sets that use 75% of the categories): this lets us test if a method is more or less convenient if the number of categories returned is small or high, in relation to the total set of available categories AND sets involving a cardinality ratio from 40 to 1000 (i.e. from news = categories * 100 to news = categories * 1000): this helps to identify if a method is more or less convenient if the number of news is more or less high than the number of categories. Results indicate that the mean of the results seems to be in favour of doing the subselect. speed_executesql1() total=117.208569 avg=0.117209 speed_executesql2() total=33.434958 avg=0.033435 speed_executesql3() total=52.830114 avg=0.052830 -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.