Hi,

I have started at my company to start changing old applications to .Net (from VB6) and also program some new ones, especially in ASP.NET.

In the old application we have a lot of stored procedures, 1000+. To me this is a nightmare. They contain some business logic, are difficult to document, maintain, debug, trace, etc. Also do I think that sp's are in the datalayer and therefore shouldn't contain much business rules.

So I would use workflow (or unit of work) objects to manage get/insert/update actions on multiple tables using transactions and if necessary use 10 queries on 1 table each instead of 1 Stored Procedure. This way it's much more manageable.

The problem for me is to convince my boss that those sp's are hell and should not be used in the .Net application (or at least most of them shouldn't), but he's stubborn (probably because he doesn't like throwing away the 1000+ procedures he has written mostly himself).

Another reason I can think of is that it's not doable to change from SQL Server to Oracle very fast now.

What I am looking for is some backup for my point of view or even a better one, for when to use sp's and when using llblgen. I can certainly think of some reasons why sp's should be much better.

For example when reading writing in about 20 different tables or if you need temporary results by using a temp table.

Can anyone help me convincing my boss and tell me if I am thinking correct? Maybe finding a source on the internet which is trusted and I can use to convince him. What are your point of views on this subject?

Especially you Frans could write 1000 pages about this I think, since you created this product to avoid this mess, but how would you do this?

Gr.,

G.I.

G.I. wrote:

Hi,

I have started at my company to start changing old applications to .Net (from VB6) and also program some new ones, especially in ASP.NET.

In the old application we have a lot of stored procedures, 1000+. To me this is a nightmare. They contain some business logic, are difficult to document, maintain, debug, trace, etc. Also do I think that sp's are in the datalayer and therefore shouldn't contain much business rules.

So I would use workflow (or unit of work) objects to manage get/insert/update actions on multiple tables using transactions and if necessary use 10 queries on 1 table each instead of 1 Stored Procedure. This way it's much more manageable.

The problem for me is to convince my boss that those sp's are hell and should not be used in the .Net application (or at least most of them shouldn't), but he's stubborn (probably because he doesn't like throwing away the 1000+ procedures he has written mostly himself).

Bosses like math, especially when it comes to costs. Do a simple calculation: with procs you spend X hours on maintenance, without procs you don't. X hours * hour rate + the cost of not being able to spend time on the real project = lost money due to procs.

Another reason I can think of is that it's not doable to change from SQL Server to Oracle very fast now.

Switching procs from sqlserver to oracle is a pain.

What I am looking for is some backup for my point of view or even a better one, for when to use sp's and when using llblgen. I can certainly think of some reasons why sp's should be much better.

For example when reading writing in about 20 different tables or if you need temporary results by using a temp table.

Can anyone help me convincing my boss and tell me if I am thinking correct? Maybe finding a source on the internet which is trusted and I can use to convince him. What are your point of views on this subject?

Especially you Frans could write 1000 pages about this I think, since you created this product to avoid this mess, but how would you do this?

Approaching the matter with "All procs have to go" is not the way to go because it's not true. As you said, sometimes procs are better, which is also the reason why you can call procs from llblgen pro.

The main issue is CRUD. CRUD operations in procs are a pain, and these are forming the majority of the procs in each app. You should keep processing procs, which perform large operations. For the crud, use llblgen pro, it will save you money: both in maintenance (no maintenance, all logic is there in your code) and in the fact that you can spend all your time on writing code which matters, not plumbing. Procs for CRUD are plumbing code, and they're just half the picture: you have to call them with code you have to write (or generate, which is never optimal when it comes to calling procs), you have to pre-process the data before you can use it and pre-process the data before you call the proc. That's plumbing code you can do without and your boss will understand, simply by doing some calculations

Money is always the answer it seems Anyway, talking about CRUD and other sp's. Do I see it correct to think:

All query's to get data from the db (unless calculations needed) is best to use LLBLGen

All CRUD sp's is best to change to LLBLGen

Use sp's, which means sp's using a few parameters only, to move/adjust data in several tables.

G.I. wrote:

Money is always the answer it seems Anyway, talking about CRUD and other sp's. Do I see it correct to think:

All query's to get data from the db (unless calculations needed) is best to use LLBLGen

All CRUD sp's is best to change to LLBLGen

Use sp's, which means sp's using a few parameters only, to move/adjust data in several tables.

Yes.

Procs which process a lot of data can be more efficient because they don'thave to transport the data outside the db, where it's processed.

this can be slower though, for example if hte processing logic is complex, which is then written in T-SQL which is interpreted and therefore not the fastest.