Good tip! I didn't realize that the dbstats showed database timings. I tried my code again (but did not reference dbstats at the time). I noticed that only the first time the page is loaded (after the changes in code) that it takes very long (a few seconds). Subsequent reloading seemed to be almost immediate.
Based on subsequent reloads, the JOIN performs faster than recursive SELECT. On Dec 1, 10:34 pm, Anthony <[email protected]> wrote: > Try adding {{=response.toolbar()}} to your view. That will include a button > for db timings showing the time for each query. Compare the join version > with the recursive select version. > > Anthony > > > > > > > > On Thursday, December 1, 2011 9:25:08 AM UTC-5, lyn2py wrote: > > > Thanks guys for the assistance. > > > This is my testing code... > > #controller > > def show(): > > discussion = db((db.discussion.id==request.args(0)) & > > (db.discussion.created_by==db.auth_user.id)).select().first() > > replies = db((db.replies.discussion_id==request.args(0)) & > > (db.replies.created_by==db.auth_user.id)).select() > > return dict(discussion=discussion,replies=replies) > > > #view > > {{for d in discussion:}} > > <h1>{{=d.discussion.title}}</h1> > > <div>{{=d.discussion.description}} - {{=d.auth_user.first_name}}</div> > > {{pass}} > > {{for r in replies:}} > > <div class="reply">{{=r.replies.reply}} - {{=r.auth_user.first_name}}</ > > div> > > {{pass}} > > > On Dec 1, 9:38 pm, Anthony <[email protected]> wrote: > > > A simple join with a small amount of data probably shouldn't be taking > > > several seconds, so perhaps something else is going on here. Can you post > > > your code and data? > > > > Recursive selects will do a query for each record, so you should only use > > > them if you need to extract just one or a few records. Otherwise, a join > > > should be better. > > > > Anthony > > > > On Thursday, December 1, 2011 2:36:49 AM UTC-5, lyn2py wrote: > > > > > Appreciate your input on this guys :) > > > > > I have two tables that are related. > > > > >> db.define_table('person', Field('name')) > > > > >> db.define_table('dog', Field('name'), Field('owner', db.person)) > > > > > According to the book: > > > >http://web2py.com/book/default/chapter/06#Inner-Joins > > > > > I can JOIN: > > > > >> rows = db(db.person.id==db.dog.owner).select() > > > > >> rows = db(db.person).select(join=db.person.on(db.person.id > > > > ==db.dog.owner)) > > > > > I can recursive SELECT: > > > > >> dog.owner.name > > > > > And recursive SELECT is supposed to be slower than JOIN, because there > > > > are more database transactions? > > > > I tried both methods out (disclaimer: I do not have many testing > > > > entries in the database, and I'm currently observing this on localhost/ > > > > sqlite) and I have found JOIN to be slower, I was literally waiting > > > > for the page to load, watching the loading bar... a few seconds. > > > > But recursive SELECT loads the page in a snap. > > > > > Is this correct behavior? > > > > Should I use recursive select for few entries (less than, say, 30) and > > > > JOIN for many? > > > > If I move the site to production, what database(s) would you > > > > recommend, and for which method (recursive select vs join)? > > > > > Thanks!
