You're correct: complicated queries on large databases are no place for an ORM. Rose is pretty fast for simple-to-medium queries, but complex ones always require hand-tweaked SQL. You can run hand-written SQL through Rose though (or others), and it can still provide a convenient place to organize your custom queries and deal with your database handle/statement caching.

I would also add that ORMs are never good for bulk updates. You can totally crush the speed of an ORM for a bulk insert/update by writing some simple DBI code or by using your database's bulk loading features, e.g. multi-row insert in MySQL.