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 12»»

how to avoid duplicate business logic Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 6:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 3:37 AM
Points: 13, Visits: 76
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
Posted Tuesday, October 2, 2012 6:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 42,998, Visits: 36,154
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
Posted Tuesday, October 2, 2012 6:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:09 PM
Points: 437, Visits: 334
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
Posted Tuesday, October 2, 2012 7:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Posted Tuesday, October 2, 2012 7:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 3:37 AM
Points: 13, Visits: 76
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
Posted Tuesday, October 2, 2012 7:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 3:37 AM
Points: 13, Visits: 76
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
Posted Tuesday, October 2, 2012 7:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Posted Tuesday, October 2, 2012 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 3:37 AM
Points: 13, Visits: 76
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
Posted Tuesday, October 2, 2012 8:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Posted Tuesday, October 2, 2012 8:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,789, Visits: 1,014
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 12»»

Permissions Expand / Collapse