It seems odd that your join query takes several seconds on a local machine with a small amount of data.
You can use web2py populate to load your database with more entries for a more definitive test. Postgresql is very solid. Whatever dbms you select, install it and develop with it at the beginning. You need to do this because the drivers are not 100% plug compatible. Your example is a one-to-one join. One-to-many and many-to-many are much more interesting, both on the db model side and on the view/ presentation side. At some point you might want to play with SQLFORM.grid and SQLFORM.smartgrid. They handle many of the boring, repetitive, mundane tasks involved in application development. On Dec 1, 2:36 am, lyn2py <[email protected]> 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!
