Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » how to avoid duplicate business logic
Add to briefcase
14 posts, Page 1 of 2
12»»

how to avoid duplicate business logic Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
norbert.manyi
Posted Tuesday, October 02, 2012 6:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 3:17 AM
Points: 13, Visits: 73
Take the following example:

select * from tbl_Users U where U.active = 1 and U.deleted = 0

It seems that the application owner keeps changing the business logic, so I would like some opinions on how I can make the business logic as separated from the code as possible.

My first idea (given my coding background) was to create functions to be used like

select * from tbl_Users U where fn_IsUserActive(U.UserID) = 0

but I don't know about how much that would degrade performance...

Ps. I understand that this particular example can be done using views, but I'm taking about the situations where you cannot do this. (I just can't find a good example right now)
Post #1366994
GilaMonster
Posted Tuesday, October 02, 2012 6:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946
norbert.manyi (10/2/2012)

My first idea (given my coding background) was to create functions to be used like

select * from tbl_Users U where fn_IsUserActive(U.UserID) = 0

but I don't know about how much that would degrade performance...


A lot. That query form means that indexes are useless and the only way to process that is with a scan.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1366997
Roland Alexander STL
Posted Tuesday, October 02, 2012 6:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 305, Visits: 311
norbert.manyi (10/2/2012)
Take the following example:

select * from tbl_Users U where U.active = 1 and U.deleted = 0

It seems that the application owner keeps changing the business logic, so I would like some opinions on how I can make the business logic as separated from the code as possible.

My first idea (given my coding background) was to create functions to be used like

select * from tbl_Users U where fn_IsUserActive(U.UserID) = 0

but I don't know about how much that would degrade performance...

Ps. I understand that this particular example can be done using views, but I'm taking about the situations where you cannot do this. (I just can't find a good example right now)


How is the application owner changing the business logic? Is the business changing their procedures? Or is more a case of changing SARG values? A little more detail's needed, I think, before we can begin to answer.

Roland Alexander
The Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Post #1367001
ChrisM@Work
Posted Tuesday, October 02, 2012 7:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 5,608, Visits: 10,970
norbert.manyi (10/2/2012)
...Ps. I understand that this particular example can be done using views, but I'm taking about the situations where you cannot do this. (I just can't find a good example right now)


Views would be a better option than functions - coded properly they won't hammer performance quite like the suggested UDF. You can't find a good example right now - if you still can't find one in an hour, would it matter to you if there was the odd documented exception to the rule?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1367007
norbert.manyi
Posted Tuesday, October 02, 2012 7:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 3:17 AM
Points: 13, Visits: 73
Roland Alexander STL (10/2/2012)
norbert.manyi (10/2/2012)
Take the following example:

select * from tbl_Users U where U.active = 1 and U.deleted = 0



How is the application owner changing the business logic? Is the business changing their procedures? Or is more a case of changing SARG values? A little more detail's needed, I think, before we can begin to answer.


For the previous example, we had to add a TempInactive field, so employees on maternal/paternal leave can be excluded. Also business decided (against my insistencies) that deleted employee records are no longer kept, so the deleted field became obsolete.

But I got a better example in the following post.
Post #1367013
norbert.manyi
Posted Tuesday, October 02, 2012 7:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 3:17 AM
Points: 13, Visits: 73
Views would be a better option than functions - coded properly they won't hammer performance quite like the suggested UDF. You can't find a good example right now - if you still can't find one in an hour, would it matter to you if there was the odd documented exception to the rule?

Example:
Employees can belong to regions and/or teams. Teams are split into organizations.
There is more than one type of team-leader. One that is directly above the employees, one that is in charge of a region and one that is in charge of an organization.
Most reports can be run by either type of team-leader for their employees, and they all do filtering at the beginning. (this is how it was before we took over)

Although this can also be done using multiple views and a few lines of code, I think this is getting closer to the 'cannot use views' scenario.
Post #1367026
ChrisM@Work
Posted Tuesday, October 02, 2012 7:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 5,608, Visits: 10,970
norbert.manyi (10/2/2012)
Views would be a better option than functions - coded properly they won't hammer performance quite like the suggested UDF. You can't find a good example right now - if you still can't find one in an hour, would it matter to you if there was the odd documented exception to the rule?

Example:
Employees can belong to regions and/or teams. Teams are split into organizations.
There is more than one type of team-leader. One that is directly above the employees, one that is in charge of a region and one that is in charge of an organization.
Most reports can be run by either type of team-leader for their employees, and they all do filtering at the beginning. (this is how it was before we took over)

Although this can also be done using multiple views and a few lines of code, I think this is getting closer to the 'cannot use views' scenario.


If you can't code it up as a view, then it's unlikely that you can code it up as a query.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1367029
norbert.manyi
Posted Tuesday, October 02, 2012 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 3:17 AM
Points: 13, Visits: 73
ChrisM@Work (10/2/2012)

If you can't code it up as a view, then it's unlikely that you can code it up as a query.

You could still do it as a quite complex function, but I guess that would be wasteful.

Also, I am not stuck on functions vs views; if there are any other ideas/tips that I could use to keep the business logic more manageable, please mention them...
Post #1367031
ChrisM@Work
Posted Tuesday, October 02, 2012 8:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 5,608, Visits: 10,970
norbert.manyi (10/2/2012)
ChrisM@Work (10/2/2012)

If you can't code it up as a view, then it's unlikely that you can code it up as a query.

You could still do it as a quite complex function, but I guess that would be wasteful.

Also, I am not stuck on functions vs views; if there are any other ideas/tips that I could use to keep the business logic more manageable, please mention them...


Norbert - check out the two articles by Paul White in my signature block. Although the subject is APPLY, they will give you some useful information for constructing and using iTVF's (inline table-valued functions).

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1367058
Jayanth_Kurup
Posted Tuesday, October 02, 2012 8:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:43 AM
Points: 1,785, Visits: 1,008
faster simpler better coding relies on moving business logic to the application layer. This is proven in many SOA implementations. The DB should stick to simple crud operations. The stored procedure is already a layer of abstraction between the application and data , however in your case the data itself changes so functions and views or even normal tables need to evaluated on a case to case basis.

A table that had a id column as PK may not id a PK once a new column is added or deleted. Try putting business logic in .NEt and app layer and if that doesnt work evaluate how it can be done within a proc.

a common mistake i have seen people make is trying too hard to make the business logic generic. Its not a good idea coz the business logic is constantly changing to meet new scenarios and managing all that change within the DB is a major headache.

Jayanth Kurup
Post #1367066
« Prev Topic | Next Topic »

Add to briefcase
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.