just take a look to
http://web2py.com/book/default/chapter/06#Connection-pooling for
understanding how DAL handles connections.
Your initial question was a concern on speed on aggregate and unions, and
the script you provided clearly use one and only one db connection.
I'm sorry if I misunderstood, but having to "aggregate" or "union" involved
in a query to fetch/show results involves anyway one and only one db
connections.
I stated (and tested) that the difference in making 2 distinct queries and
retrieve the results against making one query that returns similar results
is negligible in this case.
The "two queries" method involves clearly two "roundtrips" to the database,
but on the same connection.
When you use web2py, you can rely on "pool_size" parameter (as explained
into the book) to avoid making and closing a connection for every request.
Here "*request*" is a user requesting a page, not your application making a
query to the database. You could do
def index():
for a in range(1000):
result = db(db.emp.id>0).select()
in one controller. What will be the behaviour ? User request the "index"
page, DAL istantiate a new connection, DAL make 1000 queries to the db
using the same connection.
If you use no "pool_size", then every time a user request the "index" page,
the DAL will instantiate a new connection, make 1000 queries, and close the
connection.
If you use the "pool_size" parameter, then the DAL fetch a connection from
the pool, make 1000 queries, and put the connection back into the pool,
ready to be used in subsequent requests of users going to the "index" page.
I hope I cleared all your doubts, if not, please ask :D
