Niphold, Thanks for the reply.
My first question is about this: http://www.postgresql.org/docs/8.1/static/sql-cluster.html. Sorry if I did not explicit about what I was referring, as it is postgresql specific. I was wondering if you would see different figures or not, because order by is influenced by index clustering (i.e. a presorted table around that index). My interest is about the high initial step in postgresql response, i.e. the response seems a function of the number of records + an initial offset. I was wondering if it was something related to connection and remote backend initialization. How the dal pooling works is known to me in depth, due to the fact that I contributed fix some little bugs there. Roundtrip of queries are clear to me. Before psycopg2 there was a psycopg1: the base of code was written by me, so I suppose I have done my homework understanding how postgresql connections work. mic 2012/2/10 Niphlod <niph...@gmail.com>: > 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 > >
