August 11, 2008 at 9:22 pm
Comments posted to this topic are about the item SQL Profanities
August 12, 2008 at 3:00 am
I think that there is a problem with trying to lay down hard and fast rules as to what is 'Best Practice' with Transact SQL. For some reason, we are always far too eager to act like Old Testament prophets when passing on our knowledge. It is a tendency that afflicts a lot of programmers but we Database Developers and DBAs seem to make a beeline for the hard and fast rule.
The real message, I believe, should be this. Your code must perform well, be reliable, and be easy to understand and maintain. I also think it is important to get something up and running quickly once the design is complete, and then refine, and re-factor as you increasingly understand the key points that determine the reliability and performance of the system.
I realise this is much more boring than sounding off on the evils of various 'SQL Profanities', but hell, it is the way to get the job done.
I don't care if you use cursors, Dynamic SQL or any other naughtiness. The advice I always give is:
* Design first, then code. The application's architecture, its overall design, is the largest contributing factor to its performance and reliability. Indexes, constraints, and views should all be planned up-front wherever possible.
* Have the right tools and techniques available to measure performance, and use them to understand how long the various routines and processes take to execute, under various loads and table-sizes, and why.
* Write code that is clear and is easy to maintain and understand. By far the greatest optimisations come through changing the algorithm, so the clearer the code, the easier it is to subsequently optimise.
* Never assume that an error event, such as a particular constraint violation, can't happen. For some reason, they always seem to happen, usually at midnight on a vital production system.
* Set performance goals as early as possible. You must decide what represents a 'good' performance: perhaps by measuring the speed of the previous version of the application, or of other similar applications. Make sure that clear, documented, performance objectives on all parts of the project are agreed 'upfront', so that you know when to start and stop optimising, and where the priorities are. Never micro-optimise. You should revise performance goals at each milestone
* Only optimise routines when necessary. Because detailed optimisation is time-consuming, it should be carefully targeted where it will have the most effect. The worst thing is to plough through DML indiscriminately, changing it to optimise its execution. It takes time, usually makes the code more difficult to understand and maintain, and usually has very little effect.
* Avoid optimising too early. Detailed optimisation of SQL code should not be done until the best algorithm is in place and checked for throughput and scalability.
* Do not delay optimisation too far. The most performance-critical code is often that which is referenced from the most other places in the application, such as an inline function: if the fix requires a large change to the way things operate, you can end up having to rewrite or refactor a huge portion of your application!
* Assume that poor performance is caused by human error rather than SQL Server.
* Employ an iterative routine of measuring, investigating, refining/correcting from the beginning to the end of the product cycle
Best wishes,
Phil Factor
August 12, 2008 at 4:04 am
Amen.
But I'd like to add GOTO to the list of Mr. Simmons. SQL Server 2000 only...
August 12, 2008 at 5:56 am
Well put!
August 12, 2008 at 6:20 am
You forgot to add SQL-CLR to your list of profanities. Not that I consider it to be so. But, I have a developer background. So, my biases are to be questioned anyway. 😛
August 12, 2008 at 6:24 am
A couple of thoughts sprung to my mind when reading Phil Factors reply to this editorial.
First was a design philosphy I always try to follow, which is to think re-usable. If you are desigining a piece of new functionality, ask yourself if you can forsee it being used in other parts of the system. Is it part of the business logic of your organisation that could be captured in a stored function or procedure. Much easier to change it then when the organisation changes it's business rules (these things occaisionally happen, like once a month 🙂 ).
Following on from this, think modular, if you are going to encapsulate some business logic in a function, make it as self-contained as is logically and physically possible given performance constraints. Supply it with the key values it needs to perform the process, and let it acquire any other the associated data it needs internally. This way your code presents a consistent 'surface' to the rest of your business system. It is far easier to change the internal operation of your functions and procedures when they are not over-reliant on the nature of the information passed to them. Otherwise you find yourself playing a sort of SQL Jenga, move one block, everthing else comes crashing down.
And on the subjects of the SQL Profanities, it is not the tools that are the cause of profanities, usually it is the workmanship that is done with them...
David
If it ain't broke, don't fix it...
August 12, 2008 at 6:35 am
You left out triggers...
Good editorial. I agree with it. I also take, a very slight, exception to it. The problems that people who post here every day see are not that someone has a simple little cursor that does the job, quietly, efficiently or a table valued multi-statement function that really only returns about 10 rows so that it's generating a good execution plan that works well within the query, or a view that simply acts as a mask on table structures or builds out complex joins in a way that makes the rest of the code more maintainable. If this was what was primarily out there, then the good ole' saw "It Depends" would be all we'd need to define these issues.
Instead we get cursors wrapped in cursors wrapped in cursors calling 15 million rows and why, oh why, does it run slow. We have multi-statement functions that return millions of rows and these frequently get called by other multi-statement functions also calling millions of rows and that "should be fine otherwise why does SQL Server let you do it." Etc.
The reason so many people rail against the evils of these various tools isn't because there's anything wrong with the tools, but because they're so horribly misused so frequently. It just becomes easier to say NEVER use X rather than outline all the distinct times it's OK. So it really comes down to a bit laziness on the parts of posters who go that route, but that laziness is predicated on experience, not simply laziness.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 12, 2008 at 7:01 am
Andy Warren has a saying I love. "Always use the defaults until you have a reason, and know better, why something else should be done."
I think that applies here. Those items listed, along with GOTO and triggers shouldn't be used in code. You should just no reach for these tools.
Until you have a good reason and you understand the implication of doing it that way.
I avoid triggers if I can and look for better ways to handle things, including adding extra code to many sprocs. However I do find places where it doesn't make sense to do that, or it doesn't work and I'll use a trigger. Just sparingly and when I have a reason to do so.
August 12, 2008 at 7:12 am
For me, the use of "profane" features comes down to a question of risk/benefit analysis. Will the benefits of using a feature overcome the potential risks? The answer to that question will depend upon many factors (ex: is it a production environment? What's the required timeframe? What level of security is required?)
My attitude has definitely changed since I started my career twelve years ago. Initially, my mindset was to pursue theoretically perfect solutions. It took me a few years to grasp the reality that my role, ultimately, is not to solve technical issues, but to add value to the business.
I think it's important we strive to gain our own convictions to use as basis for making decisions, not just because someone else said it. Even amongst the gurus in the field, there's plenty of differences of opinion.
August 12, 2008 at 7:14 am
True, Steve. I once didn't even realise TSQL had a Goto...but then the first time I ever came across a goto and a cursor in the same sp was in the source of a system stored procedure!
Best wishes,
Phil Factor
August 12, 2008 at 7:23 am
I'm confused how are these opposed;-
"...not to solve technical issues, but to add value to the business."
In my job I solve technical issues that add value to the business. 😉
(thats what I get paid for anyways :D)
Tool abuse is what its all about - like an old friend of mine said he always "pitied the hammer stranglers" - he used to teach woodwork/metalwork to kids. 😀
Hiding under a desk from SSIS Implemenation Work :crazy:
August 12, 2008 at 7:33 am
I have to agree with the article, with Phil, and with Grant, all at the same time. (My sig pretty clearly sums up why I say that.)
I have run into threads here where the ideal solution was a cursor, but the person posting it was scared to use one because they'd been told "never, never, ever use a cursor". It's happened twice.
I have run into threads here where the solution was to remove a cursor. I can't count how many times, because BigInt just isn't quite enough for that. (Okay, I'm exagerating. A little. Maybe.)
The rest of these are pretty much the same.
I've used all of these, and the only one I think I'd say, "Never" on is "goto", which isn't even mentioned in the article (but it has already come up in the discussion). All the rest, I've found valid uses for, as exceptions in extraordinary circumstances. I just can't think of a good use for "goto", that If/Else, Try/Catch or Break or some such wouldn't be better. Maybe one day I will, but till then, I avoid it like the plague. Makes it too difficult to debug/refactor.
(After reading this editorial, I'm going to spend all day with George Carlin in my mind, repeating the "seven words you can't say in IT Depts"...)
- 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 7:55 am
What's wrong with views?
BTW, I'm new to the SQL Server world and EVERY MORNING I sit at my desk for 20 mins and read the latest issue in my inbox. I LOVE it and I'm learning SO MUCH!
Ryan
August 12, 2008 at 8:05 am
Good editorial on an important topic.
To me, cursors seem to be the most thoroughly-reviled feature in SQL Server. It's as though people are trying to believe that the assembly and C++ code (oh my! it's not all managed code) underlying the SQL Server engine is ... set oriented?
The first person I ever met who worked on the SQL team had the following job description: optimizing server-side cursors. How ironic.
Anyway, a lot of times when a pill-swallowing newbie, or a really excellent veteran, makes the statement that "cursors are always bad, always perform worse than well-chosen set-based logic, and should always be considered for elimination," I love asking them this question. "If that is the case, then why do so many of the fastest benchmarks posted at tpc.org use lots of cursors?" Their response is usually the very articulate, "um ... really?"
I also love telling people this statement: "there is always a cursor." It makes some people feel violated to know this, but hey, eventually, the truth will set you free, not myths. There is always a cursor defined on the database engine to handle your data retrieval and other DML. So when I write a background job using a cursor to process 5 million rows in the background, throttling the throughput so that we don't overwhelm our somewhat fragile replication infrastructure, I am often reviled. However, people who know me and have worked with me suspect that there is a method to my madness, so of course they revile me politely. I love telling them, as a loving retort, that when they use while loops to emulate cursors, they may be invoking a separate server-side cursor for each fetch (oh forgive me! I mean SELECT ... or is it the same thing?) at the top of each loop iteration. So while I know I am working with one server-side cursor, they may be working with millions of server-side cursors. The irony is deep here, since their goal, of course, is to write code that minimizes cursor usage.
Please don't read any animosity into this post. I just think this is a really amusing topic that we as a community do not understand very well at all. But, as Phil points out, there are plenty of prophets crying out in the wilderness, railing against the evils of cursors. Moreover, we have plenty of use cases where an explicitly-declared cursor is obviously a ridiculous idea. However, I do think there is a real group-think mentality going on that prevents earnest technical discussion of what is a more complex topic than we, as a community, care to admit. So if anything is frustrating to me about the discussion of cursors, or several other of these SQL Profanities, it is that trying to discuss them online, even in excellent communities like this one, too often turns into a flurry of bumper stickers: "Cursors are bad!" "Never use dynamic SQL!" "Triggers are slow!"
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.
Cheers,
Chris
August 12, 2008 at 8:12 am
@G-Squared:
The classic case for using GOTO in SQL 2000 (and earlier) was if you wanted to write one error handling section in a proc, you could put it down at the bottom, and then have a lot of code checks that basically amounted to "if there's a problem GOTO errorHandler." It wasn't the only way to do it (you could have "if there's a problem CALL my_errorHandler_sp and then RETURN") but sometimes procedures needed complicated, one-off error handling, and in that case, GOTO was not a bad solution, at least in my book.
I haven't come across any use case for this in SQL 2005 (or later) yet, since TRY/CATCH has been added.
I'm answering this in the spirit of it being a trivia question. So I don't want people to reply with better ways to handle errors. Really, though, it might be interesting for people to reply with the best use of GOTO they have seen in T-SQL. That could be fun.
Cheers,
Chris
Viewing 15 posts - 1 through 15 (of 65 total)
You must be logged in to reply to this topic. Login to reply