If it is only slow the first time the page is loading then maybe it is
a web page issue rather than a database issue. You can easily look at
how long each component took to load. In Chrome this is in the
javascript console (ctrl-shift-J) and "network" tab. In Firefox it is
in Firebug under the "net" tab.
On Dec 1, 2:57 pm, lyn2py <lyn...@gmail.com> 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 <abasta...@gmail.com> 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 <abas...@gmail.com> 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!
