Response to: Stored Procedures Vs Dynamic Queries

I read this article: http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx 

 

I have a difference of opinion here, because I have also built an ORM mapper/ code generator before. My opinion is that, the whole area of dynamic queries being faster and better than stored procedures only makes sense in the context of having stored procedures for specific tables. Which means, when we have a tool which directly operates against tables then it makes sense to have stored procedures vs dynamic queries, and there dynamic queries can be recommended because we are only talking in the context of a single table.

 

So a C# developer can write the query in C# and have a better dynamic query than the code calling a generic stored procedure because it is generic.

 

However, in big, real world applications, nobody works in the context of a single table all the time. Mostly, what we retrieve spans multiple tables and complex queries. What we insert may also span multiple tables. So, it becomes more difficult for a C# developer to write a complex query like that in C# and still have it as optimized.

 

Because of this, in such situations, stored procedures will actually work better, faster, and have lesser chances of error because it is written by a database guy, and it is easier to write complex queries in SQL (notwithstanding the newer advancements in LINQ).

 

So, I think every ORM tool must fully support stored procedure code generation, and stored procedures are fully valid in these cases.