On May 8, 2007, at 8:47 PM, Mike Orr wrote:
> > I noticed the ORM was taking a few seconds longer in my batch job. > > Today I converted my web application from Select to ORM so I could add > some methods to the mapped classes, and even tried a relationship with > a backreference (!). That worked fine, but I notice it's doing nested > selects again. > the mapper will always do nested selects for a mapper that is against a select in the first place. in all cases, the selectable you pass to the mapper is the relation to which its mapped, and it will select from all of them in the identical way....i.e. map to a table, the mapper does "select * from table". map to a SELECT, the mapper does "select * from select". this is essential since theres no other way to generically add WHERE, ORDER BY, JOIN, etc. clauses to the mapped relation. its exactly the same as if you created a view in your database and mapped to that. I guess im giong to have to find a way to back this up, but its my belief that the database should be pretty good at optimizing nested selects such that it doesnt make a whole lot of difference. otherwise people would never use views. > I suppose MySQL optimizes the queries and I shouldn't worry about it, > but it's a pity SQLAlchemy doesn't combine the two selects into one > when it compiles the SQL. I'm showing an Entry query because it's > shorter; the Incident table has some three dozen columns that are > duplicated between the main select and the subselect. > yeah its just that its not really possible, since you cant just tack on additional WHERE criterion to a query and expect it to have the same semantics in all cases. by keeping the mapped query encapsulated, it can reference any number of tables, use GROUP BY, HAVING, etc., be text-based, with zero chance of its semantics being polluted by external criterion, joins, additional modifiers or aggregates etc. being tacked onto it. > I tried the new .filter() method but it seems to add only where > conditions, not order_by/offset/limit, and I got an "unexpected > keyword argument" error on 'order_by'. Maybe it didn't like the fact > that I didn't have a where condition to add? So I went back to > .select() because it just works. err well the filter model is generative and its modeled after Hibernate's criterion queries, such as: query(MyClass).filter(<crit>).order_by(x).group_by(y).list() > > But I'm impressed that the ORM is getting the answer right. That's > been my main reason for hesitating on ORMs, their code is so complex > it's difficult to audit. a very valid concern which I share, particularly on those days when someone reports a bug that Im pretty sure nobody would be able to figure out except me (which i consider to be a failure of the code to be transparent enough). --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" 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/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
