Yves, >>From my experience with a Java webapp framework written by a friend of > mine, retrieving IDs first, then the objects in a second pass, has been > one of the best design decisions he made. I liked the idea so much that > we started implementing it in our custom ORM built on Zope, and > witnessed a very significant speed improvement. Why this works: > > 1) the 1st phase (retrieving IDs), even with complex joins and filters, > can be really > fast, since the database won't have to deal with any real data, only > primary keys and indexes. > > 2) the 2nd phase (retrieving objects) is made really fast too by using > aggressive caching. Except for the 1st access to an object, there won't > be any more actual database "select", since the object will be > retrieved from the cache. > > A note about the 2nd phase: ideally, it should be done in a single > query. For example, if phase one returned a list of (1, 2, 3, 4, 5), > and we already have (1, 2, 3) in the cache, the second phase should do > a single select with "WHERE id IN (4, 5)". Your comment suggests that > SQLObject may do it as 2 distinct selects, which indeed would be > suboptimal.
I assume you have a global cache right? Otherwise I do wonder how this works when several clients update the database. Now I don't quite understand the benefit of your technique. You say that by only requesting IDs in the first query you reduce the load of data retrieved by the database, but why don't you simply select the columns you do need to process? I mean in the second select it is not sure that you will need all the colums and you might waste some CPU anyway. Besides, it is also possible that between the time you request an ID and the time you actually fetch the row for that ID, this one may have been deleted and you will hit an error. I really fail to understand the benefit of that technique but I'm not a database/ORM expert anyway. - Sylvain --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "TurboGears" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/turbogears -~----------~----~----~----~------~----~------~--~---
