The performance problem with using multiple queries comes mostly from the network latency and query overhead that happens every time your application needs to make a call to the database. Prepared statements minimize the later and connection pools help with the former. The primary thing to worry about when writing queries is whether the logic behind them is efficient and doesn't for the database to do stupid things or fetch the wrong information. (Like using count( * ) where exists is what you mean.) Paying attention to what your queries will make the database actually do is more important than following any guidelines.
I'd agree. I'd also add that well written stored procs are often more readable than well written ad hoc queries.
The longest query I have ever written I thought was approaching unmaintainability at about 100 lines, but I can sit down and digest it without too much effort. I have however spent a week debugging a query that was three times that long. I think the time to understand starts increasing with the square of query length...... 100 lines may not take too long to understand, but 200 lines takes 4 times as long, 1000 lines takes 100 times as long, etc.....
At least my experience is that even well written SQL starts becoming a bear really fast when over 100 lines ling.
At least that's my experience.