[dasil003]

> any time you write "sql" in a non-sql language like python or ruby you are letting go of any ability to optimize your queries.

No you're not. Look at ActiveRecord, it lets you drop to any level of SQL optimization you need. In ActiveRecord 3 with ARel queries are composable, allowing lazy loading and the breaking of queries into appropriate locations according to your code architecture.

I can't speak to other ORMs, maybe they really are as bad as your opinion would indicate, but I suspect what you're really complaining about people who don't know how SQL works being enabled to write horrible data persistence code by ORMs with a pretty facade. That's a legitimate problem, but the fact that a tool can be abused is not an argument against the tool itself. We'll never build anything great if we are driven primarily by what the ignorant will do with it, after all, every single person on the planet is ignorant of most things, our tool development should be driven by what they enable experts to do.

[n_are_q]

Things like lazy loading is a red flag to me that you are doing something wrong, so if your framework allows you to do that that's not necessarily something to brag about :). Random IO that is triggered by merely accessing a property without knowledge of the programmer is not the best approach if you want to scale, you are better off doing deliberate fetches as a result of previously fetched data. If you are breaking and composing queries, how are they broken and compose by the orm, as joins or as sub queries? If as joins does your orm know the best columns to join on? You could replace everything with named sql functions (dropping to the lowest level of optimization as you mention above), but at that point what is your orm really doing for you. Anyway, sorry, I'm not sold :). Maybe if you effectively replicated the database engine in your front end framework I would come closer to being sold, but even then you don't have the same rapid in memory access to statistics about tables to make the right optimization decisions, etc..

[dasil003]

You are thinking at too abstract a level. First of all, IO is not "randomly" triggered, it's entirely predictable how it works. Composability allows you to define a scope that globally applicable like (eg. "published articles") and then add additional constraints in a controller (eg. "tagged X") using the logic of relational algebra to construct a sane query. Could this query be slow? Sure, it's still your responsibility to make sure the schema supports that query. Writing SQL manually does not absolve you of that responsibility, it just means organizing a lot of SQL strings somehow.

Here's what you're missing, and why ActiveRecord works: if you halfway know what you're doing with a RDBMS, 95-99% of the time the query generation just saves you writing a lot of boilerplate SQL. It's true that sometimes you have to drop to a lower level to hand-craft a query, but ActiveRecord in no way prevents that. Again, I don't know what kind of ORM hell you have been put through, but I assure you that an ORM does not need to be this horrendous performance killing black box that you think it is.

[n_are_q]

There is one thing that I think can help with the sql overhead you mention - if you have a rock star dedicated sql person that can take all this work off your hands (that's not me btw, i've just worked/am working with such people). I think it affords you easier long term growth if you have expectations of making it to the medium to large company world, while not slowing you down when you are small, so I think it's a better strategy for both small and large companies. Are you signing on for a potential bottle neck? Yea it is a trade off and it is paramount you hire well in that area, but that's the sort of problems and decisions you have make all the time at a company.

I understand where you're coming from and what you describe may be workable in a smaller company with 7-14 devs where everyone knows what they are doing and understands well what happens under the hood. I think it's less likely to work at a company with 50+ devs though where you inevitably start trusting people less, or just at a company where you don't trust everyone. I've worked at both types. There is also the question of the complexity of your data and the way you need to query it. Right now we do essentially a ton of graph queries that we optimize highly in sql (ends up working much faster than any graph database since the schema and the queries are optimized for the exact data we are working on). Some of the functions that I write for this would not be implementable in an orm. I suppose that could be the case where you drop down into raw sql, but that happens to be a fair chunk of our code.

Maybe you can make it work better than I'm expecting, but if you were starting from scratch would you really want to go down that path anyway, all things considered? My original argument was that you are better off choosing a different way. I suppose that point of view will be difficult to change for me :).

[nettdata]

Have you actually ever used an ORM? All of your posts strike me as being of an "I imagine it would be bad" nature, without actually speaking from experience.

I get the impression that you don't really understand the complexity and capabilities of a modern, robust ORM, or how it can be used.

And you're working on a data warehouse, which isn't usually a viable candidate for ORM in the first place.

[n_are_q]

I've used ORMs before i worked at myspace. NHibernate specifically. I've also used sql alchemy on the python side. NHibernate was in a professional environment, sql alchemy was a bunch of stuff I did for evaluation purposes, so you can discount that if you like.

And i'm not working at a data warehouse.. why do you think that?

[joevandyk]

Lazy loading in ActiveRecord works like:

users = User.where(:age => 10) # no rows fetched

# Once you access something on users, then the query happens users.each do |user| # something end

It's not really that "magic", it's useful.

ActiveRecord uses joins (mostly). If you are using a "non-standard" table schema, you can tell ActiveRecord what column to use on joins.