August 21, 2013 at 10:01 am
Tom, I agree strongly with you. I favour moving as much application business logic into stored procedures as is possible, thereby abstracting out the UI layer from the business logic. And our shop uses UDFs frequently as well as sprocs, so as to maximise our code reuse.
Like you, it puzzles me to see these practises less widely adopted elsewhere than I do.
August 21, 2013 at 10:25 am
Craig-315134 (8/21/2013)
Tom, I agree strongly with you. I favour moving as much application business logic into stored procedures as is possible,
As a developer I have to disagree. 🙁 Where or how do you determine what database to put your store procdure in? Do you just designate one? I know we have a database called 'work_db' the SQL may read or write to tables on the database but it may also read from 2 or more other databases maybe even write to 2 or more.
And as I stated earlied how do you look at a log for a stored procedure? If I have a 1000 line SQL with 50 different SQL statements and it fails in the middle of the night how do I determine it failed on line 50 or 500?
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 21, 2013 at 10:46 am
@below86 wrote:
Where or how do you determine what database to put your store procedure in?
Different shops may have different practises; some shops designate a specific database for siting sprocs/UDFs.
The important thing I'd like you to keep in mind, though, is that we're talking about application design practises, to which sprocs and UDFs are incidental. What is desirable in my view is the abstraction of business logic from the presentation layer, and the ability to re-use that business logic across multiple applications.
So if an application's business logic can be readily encapsulated within a sproc, that is a viable option; often it is the best one, at least in my shop. But the logic could also be encapsulated within, say, a .Net assembly, or within a compiled and link-edited COBOL sub-program.
August 21, 2013 at 10:53 am
Craig-315134 (8/21/2013)
... I favour moving as much application business logic into stored procedures as is possible, thereby abstracting out the UI layer from the business logic. And our shop uses UDFs frequently as well as sprocs, so as to maximise our code reuse...
What I've typically seen is the following:
- application developers are focused on .Net or whatever their language is, and have limmited SQL knowledge
- few companies actually have separate database developers that do know the SQL side of things
- the DBA typically doesn't have the time to write all the stored procs that the application developers need
- proliferation of ORM systems and design methodoloy that say the database should just have CRUD operations
Basically when people are uncomfortable with the database side of things they don't want to put things there. A number of application developers view databases as either a necessary evil or even a hinderance to their work rather than an equal partner in things. They tell me as a DBA that there shouldn't be any business logic in the database, which I usually jokingly reply all they need then is 1 table with 2 columns, AttributeName, AttributeValue. 😀
My perspective, even when I used to be an application developer way back when, is that there really are 3 kinds of "business" logic: data logic, workflow logic, and interface logic. I then decide where code should go based on what needs to be accomplished, and try to right size things since .Net isn't good at working with sets of data, and the database isn't good at handling workflows and obviously doesn't have much user interface.
August 21, 2013 at 11:16 am
Craig-315134 (8/21/2013)
The author wrote:... I sometimes think that an investment in developer education would quickly erase any productivity losses ...
Education is always a good thing, but it applies all around, too. In my experience, I've seen numerous DBAs who needed to learn a few things about the real world challenges faced by developers.
DBAs are not the font of all wisdom. Perhaps we'd like to think so :-D, but the reality is somewhat different.
Very true. I didn't mean to imply just .NET/Java/PHP/etc developers. I'd include SQL developers in there as well.
There are challenges faced on both sides. A gross generalization is the non-SQL developers tend to face short term problems and DBAs/SQL developers face longer term ones. We need to work together.
The implication isn't that it's developers' fault, but that learning more about how to better write SQL can improve productivity and produce better code.
August 21, 2013 at 11:16 am
Steve,
Agreed!
August 21, 2013 at 11:47 am
Where I work, we have kind of a hybrid approach. We recently created views for all our tables, refactored the code, and then removed all access to those tables from the service level account our application runs under. It has read permissions on the views, so we can still write ad-hoc queries, but all inserts/updates/deletes have to be done through stored procedures.
August 21, 2013 at 11:48 am
I favour moving as much application business logic into stored procedures as is possible, thereby abstracting out the UI layer from the business logic
I think I can help with wondering why it's not in wider practice--this is not a good idea. The database is a chokepoint, and you want it processing as few business rules as possible. The enforcement of business rules really belongs in the middle tier, whatever that might be for the application. Let the code in the intermediate levels do their thing and then send the result to the database.
August 21, 2013 at 11:55 am
@ronkyle wrote:
The database is a chokepoint ...
I can't imagine it being a chokepoint any more so than anything else. Network bandwidth can become a chokepoint. So can application servers. So can an application. If a component isn't configured correctly, isn't sized right for the job, isn't maintained properly - well, sure, it'll become a chokepoint.
August 21, 2013 at 12:01 pm
In addition to implementing views as a means of rrestricting user access to columns and rows, I've also used them as a wrapper for tables that contain symmetric key encrypted columns, having computed columns perform all the tedious function calls. In the example below, all the application has to do is properly open the symmetric key, and then it can select from the view, returning data unencrypted and re-cast as needed. Before writing views to do this, I was getting way too many questions from developers and analysts about how to decrypt columns. Now it's transparent.
create view ABC.vMembership
as
select
Patient_ID,
Group_Nbr,
Begin_Date,
Term_Date,
cast(DecryptByKey(First_Name) as varchar(30))First_Name,
cast(DecryptByKey(Birth_Date) as datetime)Birth_Date,
cast(DecryptByKey(Subscriber_ID) as varchar(30))Subscriber_ID,
cast(DecryptByKey(Phone_Nbr) as varchar(30))Phone_Nbr
from ABC.Membership;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 21, 2013 at 1:16 pm
Craig-315134 (8/21/2013)
@RonKyle wrote:The database is a chokepoint ...
I can't imagine it being a chokepoint any more so than anything else. Network bandwidth can become a chokepoint. So can application servers. So can an application. If a component isn't configured correctly, isn't sized right for the job, isn't maintained properly - well, sure, it'll become a chokepoint.
I have to agree with Craig here. The database can be a choke point but it isn't always. We have one application here where the application itself is the choke point. And we've finally gotten the vendor to acknowledge it!
August 21, 2013 at 1:41 pm
I find this editorial interesting because over a decade ago I the development team I was working on had this exact discussion.
I was a developer at that time and I was pushing everything to stored procedures. The limitation we had the time though was that I was unable to do queries via stored procedures because the database I was working with and the application deveopment tool would not allow results to be returned and consumed. This was also help lead us into the discussion of using views.
The number one reason for the failure of using stored procedures was that we where a RAD/JAD, Rapid Application Develpoment / Joint Applicaiton Development, shop. Because of this we constantly making changes midstream and between knowledge of the development staff of being able to write procedures and the turn arround time of getting changes to the stored procedur applied to the database. Slowly the code shifted back to the application tier.
Fast forward to today and the new champion is Entity Framework. It has become the choice to create the abstraction from code to database tier.
For the most part abstration is a good thing. I really don't want to have to remember how to write the code to add an item to a list array. I just want to be able to call $list_items += "New Item" and be done with it. Just like a developer doesn't want to rewrite the code everytime to loop through results and add result paging or to do work around all the plumbing to do a simple insert and update of a record.
August 21, 2013 at 2:42 pm
eccentricDBA (8/21/2013)
I find this editorial interesting because over a decade ago I the development team I was working on had this exact discussion.I was a developer at that time and I was pushing everything to stored procedures. The limitation we had the time though was that I was unable to do queries via stored procedures because the database I was working with and the application deveopment tool would not allow results to be returned and consumed. This was also help lead us into the discussion of using views.
The number one reason for the failure of using stored procedures was that we where a RAD/JAD, Rapid Application Develpoment / Joint Applicaiton Development, shop. Because of this we constantly making changes midstream and between knowledge of the development staff of being able to write procedures and the turn arround time of getting changes to the stored procedur applied to the database. Slowly the code shifted back to the application tier.
Fast forward to today and the new champion is Entity Framework. It has become the choice to create the abstraction from code to database tier.
For the most part abstration is a good thing. I really don't want to have to remember how to write the code to add an item to a list array. I just want to be able to call $list_items += "New Item" and be done with it. Just like a developer doesn't want to rewrite the code everytime to loop through results and add result paging or to do work around all the plumbing to do a simple insert and update of a record.
Entity Framework is OK, so long as you're using it for a case usage like bookmark lookup / edit / save.
However, there is a "code first" feature where app developers use EF to design a logical model for their application and then EF auto generates DDL scripts. Using EF as a database design tool leads very poor database implementations.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 21, 2013 at 2:53 pm
I do everything myself, application side and database side. I do not allow (myself) access to tables directly. Views, read-only, even if it's just a "SELECT everything FROM table" statement. Updates and deletes are done through stored procedures only.
Allows me to change a table and all I have to do is change the view to accomodate the change - often zero change necessary to the application. And if there is a problem with changes to the data, it all funnels through procedures, which I can modify to include traces, metrics, error checking, whatever is necessary, ALL with NO impact on the application.
Didn't think of this myself, saw the advice somewhere when I started learning SQL Server, maybe even on this site. Thought about it, decided it was a good idea and have done it that way exclusively.
I'll always do it that way, unless something even better comes along. I can think of no reason to ever do it any other way. Snivels about performance don't fly. This system, used properly, will perform just as well as allowing direct access to tables, often much better. If the performance isn't adequate, look at the design. Unlocking access to tables for performance reasons is cop-out, and dangerous to boot.
August 21, 2013 at 4:19 pm
I read about Simonyi and why he prefixed stuff and Hungarian notation is a misinterpretation of his work.
The prefix was to denote purpose but has been misinterpretted as denoting type.
I've also seen the argument about whether or not to put business logic in the DB layer come around full circle and no doubt it will do a few more loops over the next decade.
I am sceptical about the argument that putting the SQL in the app makes it more readable and maintainable. I could understand it if the tools available or the favoured tool was not particularly good at dealing with database objects. To me stored procedures are conceptually the same as refactoring out code down into smaller and smaller methods. Yes it becomes a pain in the arse to drill down deeper and deeper into the code to find its like a Russian doll but ultimately you end up with a single simple method call.
I think where there is an unhealthy separation of roles between application developers and SQL developers then you get friction points and misunderstandings. I was an application developer long before I dealt with SQL Server so when I started out the idea that I could encapsulate a query to GetCustomerByID into a single stored proc was like manna from heaven!
From a production DBA perspective I think a large part of the worry is that there will be a 3am phone call complaining about a DB problem and all you can do is point to a particular query but have no way of fixing or mitigating the problem. Word gets around that "its a DB problem" and you end up whipping boy. I've been on the wrong side of a blamestorming session and no facts were allowed to contradict the "its a DB problem". It was actually an unclosed transaction in a loop that instantiated command objects to execute in RBAR fashion.
Viewing 15 posts - 16 through 30 (of 90 total)
You must be logged in to reply to this topic. Login to reply