An example would be a subquery that yields a large temp table, only to be pruned down to a single row.
Your choice is to either have understanding of the query optimizer and formulate a query that forces the subquery with a smaller temp table first, or you can bring it back to the application. Either approach works, but in once case you need to have deep-ish understanding of the optimizer and SQL, and in the other not so much (at the cost of an RTT.)