ActiveRecord (Rails) provides counter caches for this instance. There are also gems that will analyze the queries your ORM performs to tell you which ones you should optimize.<p>I tend to stick with "premature optimization is the root of all evil".
Knowledge of SQL is definitely needed even when using ORMs, but I agree with both of the other commenters. Instead of starting out with bending the ORM to your will it is better to use the API of the ORM in the way it really is intended.<p>In my experience with Rails ActiveRecord the usual suspects are N+1 queries, repeated queries that hit the query cache (they are really quite slow in Rails 3), and missing indices.<p>But intricate knowledge of SQL is most certainly necessary when using ORMs in conjunction with anything else than a trivial data model.
My typical approach is to just go with the ORM approach while keeping an eye on various query metrics. If I begin to see signs of a slowdown related to a specific query being generated by the ORM, then I will replace it with hand tailored SQL.<p>I'm not sure if this is the best way to go about it, but it works well for me.
To use an ORM well you should know how it works internally anyway. Monitor what SQL it spits out, think how that should be done optimally and see how the ORM would generate that; a lot of ORMs you can nudge in the right direction if you know how it generates the actual SQL it executes from your (limited) input.