You can find my opinions on this issue on other threads in this forum, but here are my latest thoughts...
1) Before considering performance, you must first determine that you in fact have a performance issue. From my experiences, performance ranks below quality of implementation and readability/maintainability of code. If you have already determined that your middle-tier data access code is well written, can easily be maintained and enchanced in future development efforts, and is not experiencing performance problems in its current state, then forget about performance and move on to the next task. Spending hours trying to squeak out that extra millisecond just isn't worth the effort if it means sacrificing the good code already written.
2) Based on my experiences developing under ADO and ADO.NET, there is little difference in executing a stored procedure vs. dynamic SQL if you are performing basic CRUD operations (single record SELECT, INSERT, UPDATE, or DELETE). Both tend to be compiled and cached after their first execution since dynamic SQL through ADO and ADO.NET use sp_executesql to parameterize the query and those query plans tends to be cached. So from a performance standpoint for simple CRUD operations, dynamic SQL and stored procs are essentially equal. However, from a readability standpoint, having a bunch of raw SQL stuffed in the middle of your data access code is UGLY! So stored procedures tend to make code more readable since they encapsulate the raw SQL. They also make writing and testing just the SQL portion easier. Another option is to have your raw SQL inside of a resource file and load it into a caching object at startup or on first access.
3) If you have more going on in your data access code than simple CRUD and you will always be using MS SQL Server, stored procedures are probably the way to go since the whole plan is compiled together and definitely more readable.
4) Consider what type of application you are developing. If you are developing a true software product that is designed to run against various RDBMS vendors (i.e. SQL Server, Oracle, MySQL, etc.), then I would strongly advise against stored procedures because they are proprietary from vendor to vendor. You are proabaly better off going with all logic encapsulated in your middle-tier data access layer and executing simple CRUD operations with the raw SQL read from a resource file. You may also need to have vendor specific sections in the resource file if ANSI compliant SQL can't work with the vendor and you need to use some vendor specific syntax. A good practice in your data access layer is to use a factory pattern to swap out data access objects for the appropriate vendor. Here's an example in J2EE J2EE DAO Factory
. ADO.NET 2.0 (avail. in beta currently) implements this pattern.
Always a good question.