Am I Old-Fashioned?
-
I function as DBA and developer, both. I find myself running into conflict with other developers on DB server access. My paradigm is that stored procedures are a tier in the n-tier development model, and should form the SOLE interface to the database.
As DBA, I don't want to have to know about what code is scattered where in the organization, and in what web page or Windows app. The stored procedure layer streamlines administration, and also centralizes the rules and logic of the business and database.
Now, one developer is proposing the use of Ruby on Rails (RoR). It is my understanding that RoR will require direct access to the tables, thus pushing rules and logic back into the application layer. When yet another developer addresses the database from ASP.NET, or .JSP, is this yet another application to have its own version of the rules and logic? When I need to implement some schema change, do I now have to worry about all the code scattered all over? With the stored procedure layer, I can preserve the interface and be free to do whatever I want with the schema.
I am perfectly happy to work with developers to provide them with a rich and robust stored procedure layer, written in an SQL that's probably better than what they will write. For the application, they can use whatever they like. I don't care, just so long as it's using the stored procedure layer as its interface.
Am I old-fashioned?
- - Herb
-
This is a specific example of the more general debate as to whether the DBMS should be a dumb storage mechanism or an intelligent guardian of data intergrity.
I'm of the 'intelligent guardian of data intergrity' school, myself. Using stored procs, and views, allows for enforcement of intergrity rules, data manipulation consistent with business rules across all applications, more timely and accurate impact analysis, and better requirements definition by both data modelers and developers. I've seen it done both ways, and the effort expended to clean up the data and re-work applications once two or more different applications treated centralized data differently was immense.
There are people of sharp intellect and good character who will argue the other side. They're wrong. 😉
And then again, I might be wrong ...
David Webb -
I agree with you both... one of the more import facts is that a lot of folks who write SQL code embedded in whatever app they're writing, have no real clue about obtimizing performance, security, etc, etc, etc... they're GUI programmers... not SQL Ninjas. And, it's impossible to find all of the embedded code that may be affecting a particular table, etc.
It doesn't really take any more time to write a sproc and pass parameters to it than it does to write embedded code. And things like Hibernate, while very good at handling single row or low row number transactions, also don't necessarily use reasonable methods. The spawining of this type of code should be carefully evaluated with the idea that, maybe, it should be converted to a stored proc.
The other bad part about embedded SQL, is there is NO chance of caching an execution plan.
Last, but not least, writing stored procs usually requires the DBA to at least look at the code... in the absense of any other code review, the DBA provides a final sanity check for performance, some form of documentation, and the safety of the data and the database.
I don't think that's "old fashioned" at all... if it is, we need more of it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I concur. The problem is that most books on programming, when they get to the section on database access, are woefully simplistic in how they accomplish it. They just want some rows inserted or returned for the sake of their programming example, so they end up teaching bad practices. Granted, they are teaching VB or C#, not T-SQL and SQL Server architecture, and the books are usually 1000+ pages anyway. I try to "sell" stored procs based on what Jeff pointed out, execution plan caching, performance hits of having to prepare each embedded SQL statement, plus knowing where the code that affects the database lives. I always end up having to compromise; some code is embedded in apps, some is in stored procs. The discussion usually travels through the, "you just want control!" accusation, to which I answer, no, I just want stability, reliability, maintainability.
There is no "i" in team, but idiot has two. -
Found another good reason not to use "parameterized scripts" as embedded SQL... see the following recent URL about the wrong execution plan being created...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=373799
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I also wear multiple hats, DBA, Database Designer and application developer, so I get to experience multiple points of view. Of course being a Jack of all Trades has the draw back of being master of none. I tend to lean towards the use of stored procedures as much as possible. I like the performance and security associated with SPs.
Question: I tend to stick with the "generic" or multi-use SP's but it seems to be difficult to come up with generic select/update SPs. The insert/deletes are not to bad as the insert you expect to get all the column values or NULLs and the delete can almost always use the PK of the table. But what about the Select/update statements? Do you create a separate/custom SP for every different Select/Update your developers decide they need to do? You could end up spending a lot of time just creating/changing SP's.
Really curious what others are doing in this area. I've been fortunate to be involved with mostly small team development (10 or less developers) and being the DB guy always had "code review" responsibility for all SQL so was confident I had a fairly good handle on things and only required SP's for the resource intensive stuff and anything that was likely to be used in multiple places in the application.
All thoughts, examples appreciated.
James.
-
Please see discussion at:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=49&messageid=375196
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR -
Hi,
http://jooto.com/blog/index.php/2005/11/01/the-myth-of-data-integrity/
Only read if you don't mind having nightmares
k.
-
No Herb you are not old-fashioned. You are right.
I've done it all the different ways, and by far the easiest to manage and maintain and maximize DB efficiency is to have ALL, 100%, of the business logic via stored procedures on the server. I now try to make that a rule for all my programming solutions, small and large, and I haven't run into a situation yet where that doesn't work out.
For example, I cringe when I see users who are linking to SQL tables via Microsoft Access and then using Access to do all their business logic, only because they knew access and are afraid of learning T-SQL.
Or worse when the SQL logic is embedded in precompiled code. OK, I did that when I was first learning how to program
but I quickly got my hand slapped for it, thank goodness.I don't know Ruby-on-Rails, but it's got to be the same paradigm.
With all of SQL's built-in security, efficiency, and management tools, it simply doesn't make sense to use an external tool to create business logic, other than that developer's refusal to learn to use SQL or fanatical dislike of Microsoft. (Unfortunately there are plenty of those out there, who refuse to use a Microsoft product for irrational "political" reasons.)
Slap their hands and tell them how it has to be. If they don't like it, they should find themselves a cardboard box and start loading the stuff from their desk into it.
[font="Verdana"]If technology is supposed to give us more freedom and empower us to pursue the more important things in life, why do so many people allow themselves to become enslaved by it? Always remember, the truly important people cannot be reached... except when they want to reach you.[/font]
-
All the good points have been made, so simply "Ditto".
And if all else fails, we have "CLR" BUT ONLY AS A LAST RESORT!
So... Not Old Fashioned, Right!
-
Thank you all for your interesting and thoughtful responses.
-
Oh, man! That guy is frightening! If that's where the industry is leading, I'm getting back into photography and letting my wife support me!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
It's the only way to be sure that the business rules will be enforced -- put it all in the database. Take a clue-by-four upside the head of anyone who wants it otherwise, maybe it'll daze 'em long enough that the issue will be settled before they recover.
Or was it cutting off their heads was the only way to be sure? Are there differences between hunting the undead and SQL Server?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Another argument for stored procs is from the perspective of deployment of the application. Depending on the type of application (e.g. a desktop application deployed to many clients), use of stored procedures can make deployment (and subsequent updates for bug fixes, etc.) easier. Much easier to fix a bug in a stored proc and deploy that one proc to the database that everyone shares than it is to rebuild your application (to fix your embedded SQL) and deploy it to multiple clients, especially when you have a large number of clients.
-
Im with the stored procedure side as well - especially as my systems grow into several different front end technologies, I like to centralise the business logic so its written once (and cut down on traffic)
If I do give any `raw access` to data, I don't generally give it to raw tables, ill create views (so you can restrict down what they can access) and give permissions accordingly!
That somewhat helps me make table changes while keeping views consistent, so changes to the db don't necessarily effect the front end.
Martin
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply