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