March 28, 2008 at 3:23 pm
I'm re-training our mainframe programmers in SQL Server and database theory: they're taking a course in VB, and afterwards, (theoretically!) they'll be writing apps against my database servers. I sent the outline that I developed to an old DBA friend of mine soliciting his opinions, and he brought up the point of using stored procedures instead of accessing tables directly.
To be honest, it caught me by surprise. It's been so long since I worked with application developers that I'd forgotten this issue. So what I'm asking, and I'm not intending to start a flame war, is for references to good discussions on the pros and cons of each. I have some in mind, I'm just looking for additional references.
Thanks!
-----
[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]
March 28, 2008 at 4:41 pm
Forcing all data access through stored procedures is the industry best practice for database applications development because of:
1) Security
2) Separability of function
3) Performance
4) Manageability
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 29, 2008 at 5:07 pm
I'll second rbarry's post. Performance is not as big a deal now. The others are. Here are a couple of links to Andy Warren's Blog (It Depends) that are about Linq, but also mention reasons for using sp's:
http://blogs.sqlservercentral.com/andy_warren/archive/2008/02/05/what-s-wrong-with-linq-to-sql.aspx
http://blogs.sqlservercentral.com/andy_warren/archive/2008/02/22/linq-again.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 29, 2008 at 6:19 pm
If there is any change in the database, you just need to change the stored procedures and you don't need to touch the VB programs.
March 29, 2008 at 6:26 pm
Jeff Moden (3/29/2008)
As Jack did, I second the recommendation that Barry gave. But (Jack, don't kill me), anyone who thinks that performance is not as big an issue as it was before simply needs to take a look at all of the posts on this and other forums about people complaining about performance.
I can't kill you I don't know where you live...yet:P
Basically what I meant about performance not being as big a deal is that, particularly with parameterized queries, you are just as likely to hit a cached execution plan. As I think Jeff knows I sit firmly entrenched in the "use stored procedures" camp for all the reasons listed here by Jeff and others.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 29, 2008 at 6:28 pm
I tried to re-train mainframe programmers (COBOL) to do SQL and VB once, the hardest thing for them was getting the concept.
For COBOL program, the 'read' file (VSAM flat files) was reading one record at a time and they just could not get the concept of the 'SELECT' statement returned all the records at a time. They tried to write a loop to do the 'SELECT' statement to get all the rows!!
The traditional COBOL program was top to bottom flow. The VB program was object oriented. There was no start program and no end program, this drove them crazy.
my 2 cents.
March 29, 2008 at 6:52 pm
Sorry folks... I actually deleted my post (Jack caught a piece of it in his reply) because I was on more of a rant about performance than why to use stored procedures.
--Jeff Moden
March 29, 2008 at 9:45 pm
FWIW, I teach many programmer how to use SQL Server and I have learned that the most important thing is to impose the following two rules/commandments right from the start:
I. Thou Shalt Not Use Cursors.
II. If You Can not Do Something Without Cursors, Then You Aren't Trying Hard Enough.
And when I say that I "learned" this, I mean it. Even when I lay down this rule, there will always come a time when I will come back to them and find that they have written a dozen stored procedures with cursors because they found it easier than trying to figure out how to do the same thing with one or two simple joins.
Programmers like Loops the way that ducks like water. And if you don't literally force them out of their comfort zone, they will never learn how to fly.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 29, 2008 at 10:41 pm
Well said. I especially like the two rules because, if you substitute the word "RBAR" for "Cursor", I use them all the time in a slightly different manner...
I. Thou shalt not use RBAR.
II. When you think something can't be done without RBAR, see rule number I.
--Jeff Moden
March 31, 2008 at 8:00 am
Loner (3/29/2008)
I tried to re-train mainframe programmers (COBOL) to do SQL and VB once, the hardest thing for them was getting the concept.For COBOL program, the 'read' file (VSAM flat files) was reading one record at a time and they just could not get the concept of the 'SELECT' statement returned all the records at a time. They tried to write a loop to do the 'SELECT' statement to get all the rows!!
The traditional COBOL program was top to bottom flow. The VB program was object oriented. There was no start program and no end program, this drove them crazy.
my 2 cents.
I am constantly drilling in the "sets" not "records" concept, so hopefully it'll stick. They're learning VB right now and that should give them a clean break from "big loop" processing.
Cobol was one of the first languages that I was proficient in after Basic, so I have a feel for where they're coming from, even if I have written only one production Cobol program in my career (and that was over 15 years ago).
-----
[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]
March 31, 2008 at 8:06 am
rbarryyoung (3/29/2008)
FWIW, I teach many programmer how to use SQL Server and I have learned that the most important thing is to impose the following two rules/commandments right from the start:I. Thou Shalt Not Use Cursors.
II. If You Can not Do Something Without Cursors, Then You Aren't Trying Hard Enough.
...
Yeah, I'm staying well clear of cursors in my training. I'll give them the basics, but nothing solid. I wrote one cursor that worked, then I posted here and got a set-based solution that I just couldn't quite see. Since I'm reviewing all code before it goes live, I'll have final authority to make them re-do it.
I'm also drumming it into their heads that this web site is one of their best resources, along side BOL.
-----
[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]
March 31, 2008 at 8:08 am
Jeff Moden (3/29/2008)
Well said. I especially like the two rules because, if you substitute the word "RBAR" for "Cursor", I use them all the time in a slightly different manner...I. Thou shalt not use RBAR.
II. When you think something can't be done without RBAR, see rule number I.
Absolutely! Teaching avoidance of RBAR is going to be tricky. I'm not sure how I'll be teaching execution plans and traces, that's a couple of weeks away and I'm not quite at that section (of writing the training) yet.
-----
[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]
March 31, 2008 at 8:09 am
Wayne,
You don't mention which version of VB is being used, but if it is the latest bersion of VB.NET don't let them read anything about Linq until you get them to understand stored procedures and traditionally ADO.NET. If they learn Linq first they will probably want to stick with it and the default for Linq to SQL is to not use stored procedures.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 31, 2008 at 8:11 am
Jack Corbett (3/29/2008)
Basically what I meant about performance not being as big a deal is that, particularly with parameterized queries, you are just as likely to hit a cached execution plan. As I think Jeff knows I sit firmly entrenched in the "use stored procedures" camp for all the reasons listed here by Jeff and others.
I agree, performance isn't as big of a deal as it was back in 4.x/6.5 days. The system does a good job of finding the best path, though it does need a nudge every now and again. I find that if you do good enough design up front, along with good index selection, it takes care of the lot of the issues. Of course, making sure my people do a good job on joins is going to be a major effort.
-----
[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]
March 31, 2008 at 8:17 am
Jack Corbett (3/31/2008)
You don't mention which version of VB is being used, but if it is the latest bersion of VB.NET don't let them read anything about Linq until you get them to understand stored procedures and traditionally ADO.NET. If they learn Linq first they will probably want to stick with it and the default for Linq to SQL is to not use stored procedures.
We're training in VB2005 as materials for 2008 are not yet available, so I don't think that will be an issue quite yet. We're using Bill Vaughn's Hitchhiker 2005 book, I don't know when he'll have his 2008 version out.
We don't yet have anything better than the Express edition at the moment, we're hoping to get management to buy the 2008 edition and install the 2005 off of our DevNet subscription, but we haven't been able to find whether the 2008 license allows that. They have allowed it in the past on some products (most recently and visibly the Vista -> XP downgrade), but it varies.
I will definitely be keeping an eye out for Linq issues!
-----
[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]
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply