Comment by brigandish
4 years ago
My experience is that the ORMs I've used most (LINQ and Ruby's Sequel) can produce far more efficient SQL than a human can, and if not, you change the code, just as you would have to if you wrote a slow SQL query.
Of course, I've not seen every query in existence so it's more than possible you've seen bad SQL from an ORM, but the untangling part would again fall to those skilled in the language of the ORM - unless the ORM can't produce efficient SQL in a particular case. And just as it would if the query was originally written in SQL, you'd need someone skilled in SQL to untangle that.
What would that case (where an ORM cannot produce efficient SQL) look like?
I have some serious doubts that an ORM can tune queries as well as a human due to the fact that the ORM lacks one key piece of information that both me (and the database planning a query) can leverage - table statistics. An ORM can produce a query that will behave well in the best general circumstances, but as soon as you get into topics like subquery performance fencing ORMs simply have no ability to compute optimality on the fly. One specific example I've seen is where multiple paths exist through the database to transit from one fact to another with one path being more strictly optimal and the other path having more associations that may be needed - this can effect the join strategies you want to use so if your ORM is anything more than "I'll essentially tell you the SQL but in a weird syntax" then there's a good chance it'll chose the wrong path.
But you write the code via the ORM, so I don't see the difference between you writing the SQL with knowledge of the table statistics and you writing the code via the ORM with knowledge of the table statistics.
I haven't got the chance yo try Sequel, on the ruby side I played more with plain ActiveRecord and querying layers like ransack (my predecessor on the job loved abstraction layers)
In general ORM queries become ugly at three to four levels of joins and/or excluding under non trivial conditions (e.g. finding users that have not participated to a specific set of events). They will spit out something that works, but will take a few orders of magnitude more than an optimized query.
As you say, there is the option to play jenga with the ORM code to hit the right combination that produces a better output. But that feels like teaching a toddler to solve a puzzle that you already solved and are keeping the cheat sheet in your pocket. I personally don't see the beauty of it and would prefer to directly use the right SQL and call it a day.
On people skilled in SQL, you should have a few onboard anyway if you're doing more than basic CRUD on the DB, and it's easier to find than ORM gurus IMHO.
> I personally don't see the beauty of it and would prefer to directly use the right SQL and call it a day.
I think that's fair enough, there are enough ways to do things now that it should be possible to accommodate both.
> On people skilled in SQL, you should have a few onboard anyway if you're doing more than basic CRUD on the DB, and it's easier to find than ORM gurus IMHO.
I agree but I'm not sure there are more SQL gurus than those used to ORMs nowadays. Lately I've favoured using SQL but even 15 years ago most devs I knew couldn't use it well, I can't see devs used to Rails et al having the chops for it, sadly. What was once convenient easily becomes one's master.
> I haven't got the chance yo try Sequel
If you get the chance, I think it's worth it. It's easy to drop into plain SQL without dumping the ORM, and I've never had a problem with the stuff it generates. It's a pity ActiveRecord gets all the love instead.