Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»

Where Logic Lives Expand / Collapse
Author
Message
Posted Wednesday, August 24, 2005 3:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 07, 2006 2:28 AM
Points: 53, Visits: 1

Hi all
and thank you for contributing with thoughts and ideas I will bring on down the road of designing.

I'm a lazy b#st#rd so I put all logic into my db. If I need to change the logic (hmmm....) I don't need to re-compile my front-end project - but, as you all know - I end up re-compiling the project anyways...

But as far as I know, shouldn't there be a middle-tier that holds the logic?

DB is for data - Front-end is for viewing, the logic goes in between, into a dll for instance.
(I don't know if this approach is applicable in a asp-app.)

Could anyone set me straight?

/Ola L M

Post #213310
Posted Wednesday, August 24, 2005 4:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, May 20, 2006 9:25 PM
Points: 383, Visits: 1

David I agree that much of this discussion is based on semantics and your proposal "that any logic, the avoidance of which may produce rows in the database which do not reflect true propositions, belongs inside the DBMS and should be a function for the DBMS to enforce." is correct. I would add that any logic that does not directly address these requirements be placed elsewhere.

[edited to protect the good name of Mike C by using my full name}

Mike Du Bois

All this talk of beer and its too early. I think I will have an Irish coffee instead.  

Post #213324
Posted Wednesday, August 24, 2005 10:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 08, 2012 6:31 PM
Points: 1, Visits: 27
I wish I had gotten in on this discussion earlier.

I have written applications with logic on the database server, and logic in the middle tier. I try to put all of my logic in stored procedures for smaller and mid-size applications. If I know that I am not going to outgrow my SQL Server box , I put my logic on the server. For instance, if the application has less then 1000 users taking phone orders, a dual processor server with 4G of ram is sufficient and even has room to grow. So scaling out would never be necessary.

Why keep the logic on the server? Performance! There is no faster way to get data to a user than having SQL Server spit out data that is ready to use. If SQL server sends data to the application server that needs additional processing, it will take longer before the user sees the data. The application server can't process the data any faster than the database server can, and it often can't do it nearly as fast.

This only works well if you are not stressing your SQL Server box. If you are keeping your processors pegged at 100% and you can't scale up, then having the logic on the server is a performance hazzard. This is when you should share the work load and let the application servers handle as much work as possible, leaving the database server to handle data requests only.

Bill
Post #213519
Posted Wednesday, August 24, 2005 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 20, 2008 7:50 AM
Points: 1, Visits: 4

"I try to put all of my logic in stored procedures for smaller and mid-size applications. "

I do the same. My philosophy is to use stored procedures to transform data into information, then make that information available to application programs. The dividing line is generic vs. specific: if a certain type of information needs to be made available to all applications, I put the logic in a stored procedure - if it is specific to one application, provide that application with the necessary base information and let the application code perform any additional data manipulation needed.

It helps that the applications involved are analysis intensive and don't use all that much data - this philosophy might work poorly when applied to a few million ATM transaction records in a terabyte database.

Post #213532
Posted Wednesday, August 24, 2005 4:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 18, 2013 11:20 AM
Points: 67, Visits: 135

Mike

Excellent article. Just last week I had a tough time with my frontend dev team. I refused to add string formatting in stored procedures and the dev team was almost forcing me to do it in SP. Finally I took my stand and said NOOO. When the manager asked the develpers what is the problem in doing the formatting in the front end, there was a quite silence and they agreed to do it in the front end. Later i did a google and sent them an article which clearly explains why string manipulations should be avoided in stored procedures

I wish the front end developers understand that for the performance of the application it is a good idea not to load the SQL Server.

No one thinks of longterm impacts, they just want to do a easy and fast fix!

Ranga

 




Post #213694
Posted Wednesday, August 24, 2005 7:20 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:05 AM
Points: 2,553, Visits: 559
sorry ranga - but I really wish you'd picked any example but this...formatting and display have nothing to do with logic and rules...(imo)!







**ASCII stupid question, get a stupid ANSI !!!**
Post #213714
Posted Thursday, August 25, 2005 12:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 18, 2013 11:20 AM
Points: 67, Visits: 135

sushila, my point was that database is overloaded with stuff which could very well be done by the front end code or midlle tier!

Just beacuse you have got upto 140 miles in your car's speedometer, it doesn't meant we can do 140!

I hope it is clear now.

 

 




Post #214031
Posted Thursday, August 25, 2005 12:25 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 7:51 PM
Points: 1,244, Visits: 3
I thought that today's article had an interesting tidbit that indicates that we've been asking to put more into the database, not less.

http://www.sqlservercentral.com/columnists/sjones/sqlserverspotlightonbrianweckler.asp

"SSC : Why did Microsoft decide to add the Reporting Services engine to SQL Server?

Brian : It was customer feedback really. What we heard was that customers wanted a reporting solution that integrated with their Microsoft data sources, productivity applications, and developer tools. As we had already added OLAP and data mining to the relational database in SQL Server 2000, it was a logical place for reporting."





Post #214034
Posted Thursday, September 22, 2005 10:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 22, 2005 9:51 PM
Points: 1, Visits: 1

Mike,

Having recently read your reaction to "Logic in the Database", my impression is a few facts might be helpful. You wrote

"he made a case for placing a majority of the business rules and logic in the database"

The article (http://www.sqlsummit.com/articles/logicinthedatabase.htm) actually has a different message and it says:

"There is no single best solution for distributing the logic of a database application, and the choices are many."

The point is that databases today are more than passive data containers. As a rule enforcer, they provide data integrity and ensure consistent behavior. Many databases are accessed by a variety of clients written in different scripting and programming languages (Visual Basic, Javascript, PHP, C#, C++). For example, no matter what scripting or programming language is used to develop clients and servers, they see consistent behavior if they execute sp_accountoverdue.

"most importantly, databases don’t scale."

Scalability is a product of the system architect's skill. It's no longer a limitation of the technology. Consider these facts;

1. It's been 8 years since Microsoft demonstrated SQL Server processing 1 billion transactions in a day (11-14,000 tps).
2. The best performance on TPC-C leader is currently 3,210,540 transactions per minute (53,000+ tps).
3. UPS is doing 1.1 billion SQL transactions per hour on a federated DB2 database.
4. Verizon has an SQL Server database that's 50,747,000,000 rows.

"I can also tell you from experience that over time Ken’s theories on the advantages of this type of model will be proven wrong or at best cumbersome and clumsy."

Microsoft's choice to add the CLR to SQL Server 2005 was not risky business -- letting users embed classes in databases is not revolutionary.

Other SQL vendors (IBM, Informix, Oracle, Sybase) have been there and done that. They've shipped SQL servers that integrate the Java VM, support embedding Java classes in databases, Java stored procedures and UDFs, etc. It's mature technology that's shipped in product releases since 1997.

If this model of extensible databases had proven "wrong or at best cumbersome and clumsy", it's fooled the brain trust of the biggest database vendors. Microsoft isn't the only company integrating the CLR into their SQL servers. IBM and Oracle have done the same.

 

Post #222930
Posted Wednesday, August 23, 2006 8:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 23, 2006 8:46 AM
Points: 3, Visits: 1

Interesting points, however I would draw a distinction between data integrity, business rules, and UI elements (such as formatting). Experience tells me that whatever is physically allowed in the DB will get in there at some point; and we all know that "the app" isn't the only way of interfacing with the DB.

For my money, the DB needs to have enough logic to ensure data integrity at a minimum. This I would define as part of the base tables.

I tend to prefer a relatively passive UI, and I think one can make a good argument that any functionality that involves only information, and no UI elements, can and probably should be coded into a DB interface layer of stored procs, views, functions etc. This promotes flexibility, reliability, and ease-of-maintenance via the 'single access point' model.

I mean, do we really want to replicate all of our business and data integrity validations at every point where the app touches the DB? And then let all the DBAs and developers skirt that logic via direct backend access? Both are recipes for trouble of you ask me.

Post #303532
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse