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 8:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 18, 2014 9:33 AM
Points: 20, Visits: 112
I've always liked an "interface" approach, using stored procedures.
If a developer needs to access customer information, you give them a "GetCustomer" stored procedure.
This insulates them from the schema and schema changes... you can completely re-architect the underlying tables as long as their interface works the same. This also gives you excellent control over security, as you can have separate interfaces like "GetCustomer" and "GetCustomerPlusConfidentialData", each with its own permissions.
Unfortunately, this takes more work up front, and it tends to get messy over time when you end with 10 different "GetCustomer" flavors (of which you probably only need 2-3).

In my experience the view approach usually leads to performance problems. It's OK when the view is SELECT a,b,c FROM [sometable], but as soon as the views get more complicated, queries joining multiple views together get hard for the query optimizer to work with.
Post #1486753
Posted Wednesday, August 21, 2013 8:39 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 12:13 PM
Points: 485, Visits: 1,174
We have an 3rd party HR application that takes exactly this approach. Mainly to implement row based security in the app by joining in a table that contains the employee id's that staff are permitted to see. The views get used for basic CRUD operations in the app screens and also in reports. Stored procs are used when a process requires more than a single query to accomplish the task. In this way most of the business logic has been moved into the database.


Post #1486755
Posted Wednesday, August 21, 2013 9:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:11 AM
Points: 159, Visits: 467
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 , but the reality is somewhat different.

Post #1486774
Posted Wednesday, August 21, 2013 9:37 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 214, Visits: 724
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 , but the reality is somewhat different.



As a developer I have to agree 100%.
Post #1486778
Posted Wednesday, August 21, 2013 9:46 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:16 AM
Points: 8,289, Visits: 8,742
I like the "do everything through stored procedures" approach, and have been practising and advocating it for more than a decade. I'm surprised it isn't used more often, since it is quite certainly the surest way to get a really secure system.

The statement "I rarely see this in place" in your article surprised me too. I knew quite a few systems that had it in place in the early 2000s. I worked knew two systems that not only had the all stored procedure approach but also had quite a lot of their C++ and/or JScript code driven from SQL (some of it as extended SPs, some through activating jobs with cmd and/or ActiveX jobsteps, some using xp_comandshell).


Tom
Post #1486783
Posted Wednesday, August 21, 2013 10:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:11 AM
Points: 159, Visits: 467
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.
Post #1486790
Posted Wednesday, August 21, 2013 10:25 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 214, Visits: 724
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?
Post #1486800
Posted Wednesday, August 21, 2013 10:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:11 AM
Points: 159, Visits: 467
@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.
Post #1486805
Posted Wednesday, August 21, 2013 10:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:33 AM
Points: 1,786, Visits: 1,922
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.
Post #1486809
Posted Wednesday, August 21, 2013 11:15 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 4:11 PM
Points: 32,810, Visits: 14,959
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 , 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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1486816
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse