Generic suggestions for SQL 2005 Framework\Design and Implementation

Question

I'm renovating an existing ASP.Net web-app which has a full-fledge functional SQL 2005 DB as its backend. By full--fledge functional I mean that there're many things (infact almost ALL the CRUD operations) that are being handled from within the DB using SP.

So, my first question is that is an extensive usage of SP good based on parameters like performance, scalability and security? I'm also asking this in comparison to the latest things like LINQ to SQL, Dynamic query generator frameworks like nHibernate, etc..

The DB is going to handle few million records in each table, we've an extensive lookup/linking process (I'm planning to use VIEWs for it) So, the concern is that is it worth to keep the 'CRUD' implementation in SP - obviously this also drags some validation and business-constraints into the SP-layer (I'm trying to minimize it) but still if its worth those three primary parameters - performance, scalability and security the tradeoff is affordable.

---

And a few one-liners for those expert DBAs and programming GURUs:

1. Are VIEWs cached physically @ any place on the server? Or they're the same as a typical JOIN operation (In nutshell shud I continue using JOINs among tables or create VIEWs of such JOINs)

2. Are T-SQLs via EXEC worth compared to compiled SQL in SP? I mean obviously they provide a great flexibility but then the whole benefit of 'compiled SQL' is ruined? It does reduce the complexity greatly but does it effect the performance\security (i.e. SQL injection, etc..) in reverse?

3. Framework-level: Shud the CRUD operation stay in SP or do some frameworks promise better tradeoff by dragging them into app (i.e. LINQ-to-SQL, nHibernate, etc..) .. again I believe this point is also linked with #2.

4. Considering all the explanations I've provided which framework is preferable for my web-apps? Backend is SQL Server 2005+.

Any additional creative thots are welcome. Thank you for sharing your knowledge.

Answer 1

Hello,

Regarding "benefits" of Stored Procedures over Ad-hoc queries - there are a lot of articles over the internet. Good examples are here and here.

Performance of query/SP solely depends on the execution plan in SQL server. Execution plan is generated during first call based on SQL Server statistics and is cached for boths SPs and ad hoc queries. So, to sum up, I'd say that there are no reasonable benefits of using SPs.

The one of the biggest disadvantages of SPs is maintainability. With SPs you are forced to move at least part of your business logic to the database layer. SQL is not structured language and it is awful to maintain complex business logic there.

Therefore I am a "follower" of "no SPs" approach. Software development should not be database-centric, but code-centric. With good ORM framework you can make really maintainable and orthogonal business layer component.