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 «««12345»»»

Always Abstract Expand / Collapse
Author
Message
Posted Wednesday, August 21, 2013 11:16 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 212, Visits: 609
Steve,

Agreed!
Post #1486817
Posted Wednesday, August 21, 2013 11:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:40 AM
Points: 327, Visits: 304
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.
Post #1486827
Posted Wednesday, August 21, 2013 11:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:43 PM
Points: 814, Visits: 2,014
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.



Post #1486828
Posted Wednesday, August 21, 2013 11:55 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 212, Visits: 609
@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.
Post #1486831
Posted Wednesday, August 21, 2013 12:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 1,651, Visits: 4,707
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;

Post #1486833
Posted Wednesday, August 21, 2013 1:16 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 760, Visits: 2,194
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!




The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Post #1486889
Posted Wednesday, August 21, 2013 1:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:33 PM
Points: 1,350, Visits: 753
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.


Post #1486903
Posted Wednesday, August 21, 2013 2:42 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 1,651, Visits: 4,707
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.
Post #1486942
Posted Wednesday, August 21, 2013 2:53 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 24, 2014 3:38 PM
Points: 336, Visits: 942
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.
Post #1486955
Posted Wednesday, August 21, 2013 4:19 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:07 AM
Points: 2,901, Visits: 1,805
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.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #1486991
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse