I think SQL is slowly going the way of assembly language over time - still useful to write by hand in special cases but most of the time you want to write in something higher level and have it generated automatically.
Assembly has been largely replaced by higher level programming languages.
And since SQL is a fourth generation declarative language, I really don't see how it could go the same way as assembly by being replaced with imperative programming. It's already more expressive and succint, and have stronger abstractions from implementation details, than imperative programming languages.
Hibernate for Java, SQLAlchemy for Python or Class::DBI for Perl (never used that last one) would be good examples for ORMs that blend well with the language they're written for.
Performance may vary across different ORM modules; the best-of-breed ones are certainly as fast as if you'd create the SQL yourself.
Please don't use Class::DBI for Perl. The modern replacement, DBIx::Class is better-designed, faster, and more flexible. And it comes with a full Class::DBI compat layer, so you can incrementally migrate your CDBI application!
In my experience, most layers that make things easier generally suck under load. I would think it's especially true with something that adds a layer between you and the database.
How is Active Record with regards to performance under load?
clients = Client.all(:limit => 10)
clients.each do |client|
puts client.address.postcode
end
looks harmless but requires eleven sequential round-trips to the database. This version
clients = Client.includes(:address).limit(10)
clients.each do |client|
puts client.address.postcode
end
still requires two, and one of the queries gets bigger as the number of ids increases. You can get into find_by_sql, but at that point ActiveRecord isn't adding any value.
Sure, but how many times have you seen someone create something equal to that with SQL queries? Instead of using a join, they execute a SELECT against the database for every row they fetch. This is shockingly common in horribly written PHP code.
In addition, ActiveRecord also supports joins instead of using includes. Includes is almost always fine, unless the initial result set is large (100+ rows).
ActiveRecord has great performance. Mostly because it's a really thin layer over database tables. The only additional overhead is instantiating one object per row returned.
Just in case the OP is using PHP, there are libraries that support Active Record without necessarily resorting to a PHP framework. (Same as you can AR outside of Rails [caveat emptor, might have changed in Rails3].) Adodb has one. Not a personal recommendation, just saying it's there if you need it.
With Active Record you rarely if ever need to write SQL any more.
http://guides.rubyonrails.org/association_basics.html
http://guides.rubyonrails.org/active_record_querying.html
I think SQL is slowly going the way of assembly language over time - still useful to write by hand in special cases but most of the time you want to write in something higher level and have it generated automatically.