Am I Old-Fashioned?
-
Well - being a "Jack of all trades" for a long time as well - I'd say that by far and large, NOT programming your data access to use stored procs is typically very sloppy. ASP.NET will create the stored procs for you should you ask it to, so your developer wanting to keep his data calls in his app is just not wanting to play well with others
.However, to be fair:
- stored procs are no more the holy grail than anything else. They're tools just like everything else, and can be tortured, mangled, etc... just as badly as anything else. I spent two years rewriting SP's that used to return HTML markup (??>:?? don't ask....). That makes no more sense than doing nothing but embedded SQL in your app code.
- data integrity CAN happen in any code. you build it - it will run....
- until recently - SQL had no decent source control for its elements. Like it or not - stored procedures are code, and should be versioned. The fact that we had to wait until 2K5 to get built-in versioning is a crime. So when apps need to rely on getting data, and you aren't sure someone else isn't going to change something on the SQL server and send your app crashing, you might rely on other tactics. Like Chris Rock would say - "I'm not saying it's right, but I understand..."
- interestingly enough - one of the main features for these "direct access" mechanisms in Ruby and ASP.NET are to create data-bound forms, meaning make them much more directly accountable for implementing the validations, etc... required for data integrity.
Finally - having 4000 stored procedures in a single DB might not be the best way to go either. Under most cases - you're absolutely right: it's just that there are reasons to deviate from every scenario. Just like writing TSQL to do something not set-based doesn't make much sense (that's what CLR is for)....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I have to say though I do find it takes longer and the tools arent nearly as good (even in the newer versions e.g. sql 2005) to craft stored procedures than writing equivalent code not in tsql.
(And i dont include having to mess around with crl integration as being nice and easy
)martin
-
You still mean writing in embedded SQL or what?
--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)
Intro to Tally Tables and Functions -
RoR can work against Stored Procedures (may take som checking on Google but I found several comments on). Just give yor justification as to why they must use Stored Procedures and tell them as long as they can do it they can access otherwise they need to consider another programming language.
Also, get your corporation to adopt goverance over what languages can be used and secuirty requirements for databases. Set standards and get the corp to be dilligent by making them aware that this will improve quality and performance in the work done.
However, if the corp says the developer they can do this just put your concerns in a big email to your manager and save yourself a copy. This way if they come back to you about issues you can say you voiced your concerns over those very issues.
Note for extreme cases: be carefull who you voice to thou outside of you manager if you are in a very political environment, these thing can bite you. Plus be ready to go somewhere else if you feel yo may have an otherwise hostile environment. And of course even if you voiced your concerns be prepared to show what is going on with performance if a problem occurrs to show you are not trying to sabotage the results which some developers (who know little about what they are doing, especially with new methods they are playing with) may try to say as a way of shifting blame from their lack of experience.
-
James,
I have a stored procedure that I wrote that creates a "generic" CRUD stored procedure for the table that is passed in as the parameter for it. It works great. The way it is written, it will allow any number of colums to be updated. And, in the insert portion, if a column with a default is not passed in, it provides the default value. All parameters are optional except the "mode" of the procedure. The mode tells it what to do.
The other thing about it is that you can add additional modes to it based on what you want to do with the table. For example, get all the phone numbers for a specific name id.
It could be improved by letting it write the "reads" for the foreign keys, like the example above. But, I have not got around to that yet.
In reference to the original poster, is there any way other than stored procedures and views? 🙂
-
Martin,
I'd be interested in knowing exactly how or why it's harder to write stored procedures. In most cases you take your T-SQL, move the variables to parameters and wrap the code in a CREATE PROCEDURE and RETURN. Error checking has to be done either way.
I'm definitely in the stored procedure camp, especially as it tends to insulate other client code from schema changes, which invariably occur.
I'm not sure I think all business logic should be in the db. I do think that all data access and integrity code should be in the db, but if there's some logic that says when A occurs that B should happen, I might move this to the middle layer. Now the middle layer might be split somewhat among the DB and client/app server, but it's a decision based on the situation.
If I need to update inventory or notify someone, I could do it in SQL Server (update a table, set a flag, send a message to a Q), or if I need to contact some other service, like MQSeries, I might do this in the client wide or app server, not the db. It's not needed in the DB and I like to conserve those resources for things that aren't dealing with data integrity or access.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
I'm not going to argue with you guys about whether or not to use stored procedures, but I will try to address Herb's question.
As for whether or not you're old fashioned, the current popular way of developing applications using java and Ruby on Rails (a very large percentage of development shops) is to use an Object Relational Mapping (ORM) layer such as Hibernate/EJB3 or ActiveRecord and to generally put all business logic in the application and minimize the use of stored procedures. (I'm not really familiar with Microsoft technologies, but I think the ORM approach is not as popular in that space.)
If that means you're old fashioned, then yes, I guess you are. It's not necessarily a bad thing
There are many very compelling and valid reasons to use the ORM approach. It is a widely-used and mature approach. Many large companies (eBay, for example) that manage huge volumes of data use this architecture successfully.
Software architecture is very complex, and requires a balance of different concerns. Try to keep an open mind about the use of stored procedures. It's not always the best way.
-
I personally find sp's take longer to develop, some things are far trickier/long winded to code compaired to in external alternatives (isnt that one reason why they introduced the CLR to help handle the stuff tsql isnt good at) and theres lots of things in e.g. enterprise manager that arent nearly as friendly as other development environments. Its definatly getting better though. But thats just my opinion
I think you can probably look at SP's a little bit like the ?good old days? of coding in assembly language v.s. something written in a compiler of the times - assuming the assembly language version was well written it would generally be faster, smaller, take less resources than the compiled version of code, but take longer to develop and harder to find the bugs.
But i dont want to jump into a sp's vs other things argument here
I do use sp's where i can, its my preferred solution. But sometimes I don't - I might set up a restricted view and process externally accordingly.
martin
-
Here is an interesting article on Stoed Procedures in RoR
http://wiki.rubyonrails.org/rails/pages/StoredProcedures
Which leads to
http://web.archive.org/web/20060418215514/http://www.loudthinking.com/arc/000516.html
which sets the tone for no SPs and whyDavid really did want. Basically all I can find is the author more or less said I don't want my DB telling me how to handle something, I get to decide that. Just lik some folks may say SPs are the only way. Me, I find there is a middle groun more than anything else personally so I can take the good from both worlds.
Woul not say you are old fashioned or they are fresh in concept but that just like when people say Oracle beats SQL hands down or mySQL is the way to go it is uaually because of some very specific bias and that people tend to set there ways. Consider how many mainframe programmers are being outsourced of a job because they didn't learn any other skillset as related to their job.
-
Heh... yeah... just remember, there are TWO worlds in SQL... the external world of GUI's and the internal world of batch processing. The idea of using ORM and Hibernate and all manner of other things to help you build GUI's quickly and effeciently is based on RBAR, as it probably should be. Use the same techniques for batch processing and you'll have a hell of a mess on your hands.
In this "instant" world that we live in of getting an email saying "Thank you for your order" before you can click the email button on your desktop and online API's with SLA's of 15 seconds or less, there is less and less need for certain nightly runs. Just don't use the GUI techniques on batch processes because the runs will take forever.
--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)
Intro to Tally Tables and Functions -
The ultimate argument for using stored procedures is "re-use", which is something developers understand (at least in theory).
In my experience, it is not at all unusual for the database to be accessed by multiple different user bases. A significant portion will use the application itself, the rest will use the tool that best serves their needs (e.g. an analyst might use Access & Excel to pull and then analyze data). Data access via stored procedure or view can/will provide consistent results for everyone using the database whereas business rules enforced in the middle or application tier usually ends in tears as everyone fights over why their "numbers" are the right ones.
When all of the users access/manipulate data within the database through the same methods (e.g. stored procedures, constraints, views, etc.) you can at least be sure that everyone is starting from the same point. Put the business rules into an application or library not available to all users and it starts to get ugly.
Joe
-
OH, I don't know. Where I'm working right now, I'm basically a "sql developer". I know enough in general about some of the DBA's sql oversight roles, as well as the basics of rdbms structures for a variety of systems, that I feel just a leetle bit enlightened over things than the .Net developers who are rather ruffled having to write a query with a join in it.
But long ago I really decided that I didn't want to know all the various permutations of backing up and restoring databases, tape rotation strategies, etc.
As such, me and my co-worker are faced with the lack of a coherent, abstract business logic pattern over the financial data stored in a rather organic and crufty SQL Server database for our company. There are other groups who use the data too: one group writes all sorts of datamart/datawarehouse models (pulling data from our databases), another group uses the raw financial data for reporting to the Mothership (which of course uses Oracle Financials, so there is a bunch of ETLing to make that work...), and yet another group uses it for investor reporting purposes.
One of the difficult parts is that we use an in-house loan servicing application, which generates most of the financial data relevant to our company and the Mothership. It is NOT backed by a RDBMS; it has been developed over time in IMS Basic. So at least the daily ETL process is relatively straight forward. But because it's not an integrated loan servicing platform, it's a pretty big impedence mismatch as well.
There has been no overriding uber-architecture driving the whole thing over the last 10-15 years. All of the people who have put together what I now support have left the company. Now, we're much bigger than when they were around, more corporate, and consequently, much more pigeon-holed in our job titles, it's a PITA, and relatively amazing that it hasn't all fallen down to the ground.
On my side, we have loads of fun dealing with several now mission-critical Access applications that interface with the database. Oh, yes, they do lots of stuff with stored procedures in SQL Server. I doubleplusgood love our system so much! There are several hundred stored procedures used by everything - DTS and sqljobs that do all sorts of ETL and batch processing. Some of the DTS packages kick off stored procedures. Some of them have business logic encased in VBScript tasks, workflows, etc. Some of the sqljobs call stored procs, but most call off DTS packages (which call stored procs, other DTS packages, etc.). It's a f***ing piece of spaghetti batch processing if there ever was one.
Also, because it's never been architected, and no one ever took a "hey, let's take a couple of months and review some of what we've done and consolidate things", it's rather ponderous to deal with.
It is little solace that I can extract all the procs to .prc files, or write an Access (or external VBScript) application to grok the relevant info from the DTS packages and sqljobs.
At the very least, the Access applications are quite a bit easier to grok. For one, they're generally focused. OK, the various functional parts are pretty focused. So while they're business-essential applications, the various parts of them don't try to do TOO much, they're still manageable.
Oh, I forgot to mention that several parts of the datawarehouse/datamart datasets, investor reporting datasets, and JDEdwards' manipulations all feed back into the systems I report. Feedback loops can bite hard sometimes!
I suppose I don't know what I'm really saying, but while the DBA might be a good arbitrator over what is a good proc or query, how to spot hotspots and provide some insight to optimize things, they're probably about as useful at architecting the overall environment and application space as any other developer.
An intermediate business logic layer on top of the database layer makes perfect sense. But people have been talking about doing it for quite some time (no, not months, but at least a decade). But since no one can agree on a common presentation layer, it's not going to happen. There will always be a stealth desktop or server application that will manipulate the data directly instead of going through the business logic layer. Even in a .Net shop (or J2EE shop, Rails shop, etc.), there will be the Must-have 3rd-party application that is written using the other, reporting applications that can only access the data directly, etc.
Unless you're in a small enough company where you can control these very external things, it just ain't going to happen.
The cool thing for me, I suppose, is that there is always going to be Excel spreadsheets, Access databases, 3rd-party apps, etc. that either need to be supported, or the Big Iron application will need someone to develop those stealth apps to provide needed functionality that isn't justified by the development effort required to make it happen in the Preferred Domain (i.e., writing custom reporting and ETL apps outside of SAP ABAPs, for example...).
-
Wayne West scribed: "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."
Yeah, except for the business rules and logic that are stored in the Accounting application (not necessarily the database, but the application built on top of the application, ala JD Edwards), or stored in the eCommerce system, or stored in the loan servicing application, (even *if* it's all in SAP...), or stored in Bob's head, who left the company 6 months ago, or used to tell one thing to the Mothership company but tell another thing to the IRS, loan servicing rating agencies, shareholders, investors, etc...
I wish I lived in your utopia right now.
May I ask if you've ever worked for a company large enough to have various subordinate business entities that also do things their own way on their own systems of choice (like, in a Fortune 500 company)?
-
Antares randomly pecked: "RoR can work against Stored Procedures (may take som checking on Google but I found several comments on). Just give yor justification as to why they must use Stored Procedures and tell them as long as they can do it they can access otherwise they need to consider another programming language."
...but the SqlserverAdapter.rb can't use stored procs (I'm only at RoR 1.23, so maybe something changed...), because the ADO.rb part that it relies on DBI.rb doesn't support the concept of it. Which is too bad, because SQL Server procs can return recordsets, unlike the hoop-jumping required on Oracle to do sort of the same thing.
You know and I know that ADO (OleDB) supports stored procs, but...
A big part of it is that ADO.rb uses ADO about as minimally as one can - processing SQL strings. It doesn't really deal with Recordsets or its methods. It definitely doesn't deal with Parameter objects.
I've looked into trying to make a pure ADO-based sqlserver.rb, but I can't see a way to make the adapter deal with a large number of webapp sessions needing their own Recordset objects, potentially on the same base table/view/query. Yes, I know that ADO does some of this seemlessly (and quite invisibly to the developer) within ASP, but...
-
Steve Jones wrote: "In most cases you take your T-SQL, move the variables to parameters and wrap the code in a CREATE PROCEDURE and RETURN. Error checking has to be done either way.
"I'm definitely in the stored procedure camp, especially as it tends to insulate other client code from schema changes, which invariably occur."
Which is all fine and dandy, until someone before you decided that the best way to push functionality around was NOT to encapsulate it within its own proc or view, but to take the chunk of SQL and paste it everywhere it's needed.
Good luck finding it all and refactoring it!
Of course, this is the source of some of my current woes. Maintenance programming sucks.
There's "data integrity" at the field level, and then there's data integrity at all sorts of higher levels, information integrity, or even "meat space integrity" (i.e., documented procedures used by people that really aren't reflected in any computer-based layer. It's great when they are of the oral tradition type...). Too many people on this list seem to confuse the lowest layer of "data integrity" as applying to the whole problem domain spaces or applicable to heterogeneous systems (i.e., integrating a specialized application-specific system into a completely different financials system, for example, or doing any customizations in SAP...heheh).
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply