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!
>
>
