Do the db timings differ between first load and subsequent reloading? What are the times?
On Thursday, December 1, 2011 9:57:59 AM UTC-5, lyn2py wrote: > > 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! > >
