October 16, 2015 at 7:39 am
What are the advantages of using CLR instead of a stored procedure? For performance reasons, programming that processes data should be as close to the data as possible, preferably all done in-process. Generally speaking, unless data is for export or presentation, you don't want to cross the boundary of SQL Server's process, whether it be exchanging data with .NET CLR or (even worse) pulling it back and forth across the network to be processed on another physical machine.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 16, 2015 at 7:45 am
In our case we have some complex scheduling that is going on. We are trying to line up inventory, supply, demand and make sure everything gets scheduled properly. We had stored procedures that were trying to do this logic, but it was never quite right and it was slow. Moving the scheduling to use a .net dll via the clr greatly improved the performance.
Ben
October 16, 2015 at 7:47 am
This has always bothered me: What really is meant by "business logic"?
Wikipedia defines it as "In computer software, business logic or domain logic is the part of the program that encodes the real-world business rules that determine how data can be created, displayed, stored, and changed."
Seems to me that 3/4 of that is the definition of the relational model.
Created: Defining the reality that is being modeled by the systems, using relational tables and attributes, defining the property rules and class rules (column constraints and table constraints like FKs).
Stored: Physical layer of the database defines the process of implementing the logical model, adding indexes, computed columns, partitions, filegroups, etc etc.
Changed: Much of what defines how data can change is defined in the class rules of the logical model. Certainly complex calculation and input is handled by an application interfacing with the database, but these changes must be approved by the database, which is *model* of reality.
Displayed: The one place where the database doesn't have much say. It's the job to the person or tool querying the database to decide how best to display the resulting set that the database provides.
The answer has always been in understanding that a computer database system provides a *model* of a reality outside of the computer. A person buys a product and interacts with a sales representative. A couple gets married on a date in a county. A person writes a message to be shared with their friends. We live in a real world that is *modeled* by a computer system. Too often we lose sight of this and we enagage in reification, turning the computer system into the reality - the data that's in the system *becomes* reality.
Modifying a well modeled database (5th NF +) is easy to do since all the rules are defined there. Instead when rules and processes are scattered throughout the inputs of any model, change is *more difficult*, not less. Of course, this assumes a well modeled database, which most businesses have been running from for ages, because it requires *knowledge* of what the business actually does!
October 16, 2015 at 7:55 am
RonKyle (10/16/2015)
While the final answer may depend on the skills that are available in-house. Also, if the application will never be large, it probably doesn't matter. But if you are building a large application that needs to scale well, then the business logic absolutely belongs in the middle-tier.The middle-tier can have lots of servers with copies of the code. But ultimately they have to point to a single database. If the logic is in the database and the middle tier simply serves as a way station to pass requests, it will fast reach a choke point. If the logic is on the middle tier, the real work can be spread among the various servers who do the heavy lifting and reduce the final answer to an insert/update/delete.
I've seen the gain in performance when the logic is moved from the database (mostly in stored procs) to a middle tier with multiple servers. As a DBA who can also program, each has its place.
I'm not advocating that the middle-tier replace the PK and FK checks, though. That is not only properly done at the database, it's vital for the integrity of the data that it's there.
Ron pretty much covered it all.
The more you are prepared, the less you need it.
October 16, 2015 at 7:56 am
This has always bothered me: What really is meant by "business logic"?
This is a good point, and someone else has also mentioned it. As you point out, many business rules are enforced in the database by design, such as the relationships between entities. But calculations on order totals with rules for taxes and discounts are business logic and probably which most of us would agree are best handled in the middle tier. When we moved these rules out of a lengthy stored procedure and into a more compact middle tier, we experienced an increase in performance.
But if you try to maintain the PK and FK relationships via code, you are likely to see a performance decrement because the code can't do this as well as the database.
There is a feel that you gain for these things with experience. But they probably can't be reduced to a few handy dandy rules and regulations, only examples.
October 16, 2015 at 8:13 am
jack.erdey 16020 (10/16/2015)Another way of looking at it is core competency. What is the purpose of an RDBMS? Its core purpose as a tool is to ensure the integrity of data while being read and updated my numerous users across an application. The procedural language of SQL is designed for and does a great job at data-access. CRUD. By keeping procedures small and data-focused you encourage less contention and greater throughput. Leave the job of data access/integrity to the DB and business logic to the middle-tier. Now granted business logic is inherent in the normalized design of the DB, but these usually take the form of relational and column level constraints (but even column level constraints can be over-done).
The core purpose of the RDBMS is as an inference engine. It is not simply a place that data is stored. The rules defined in the RDMBS describe the reality that is being modeled, and allow one to make provably correct statements about the real world, based on the relational algebra and predicate logic. SQL is imperfect, certainly, but if all it is for is for data access, you might as well just use flat files. Chris Date's books are an excellent starting point to help developers who are going down all the paths that we've been down over and over again.
October 16, 2015 at 8:44 am
We're a small to medium size company and we use Access and Access Web Apps coupled with SQL Server and Azure with great satisfaction since it can also handle managed code.
October 16, 2015 at 9:00 am
Oh Ben, you sure know how to pick a hot topic for Friday. 🙂
Well, I can honestly say that I've been on both sides of this issue. At my old job I was there for many years. I helped start the business and helped build all of the software solutions used there. I wrote many of the stored procedures. Sometimes I put the business logic within a stored procedure. In particular one instance I put the business logic in the stored procedure because the front-end application had no knowledge of all of the tables involved performing the transaction. Also, I wrote the SP to do this back in the late 90's. Our network at the time was abysmally slow. I was concerned that doing all of the necessary business logic in the client (we weren't using a middle-tier architecture at the time; didn't know about that) would have been too slow, so I put that business logic into the SP. At the time I thought it was the right decision given the circumstances. But as time went by and other developers came and went they would, politely ask me why I put the business logic in the SP and not have taken it out and put it into a middle-tier component of some sort.
Now fast forward to my current job. The tables have certainly changed. I now work for a large state department. Of the several large software products we've purchased and used, I'm familiar with one of them. It was written by a SQL DBA in another state for her state's department. They license it to about a dozen states throughout the country. It's there to help satisfy Federal government requirements for the type of data collected and reported to the Feds. This developer is first and foremost a SQL DBA. I've seen her stored procedures and they literally amaze me. I've never seen such sophisticated SQL code. But being a DBA first, she isn't much for front-end development. (I don't mean to say she's a bad coder or anything like that. This is jus to say that her strengths are in the back-end. I wish she'd left the front-end development to someone else.) The front-end is an ASP.NET Web Forms app, which is basically just controls on the page. She performs all SQL queries in components and then manually moves the data into collections - i.e.: she doesn't use what more ASP.NET Web Forms developers would use and that is data binding. (OK, she does use data binding, but not in the way that you'd expect as an ASP.NET Web Forms developer.) Consequently what she's got is all of her business logic in SP's and front-end code which is not standard. In our environment this really causes problems. The DBA's here have so many databases to contend with that they cannot do maintenance to the SP's, SSIS packages and God only knows where else the original DBA/developer put all of that stuff. That leaves the programmers to try and contend with it, but they're skills aren't up to the challenge. I'm one of those programmers. Before this job I only knew of the acronym of "SSIS". I've learned some more about it, watched some Pluralsight training videos, but my involvement with this software package is going away. The other developer is just going to be left out in the cold.
So I do not support the idea of putting business logic into the database, because of my experience with this. The people tasked with doing the maintenance on this app aren't capable of doing it. That's because they're skills are elsewhere. And frankly, even if they had the necessary SSIS skills, they prevented from doing any work on SSIS packages; they don't have the permissions. So changes need to be done to the whole system, but they can't be done by the people given the job to do it can't as they don't have the necessary skills. Its a lose-lose situation. So from this point forward I am not in favor of putting business logic into the database. And especially I am against putting all business logic into the database!
Kindest Regards, Rod Connect with me on LinkedIn.
October 16, 2015 at 9:06 am
Rod at work (10/16/2015)
Oh Ben, you sure know how to pick a hot topic for Friday. 🙂
Rod, the hot topics are the most fun. It is interesting to see everyone's opinions and experiences, even if we don't all agree.
Ben
October 16, 2015 at 12:18 pm
RonKyle (10/16/2015)
This has always bothered me: What really is meant by "business logic"?
This is a good point, and someone else has also mentioned it. As you point out, many business rules are enforced in the database by design, such as the relationships between entities...
I think business logic is a poor label of what really is multiple kinds of logic. When developers tell me they want a database without business logic, I tell them "OK, all you need is 1 table with 2 columns, attribute name and attribute value." There's data logic, process and workflow logic, and interface logic. Trying to put any one of those three somewhere other than their natural place, (database, middle tier, application) will cause grief on many levels. .Net is bad at dealing with sets of data, just as SQL Server is bad at dealing with a process or workflow. Views and stored procedures act as an abstraction layer between the physical data structures (tables) and the process/workflow.
Now what I've experienced at the several different companies I've worked at over the past 20+ years is that the work tends to get done in whatever the path of least resistance is. 😉
October 16, 2015 at 12:24 pm
There's data logic, process and workflow logic, and interface logic.
This is really a great way to break it out. If this were fleshed out, you might have an article.
October 16, 2015 at 12:40 pm
"SQL is imperfect, certainly, but if all it is for is for data access, you might as well just use flat files."
LOL
October 16, 2015 at 1:13 pm
Security is also an issue. We've seen instances where customers steal code that is in Stored Procedures, take over maintenance themselves, and stop paying for the product. If more logic had been in the application, this would have been much more difficult to do.
October 16, 2015 at 3:38 pm
When working in the video game industry in online games, we relied on the middle tier and the client to distribute the logic and the processing that SQL Server cannot provide. The client (e.g.: application layer) for example is the sheer definition of distributed processing. If all that logic lived in the database, it would break. So, it's layered and balanced between all tiers and not heavily being pushed on just one engines shoulders.
The reality is SQL Server is a bottleneck at scale because as you grow, SQL has to do more work because you're putting more work on SQL Server's shoulders outside of the engines core responsibilities. While it surely can be managed easier in the database, it doesn't justify the sacrifice to performance in the right use case.
However, in my current use case, I keep all logic in the database. SQL Server is the BI platform. Everything is batch, everything is controlled by SQL Server. SQL calls the API's via Python. SQL controls when data is ingested, how the logic is applied, and when it's ready to be used. It's efficient, easy to manage and does a damn good job for that use case.
So, to answer the question, "Where should middle tier business logic be stored?" It depends... How tall is a tree? :hehe:
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply