October 2, 2012 at 6:50 am
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)
October 2, 2012 at 6:54 am
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) = 0but 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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2012 at 6:55 am
norbert.manyi (10/2/2012)
Take the following example:
select * from tbl_Users U where U.active = 1 and U.deleted = 0It 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) = 0but 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 Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
October 2, 2012 at 7:00 am
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?
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
October 2, 2012 at 7:06 am
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 = 0How 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.
October 2, 2012 at 7:24 am
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.
October 2, 2012 at 7:27 am
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.
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
October 2, 2012 at 7:31 am
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...
October 2, 2012 at 8:03 am
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).
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
October 2, 2012 at 8:17 am
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.
October 2, 2012 at 9:33 am
Jayanth_Kurup (10/2/2012)
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.
I (guess I) understand... Up until now I tried to push the business logic up onto the database side (used to work on desktop apps, where changes to the BL could be made much easier if it was on the central server)
Then I guess for the future, we should try to separate the .Net side into display components and BL components, and focus on moving the BL that cannot be done using views to the .Net side (will not consider MVC or similar frameworks)
October 3, 2012 at 2:41 am
You should bear in mind that moving business code to the application does introduce a possible loss of integrity. Does the application assure that every set of interdependent modifications are wrapped into transactions? If you need to change the physical database schema for performance reasons, will the application developer be able to incorporate this change easily in his code? I am both an application developer in .NET and a SQL Server DBA in several larger projects, and sometimes I favor .NET over stored procedures only because my fellow team members are not as familiar with SQL as I am.
Please think twice before you move business logic into the application. If it changes so often, how can any developer assure that 'old' data is still valid for the 'new' code? Who keeps track of all those changes to avoid any flaws an conflicts that might occur at the storage level inside the database? Many developers do not like foreign keys because they limit them severely in the way modifications on the data may be performed. But every 'relation' without a foreign key constraint is doomed to loose its integrity sooner or later.
October 3, 2012 at 5:48 am
All the developers are on both SQL and .Net, so we will not have those kind of problems.
Most of my concerns are regarding keeping everything in one place, and as clean as possible.
So, at the moment, I can say I'm confused 🙂 I am aware of the speed gained by keeping everything in optimized stored procedures, but in the same time, I would like to maintain flexibility to make changes in BL faster, and with less modifications.
October 4, 2012 at 8:55 am
norbert.manyi (10/3/2012)
All the developers are on both SQL and .Net, so we will not have those kind of problems.Most of my concerns are regarding keeping everything in one place, and as clean as possible.
So, at the moment, I can say I'm confused 🙂 I am aware of the speed gained by keeping everything in optimized stored procedures, but in the same time, I would like to maintain flexibility to make changes in BL faster, and with less modifications.
From what you've desribed, I think a view would provide the business logic encapsolation you need. As far as the employee table itself, it would be useful to have active_date, inactive_date, and status_code columns indicating the timeframe and reason for the employees status.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply