Case against stored procedures?

views:

answers:

+1 Q:

Case against stored procedures?

Any thoughts/comments on a database with over 3,000 stored procedures querying/accessing over 1,400 tables (overly normalized). Really want to use technologies such as Entity Framework, preferrably 4.0, and get the business logic out of those sproc's?

Appreciate any real-life practical experience & feedback if you decide to share. This is a database I inherited and really feel like it's time to ditch the sproc's for a better business layer.

Thanks in advance.

Baskin

This depends on the shop you work at. I've worked at many shops that have conflicting strong opinions about whether or not to capitalize on sprocs, or code in the application layer. The question is: do you have more people that are expert at maintaining plsql/tsql or not?

Personally I used to hate sprocs, and favored orm tools, etc. Now days I prefer to keep the application code clean. This is only after getting really comfortable in the database. Let the database code exist in it's native environment, with all of the tools available to it.

jskaggz 2010-06-22 02:50:11

Good point. The database must have evolved from an early Access database, I would presume from the late 90's, I am not sure. It uses 'Y' or 'N' characters for storing booleans (ugh!) Overly normalized. I heard expert DBA's and developers talk about no more 30 joins per sproc. My jaw is dropping. I am trying to get more feelers about the team, but i want to hit with some practical experiences. Hence the question. ;)

baskint 2010-06-22 02:59:01

Sounds like a major undertaking to bring that stuff into entity framework man. :-) Which is not error prone in itself. I feel your pain though! Good luck sir!

jskaggz 2010-06-22 03:24:08

Why use Stored Procedures?

In practice, you'll end up refactoring away from EF in a few years whereas your stored procs could still be sitting there ready to use in the latest and greatest client layer. Client agnostic, encapsulating, etc.

gbn 2010-06-22 05:15:36

that's a valid point, but the amount of business logic encapsulated, untested business logic really does not jive well with "unit-testing" the business rules/logic. I rather have them written in C# and exercise their execution via some db-integration tests. but that's my opinion. agree, sproc's do stand a test of time, but with cloud computing is looming on the horizon, maybe it is time to move away.

baskint 2010-06-22 16:24:51

I didn't mention business logic should be packed into procs, but they will excel transaction handling, data integrity, avoiding multiple calls etc.

gbn 2010-06-22 16:26:47

+1 A:

Not really an answer to your question, but you can still use sprocs in Entity Framework: see here, basically sprocs map to method calls on your context object.

My personal experience is actually the opposite to yours though: I've usually tried to stay away from EF because it has this tendency to become so tightly coupled with your application. The nice thing about sprocs is that they allow a degree of de-coupling (e.g. I can update the database without modifying client code). But that's just my personal opinion of course.

Dean Harding 2010-06-22 05:24:18

Yes, they do allow de-coupling, but the amount of business logic within those sproc's are really the concern. they are not simple CRUD operations either, complex many LEFT OUTER JOIN type operations over a dozen or more tables with a few WHERE filters. Yet I also see your point, and I will start your link. Thanks.

baskint 2010-06-22 16:27:46

ansaurus

tags:

views:

answers:

Case against stored procedures?

related questions