Kudos to dcaillouet for a nice clear explanation.
I will only add that there can be performance benefits to using stored procedures. There's not always a performance downside:
Stored procedures are usually compiled, cached functions, so one SP can often perform a group of related queries faster than if you 1. begin a transaction, 2. execute your queries, and 3. commit the transaction. And, as dcaillouet mentioned, those queries are all happening in one environment, atomically, rather than travelling to the SQL system from the app environment. So, when performing a complex operation, you make one DB call from your application environment rather than 4 or five, for example.
In fact, even if you just use a stored procedure to handle one query, that might be faster than a standard application SQL query, because the SQL query plan itself can be cached.
Also, as to the question of database features, it's funny to me how often programmers immediately raise the red flag about performance. The amount of application code you save by having advanced logical features is amazing, once you get the hang of it. There are just some things that make so much more sense in a relational query mode. (not everything, I understand). For example views, procedures, CHECK constraints, and domains-- when you get the feel for these, you will be amazed at the coolness factor. These go a long, looooong way toward making a database management system feel more "object-oriented". Each of these allows for a powerful type of encapsulation, and even a sort of inheritance, if you learn to play with them. (And the future looks even better. With the more advanced RDBMS systems that www.thethirdmanifesto.com talks about, inheritance and abstract datatypes will be built into the system from the ground up).
So don't view these features as "trade-offs to performance". View them as ways to accomplish more with less work. Programmer performance is worth far more than system performance ;-).