August 12, 2008 at 8:16 am
In defense of GOTO.
While not suitable to be published in production, it's a handy little statement to have when working with piecemeal assembly of large blocks of code.
August 12, 2008 at 8:22 am
Chris: Yes, SQL 2000 needed Goto for exactly that. You're right.
Tom: I haven't found a need for that. I can see where it might have a use, but I generally write my scripts on the assumption that, one day, I may have to turn them into procs, so I try not to use features that I would want to avoid in a proc. Also, since my main objection to goto is that it makes debugging/refactoring more difficult, I would avoid it in any script that has any chance of ever being used again. Again, that's me. That's not a law handed down from on high, or whatever. It's just the habit I maintain. Go with what's comfortable for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 12, 2008 at 8:30 am
chrisleonard (8/12/2008)
... Alas, I am an old man and I tire easily of such bumper-sticker exchanges. After 20 years dedicated to nothing but database work, and having been designated a SQL Server SME by Microsoft themselves, the only blanket advice I would give in this context is as follows:When choosing what programming features to use to solve a problem, there are no valid blanket statements except the statement that there are no valid blanket statements.
My bumper sticker can beat up your bumper sticker! 😀
There is a Clarke's law: "When a distinguished but elderly scientist says that something is possible, he's very probably right. When a distinguished but elderly scientist says that something is not possible, he's very probably wrong." I do love statements that fall in the same flavor as Catch-22.
I have yet to have a need to place cursors in production code, though I do use them occasionally for one-off or management purposes. I also don't do While loops, and I've never seen a need for Goto. Heck, I usually don't remember they're there!
My only quibble with the editorial is the concept of public peer review. I can see the use, but it just doesn't seem practical at the project level. I'm finishing up a file transfer process that's a five step scheduled job with SFTP, batch files, two DTS packages, and copying data between network shares. It also sends emails, and both the email and batch files are built by DTS. There is no way I could post something like that for public review without writing a novel, not to mention having to go through and obfuscate site-specific information for security purposes.
We buy almost all of our apps from vendors, so I can't do anything directly with their code. Everything I do from a development view is to add functionality that interfaces with or reports from the canned systems.
Write the best you can. If it performs well and the execution plan is reasonable and the results are correct, that may be the best you can do. If it doesn't perform well, there's a site called sqlservercentral.com where you can post your code along with table structures and insert statements to build out a dummy load where I've heard there are a few pretty helpful people who can give you a hand.
-----
[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]
August 12, 2008 at 8:30 am
ryan.leuty (8/12/2008)
What's wrong with views?
They have a tendency to be badly misused, much like cursors, UDFs, et al.
I've seen lots of views that included six or seven tables, dozens of columns, and the view is referenced in a proc that needs two of the columns, one each from two tables that have a direct FK relationship.
That kind of thing often kills performance, because it messes up execution plans horribly.
As with all of these, it's not the uses that are bad, it's the misuses, and the fact that misuses outnumber uses by a huge margin.
A view that defines a complex, multi-table join that's used in many procs, and thus simplifies the procs, can be very valuable. Index views of common aggregates can be wonderful. Views that are used by lazy database devs who save themselves the thirty seconds it takes to write a join or two, at the cost of killing the performance, those are bad.
(Just thought of a new sig idea: "Code doesn't kill performance. Bad developers kill performance." The NRA reference might be good enough to run with. 🙂 )
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 12, 2008 at 8:35 am
GSquared - You are right about not leaving loose ends. I was referring to scripts that were on their way to being procs. I use the GOTOs to skip already tested parts, or not yet ready for prime-time pieces. But along with various print and select statements that I may be using, they have to come out before release to production.
August 12, 2008 at 8:43 am
Thanks for that bit of feedback. I'm learning 'best practices' as I go along. I've inherited an environment setup by the previous guy and things were done so that they worked at the time. That kinda thing drives me nuts.
Somewhat on topic...what do you think of a scenario like this:
*ASP.NET website that pulls from SQL Server
*ASP.NET primarily calls sprocs when more than a simple SELECT is needed
*Views always used to handle multiple table joins where only a few columns are needed
Is this a 'good' or 'bad' way to go about things?
August 12, 2008 at 8:47 am
Ryan
What is the usage scale for the website? Number of concurrent users, etc.
How mission critical is it?
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
August 12, 2008 at 8:53 am
This is a highly important service that has two peak seasons. It displays property tax information for the county. A similar web app also displays public records. Both are mission critical functions.
During peak season for property taxes, there may be ~30 concurrent users. I've not seen more than that at one time.
By taking the complex SELECT with joins out of the ASP code and moving it to a sproc on SQL Server, I brought performance from ~35 seconds to 15. Adding an index to the table brought response to ~2 seconds 😀
The property tax table contains nearly 9 million records and grows by hundreds of thousands when payments are being made.
August 12, 2008 at 9:07 am
why not add in diagrams and foreign key constraints? surely there's a perf penalty to consider when every fk is constantly yet needlessly checked by the dbms when the CUD (who cares about R) is done by stored procs and the db is secure against external changes.
reason I ponder this is my background is large mainframe that had no ref integrity capabilities and yet we managed to build, maintain and audit, massive and extermely high perf databases - probably most of your bank accounts, govt pension plans, and military inventory dbs.
cuz isn't that what dba's are supposed to do - steward the db's instead of letting the dbms do it for them?
jes wonderin ..
August 12, 2008 at 9:14 am
I don't know:hehe:
I'm a web programmer and I'm doing all I can to get up to speed on DBA stuff. What IS the correct architecture to use?
Is it better [faster, more reliable, more secure] to send a complex statement to SQL via the web app, or use a sproc?
Should a sproc handle the joins of 6 or 7 tables, or should a view be used if I only need 1 or 2 columns from each table?
These are the questions that burn in my mind...and I'm totally hijacking an editorial thread...
August 12, 2008 at 9:22 am
Wow. This obtuse defense of cursors and RBAR has more hand-waving than a Michael Jackson concert. Practically every other post here has statements to the the effect that "sometimes cursors are the best solution", starting with the original article itself, and yet none of these claims has been backed up with an example, or a pointer of a cursor-based SQL procedure that is the best solution for SQL Server in its current release.
Not one.
Hmm, makes me think of one of my own maxims:
When everyone says that something is true, but no one can provide an example, then it probably isn't true.
And let me be clear about what I mean by cursors being bad. Technically, anything in SQL that serializes a data stream and can keep positional context is a cursor. However, when we say "cursors are bad" we do not mean client-side cursors, internal cursors, implicit cursors, etc. We mean explicit Transact-SQL server-side cursors. The kind that people explicitly write in procedures all the time (or try to hide in another stored procedure) and that use the CURSOR datatype.
Since the advent of the new features in SQL Server 2005 (particularly Varchar(MAX) and CTE's), I have not seen one instance where a cursor was the best solution for a problem. The best that can be said for it is that there are some (very few) cases where a cursor was no worse than some other solutions. But real-world problems addressing situations that reasonably belong in the database-tier where cursors are the clear best solution? Not one.
[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]
August 12, 2008 at 9:31 am
ryan.leuty (8/12/2008)
I don't know:hehe:......
Is it better [faster, more reliable, more secure] to send a complex statement to SQL via the web app, or use a sproc?
Should a sproc handle the joins of 6 or 7 tables, or should a view be used if I only need 1 or 2 columns from each table?
...
It is more manageable, more reliable and more secure to use stored procs rather than SQL from the web app. (in my opinion)
view vs stored proc....you still need to wrap the view up in a stored proc in order use dynamic selection criteria.
Hiding under a desk from SSIS Implemenation Work :crazy:
August 12, 2008 at 9:31 am
gserdijn (8/12/2008)
But I'd like to add GOTO to the list of Mr. Simmons. SQL Server 2000 only...
Whoa there! SQL would not be Turing complete without GOTO - don't be so quick to toss it!
But why the heck are views in the list of no-no's? Forget the hand wringing about cursors; views are wonderful things especially when you have to present base data differently on the DB side to make something like Business Objects run well.
August 12, 2008 at 9:34 am
magarity kerns (8/12/2008)
gserdijn (8/12/2008)
But I'd like to add GOTO to the list of Mr. Simmons. SQL Server 2000 only...Whoa there! SQL would not be Turing complete without GOTO - don't be so quick to toss it!
This is NOT true. Both iteration and recursion also provide Turing-completeness.
[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]
August 12, 2008 at 9:34 am
hey Ryan I know what u mean - stepping into dba shoes seems often like moving into the Dark Arts or something - tons of sage knowledge from all directions by persons smarter that me that totally defeat any attempt I attempt to move in any direction except lost. Now toss into that the tons of books these sages have penned in their valiant efforts to confuse me further (and possibly also to generate generous income) .. well daunting to say the least. Good luck, don't fix it if it ain't broke, and set yourself up a bunch of test databases and experiment like crazy on weekends and when the sages are otherwise asleep .. 🙂
Viewing 15 posts - 16 through 30 (of 65 total)
You must be logged in to reply to this topic. Login to reply