Derik Whittaker

Syndication

News


Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)

**** Warning, a rant is about to take place ****

Can I ask a stupid, rhetorical question?  Why do we continue to put business logic inside of SQL Procs?  Can anyone answer me this?  About the only half way decent answer I have ever heard is:

Because we have some of this type of logic (Business Logic/Rules) inside of a sql proc we are able to update/change/modify this rule on the fly without the need to redeploy assemblies.

I am sorry, but this is about as lame of a reason to do something as I have ever read/heard.  Yes, I will admit that when you do put logic into your procs you do not need to redeploy.  However, you are making this decision at the expense of other, better decisions.

When you put your logic inside your procs you are doing many, many things which I believe are bad.

  1. Hiding business logic
    When you put your business logic inside of a proc you are now making future developers ‘hunt’ for this information.  As developers the first (and should be only) place we look for business rules/logic is in the code.  When we do not see logic in the code we are stumped.
  2. Making it really, really hard to reuse any logic which is inside of the procedure
    Because this logic is put into a ‘single use’ object like a stored proc there is no way you can reuse this logic.  This leads to code duplication, which leads to bugs, which leads to shit going bad.  If the logic inside the proc was to be placed in some reusable business class then you can reduce possible bugs when the need arises to implement this logic in multiple places (and we all know this happens)
  3. Throwing testing to the wind
    We all know (or I hope we do) that we need to test our code (yes I am talking via a testing framework, but this cause also mean manually).  When we put logic inside of a proc testing gets very, very difficult.  About the only way you can test this is by running the application and executing the proc.  Although this approach could work in a limited capacity, it is very error prone and very hard to repeat with any success.

If I had my way SQL as a language would no longer support items like ‘If/Then/Else’, While loops, case statements, or pretty much any other logic items which ‘make decisions’.  Sql syntax and usage should be used for one purpose…… Manipulate data in the database.  And i define this as, Read (straight up selects) or Add/Update/Delete.

Till next time,


Posted 02-28-2009 3:02 PM by Derik Whittaker

[Advertisement]

Comments

Ryan Svihla wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 02-28-2009 5:24 PM

Even the "don't have to redeploy" argument falls down with clustering or HA scenarios.

One could just as easily argue that means "ok then we use only dynamic  languages from now on and just edit directly on the production server".  That's basically the same argument (though I know people who misuse dynamic languages in the same way).

It's the ultimate red herring argument in IT right now that sprocs are even relevant to whether an application is successful.

Michal Chaniewski wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 02-28-2009 6:49 PM

I kind of agree with this point (to the point that I find CRUD stored procedures unnecessary and tend to use strong ORM instead), but there are special cases, where you want to use SPs that do something more.

This is important especially when you do some kind of processing that would not be performant enough when done in a different way. In some situations you need to bring your logic close to data, because, well, it operates on large portions of that data.

NaiveDeveloper wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 02-28-2009 6:52 PM

spoken like a true application developer....lol

VR wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 02-28-2009 7:34 PM

Agree 100%.

I just finished working on an application where a previous "architect" added business logic into the views, on all views added triggers that executed stored procedures with other business logic. Complete nightmare with horrible performance.

The argument for that design was the "reusability in case some other application needs that". obviously it was so bad that the main application could hardly use it.

WR wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-01-2009 9:11 AM

One reason to put biz logic in the db is that you have everything in one place, instead of having pieces of code all over, and if you practice some type of discipline it could work better than having pieces of your biz logic separated and floating all over the place.

Derik Whittaker wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-01-2009 10:29 AM

@WR,

Sorry, but that argument holds no water with me.  Biz logic goes inside your business domain of your application, not the data later.

Your data access needs to be simple and fast.  Your business logic needs to be testable and maintainable.  Having the the logic outside your db allows for this.

Anne Epstein wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-01-2009 4:18 PM

The quick deployment thing seems particularly noteworthy.  Wherever business logic lives, whatever language it's written in, it's still business logic, and still important.  If deploying patches to the code is slow because there is a testing/approval process for the code, why is it okay to ignore that process for some arbitrary part of the application?  And make no mistake, if there is business logic in the database, the database is no longer just a "dumb" persistence layer, but an integral part of the application that by all rights should go through the same process.  If this process is onerous and developers feel a need to exploit holes in it to get their work done, the procedure itself needs to be evaluated.

DotNetShoutout wrote It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language) - Derik Whittaker
on 03-01-2009 4:49 PM

Thank you for submitting this cool story - Trackback from DotNetShoutout

jdn wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-01-2009 6:03 PM

You've clearly never been a DBA or been on an ETL-type project.  I think this rant is pretty ignorant.

T-SQL does many things better than managed code.  Have you ever needed to move GBs worth of data from one place to another in a solution that involved both T-SQL and managed code?  Trying to do it solely through managed code would be negligant, IMO.

Hiding business logic: unless your developers are incompetant, they can be taught very easily to find business logic in T-SQL.  Ever heard of 'sp-help'?

Making it really, really hard to reuse any logic which is inside of the procedure: sprocs can reuse logic.  This is, in fact, pretty standard, something I've dealt with at almost every client recently.

Throwing testing to the wind:  Only people like you who are ignorant of testing outside of your conceptions of what testing about would say something like this.

Seriously, Derik, this is an ignorant rant.  If I didn't know you personally, I would say that you were incompetant to work on most of the clients I've worked with the last 5 years.  This is just stupid stuff.

Chris wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-01-2009 11:39 PM

I work at a company that has extensive Business logic in our sql and I hate it. However, the one point that someone pointed out to me is it can be great for reports. Granted this has nothing to do with business logic in CUD just the R but if you don't put it in your SQL you must either - report off your objects - great for small datasets, awful for huge amounts of data (Rocky of CSLA.NET even recommends reporting straight from the db) or repeating your logic inside the reports.

But you are right we do put way too much logic in SQL. A further disadvantage I find is that stored procedures have no strong relationship with each other. The only way to say that 2 procedures are related is to put a prefix on them. while in the business layer we can put them in the same class.

just3ws wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-01-2009 11:45 PM

There's a horrible, horrible middle-ground between putting everything in stored procedures and processing the data in a more OO manner. There's the dynamic-business-data layer. Basically, dozens of classes wrapped around a temp-table, which is defined and managed all in the application using dynamic sql. !@#$% So, there are worse things than putting it all in stored procedures. :-) I don't care if it's all in procs or all in a domain model, make it consistent and code it using the best practices of that style. Procedural, Functional, OO, whatever.

anon wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-02-2009 7:53 AM

I can't wait for the day that object databases become mainstream.

Patrick Dewane wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-03-2009 2:50 PM

While I'm one for layered design, performant related reasons do sometimes outweigh the need to be so strictly layered. Often placing business rules inside of stored procedures allows a more performant set based approach to filtering that otherwise would be done in managed code. For instance, if I have a business rule that dictates I show a months worth of data in some UI, I would want to apply the date logic within the stored procedure instead of having some layer filter out irrelevant data. The procedure can accept parameters which dictate the rule, and hence, the interface to the stored procedure can still test the business rule. It's difficult to argue the side of "never do this", especially when there are valid reasons to do it.

Brandon wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-03-2009 6:58 PM

I can see the point for putting some business logic in the database especially when it deals with a larger dataset, but a 2500 line insert / update trigger on one of the main tables in my database?  Thank you, but no.

Jason Short wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-03-2009 11:59 PM

I agree with you entirely.  I see WAY WAY too many developers (NOT DBAs) putting everything and the kitchen sink into the database layer.  Usually they do it in the worst possible way and the entire database suffers as a result.

There are valid reasons when and where to do this.  But having a developer stick everything in the database is just dumb.  

The user above who said this rant was ignorant  - meh.  

/clap  - I agree with you.  Keep up the good rants.

BJ wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-05-2009 1:13 PM

I guess thats why they call them rants, becuase its a preson just blowing off steam...

Business logic in stored procs has its place.  For example large batch applications that will not meet performace requirements through managed code.

Czy ty te?? nadu??ywasz procedur sk??adowanych? « !FrAgile Thinking wrote Czy ty te?? nadu??ywasz procedur sk??adowanych? « !FrAgile Thinking
on 03-05-2009 5:09 PM

Pingback from  Czy ty te?? nadu??ywasz procedur sk??adowanych? « !FrAgile Thinking

Benjamin Geiger wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-06-2009 10:20 AM

I concur with Chris: a major exception to the "no logic in SPs" rule is reporting. I work for a government agency in Florida, and we have to give large reports to the state. Doing these reports in code would take eternities.  Bulk data manipulation isn't really feasible with most ORMs, and even with standard DataSets, it's ugly.

Dave Schinkel wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-07-2009 3:28 AM

Not just that, but why does Microsoft continue to push code into Stored Procs via all the new capabilities in SQL 2008 and on forward into the future.

Keep your logic separate from CRUD.  Stored procs are only good for CRUD.  Maybe one-offs for extreme cases for calculations but one can make an argument that it is no longer inefficient to do your logic in OOP as opposed to hard core calculations for reports in SQL.

Coding logic in SQL is horrible.  Do that in your C#.  Who wants to debug a pile of SQL Shit.  If you like headaches, go ahead...it's just plain ignorant and stupid to create spaghetti code in SQL and endless loops and nested piles of statements that can't be managed.

Dave Schinkel wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-07-2009 3:29 AM

>>>I concur with Chris: a major exception to the "no logic in SPs" rule is reporting. I work for a government agency in Florida, and we have to give large reports to the state. Doing these reports in code would take eternities.  Bulk data manipulation isn't really feasible with most ORMs, and even with standard DataSets

I hope you are not talking MS Datasets.  That's  your first  problem, don't use them.  Code custom classes and keep your BL in your BL

Dave Schinkel wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-07-2009 3:32 AM

And by now, if you do not know better than to keep logic out of procs, you shouldn't be a developer.

tamir wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-10-2009 3:44 AM

in most cases i agree with this opinion.

but when you need optimization for large amount of data or you have many application(servers) going to the same database i guess that you can do the database manipulation in SP.

but as i said in most cases you can do it by "normal" reusable coding.

It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language) - Derik Whittaker « the antfactory wrote It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language) - Derik Whittaker « the antfactory
on 03-10-2009 8:51 PM

Pingback from  It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language) - Derik Whittaker « the antfactory

DotNetKicks.com wrote It is SQL NOT SBQL (Structured Business Query Language)
on 03-17-2009 2:29 PM

You've been kicked (a good thing) - Trackback from DotNetKicks.com

Bill Bryser wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-18-2009 4:23 PM

Yet another uninformed rant, congratulations. I here this over and over again from developers. It's a little like a cyclist complaining about car drivers and vice versa.

You ain't a DBA or a SQL guru are you? This is why you don't get it.

Derik Whittaker wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-19-2009 7:13 AM

@Bill,

I am not a DBA or a SQL guru.  But not being neither of these does not change the fact that putting business logic in the database is a BAD idea.  

You can try to convince me, yourself or yoda it is, but it is NOT a good idea to do so for a business application.

Hell, DBA friends of mine i know agree with me on this

Miriam wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-25-2009 12:25 AM

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.

Miriam

www.craigslistposter.info

Pankaj wrote re: It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language)
on 03-30-2009 8:13 AM

Do u ppl know about server n client side?

Storing logic in SQL helps a lot. Faster n quick data management as it is runned on server. SP is compiled object which also make process faster.  There are many other points...

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)