I disagree about SQL being like assembly. It's a fairly high level language, because underneath the engine is figuring out how to use indexes and actually run your query. If you want to draw parallels, ORMs are more like writing code in a template language that's written in the language you're actually working in.
I've spent far too much time fighting with ORMs trying to get the SQL I want generated. Additionally for complex queries, I often develop in a SQL manager interface, because it's so much more direct. When I'm done, I have a working SQL query - I can paste it in to my code and parameterize it, why should I spend even more time fiddling with an orm?
I like "micro ORMs" that just map rows to objects. Generating INSERT and UPDATE statements is ok, and single table selects with a single where clause are sometimes ok. Beyond that, I'd rather just skip the middleman.
That said, for small databases (a GB or two) that are reasonably well designed (e.g. 3NF), the speed of current I/O and optimization engines in databases is such that even the worst SQL will generally run fine - so if you find it faster to use ORM and understand the future pitfalls, go for it.
Actually more often than not ORMs are going to provide better performance than having people inexperienced with SQL writing it themselves. Especially when it comes to batching, pagination and joins and the intelligent use of in memory caching rather than doing everything in database.
And the biggest advantage of an ORM is that you can trivially switch between databases which is often required for running automated tests. SQLite or H2 for development and then Oracle, SQL Server or Teradata for production is a very common pattern I've seen at many companies.
Running your tests against a different DB than what you're using is an anti-pattern IMO. You'll not be able to trust the results, and you'll be limited to the lowest common denominator of the databases your using.
I've found views, parameterized-views/table-valued-functions can be a nice compromise to keeping the SQL relatively simple and thus ORM friendly without sacrificing performance.
This can be dangerous if over used. It can be very tempting to quickly pull in a 'left join blah' to grab some fields you may need. But that blah view could be hiding some massive performance sapping beast.
When it comes to optimizing a slow running query, having to step through layer after layer of nested view can be incredibly challenging.
I can't speak for other engines but at least SQL Server and Oracle will eliminate unnecessary joins for inlined table-valued functions, e.g.:
create function thing (a, b) returns table as
select * from table_1 left join table_2 on ...
select (only cols from table 1) from thing(a, b)
..and table_2 is not accessed
predicates from the topmost query will be pushed down to the function's query as well so the functions performance is generally equivalent to the adhoc version.
on the whole I find this strategy to be very effective at reducing the complexity of adhoc queries without performance penalties. in the case of indexed views it can greatly improve performance.
Worse, someone is using the view (that joins several tables) in a join to get one extra field, which would be a simple single join -- they just didn't bother to check where it came from because 95% of the time that view gets used, and no one questions it. I've seen it happen more than once.
Unless you're fetching all the unneeded fields as well, in any decent DBMS both approaches would result in the exact same execution plan with the exact same performance, the unneeded parts/joins of the view wouldn't be executed.
I've spent far too much time fighting with ORMs trying to get the SQL I want generated. Additionally for complex queries, I often develop in a SQL manager interface, because it's so much more direct. When I'm done, I have a working SQL query - I can paste it in to my code and parameterize it, why should I spend even more time fiddling with an orm?
I like "micro ORMs" that just map rows to objects. Generating INSERT and UPDATE statements is ok, and single table selects with a single where clause are sometimes ok. Beyond that, I'd rather just skip the middleman.
That said, for small databases (a GB or two) that are reasonably well designed (e.g. 3NF), the speed of current I/O and optimization engines in databases is such that even the worst SQL will generally run fine - so if you find it faster to use ORM and understand the future pitfalls, go for it.