What is the role for stored procedure business logic in CSLA

bill2010 posted on Monday, October 19, 2009

We have an offshore team that is using CSLA... however, they also appear to be creating extremely complex stored procedures... sometimes several thousand lines in length. This appears to break the goal of CSLA in centralizing business logic in the app tier... and it also is controversial among DBA's who seem to feel that the bulk of business logic is most appropriate in the database.

Thoughts on thiis, anyone?

pandelaras replied on Tuesday, October 20, 2009

I always try to balance efficiency with encapsulation. Sometimes i also create such stored procedures,  because without them i would need a lot more business objects and thus seperate database queries. It all depends on what you want to achieve.

For example i would create a long stored procedure  that requires reference to many tables for a ReadonlyList object.

DancesWithBamboo replied on Tuesday, October 20, 2009

From a management side it looks like you have 2 options.  Either you (and your team onshore) do a full design document of how everything should be constructed and then give that to the offshore team to fulfill so that everyone has the same expectations.  Or, you trust your offshore team to do a professional job and you stay out of critiquing their work after it is completed.  Choosing the second option would obviously depend on how you view the offshore team's competencies and if you have worked with them before.  But don't backseat drive.

As far as long stored procedures goes; 1000s of line seems ridiculously long.  I generally have 2 criteria as to when to put something in the database; is it a set operation and is it cheaper to do there.  RDBMs are designed to do set logic extremely fast so if your problem domain is set based then do it there.  If you need a lot of if/then/else and or looping constructs then that is a sign that your problem would be better solved in the business layer.  SQL Server and Oracle are expensive solutions to use to solve problems.  The db hardware costs more than an app server, the licensing for the db is more costly, and the administration of the system costs more.  While the db is running a 1000 lines of code, it could have been serving data to someone else.  It is a shared resource and each person that puts load on it due to a perceived performance gain is potentially slowing the entire system down for all users.

Another note (only from my experience here, you may have a much different experience), dba's have terrible source control skills and their stuff is hardly ever properly integrated into the repository with the rest of the app source.  This is generally related to the fact that they exist in a different org structure and aren't part of the "team".  This has made versioning and deployment ugly over and over again for me.

tiago replied on Tuesday, October 20, 2009

bill2010:

We have an offshore team that is using CSLA... however, they also appear to be creating extremely complex stored procedures... sometimes several thousand lines in length. This appears to break the goal of CSLA in centralizing business logic in the app tier... and it also is controversial among DBA's who seem to feel that the bulk of business logic is most appropriate in the database. Thoughts on thiis, anyone?

On the last 10/12 years my coworkers build applications (asp/vbscript) using very long stored procedures. We had a in-house debate about that. In a nut shell:

SP are bad because

• SP are hard (or nearly impossible) to debug (I mean the F5 way with breakpoints, etc)
• SP are not good for unit testing
• SP are difficult to version control
• Application that are SP based don't scale well
• SP require programmers with a lot of skills
• SP are not necessarily faster (look for CURSORs)
• SP are difficult to port to other DB systems

When can we use SP?

• basic CRUD - in fact when you don't need them. One of my coworkers pointed out that CRUD SP could be written as prepared statements as well

When do we need to use SP?

• when we basic CRUD results in an enormous amount of data flowing through the network just to do a small processing and one needs to optimize the data flow and application performance

Suggestion

• CLR stored procedures can be easily written in plain VB or C# and they get executed in your SQL Server as if they were internal to SQL Server
• They are easy to debug (just F5)
• Version control is just like ordinary .NET code
• They can be faster than native T-SQL SP - depending on how you do it in VB/C# and the available choices using native T-SQL language
• They get the data off the network as well

tiago replied on Thursday, November 05, 2009

On top of all the above, let me add a recent one.

Stored Procedure abuse is very bad because...

You build a long (as in number of lines and time it takes to run) stored procedures that needs to lock a lot of tables (because you never heard about the concept of concurrency management or you think you don't need it) and on big customers with hundreds of users and big databases you get the "angry customer" incident:
- very frequent need to kill jobs
- users complaining (almost) all day long
- etc
- etc