Revisit What You Know
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/3119.asp
-
I agree that it is all to easy to fall into the trap of using the same methods for everything. One reason I got into programming/DBA work was because of the opportunity to be continiually learning and finding better ways to solve every day problems.
I come to SQL Server Central every day because I am able to learn new ways from my peers. I am not in a position currently where I use SQL Server, but I am hoping to move the organization that direction and I use this site, and some others, to try to "keep up".
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 -
That all sounds pretty sensible to me. We face the same issues in software development. Take this as an example: multiple inheritance is bad. Is that really true? Sure you can get in a mess with it, and people due some dreadful things, but there are three occasions over the past 7 years of programming, mainly in Java and C#, where actually being able to use it would have been really useful. Obviously you can factor the code a different way to achieve the same end, and I've had to, but multiple inheritance would have been a much more elegant solution.
Or what about using multiple threads? Dangerous and error prone right? Best avoided? Well sure, you don't want to use them just for the sake of it, but sometimes multithreading is the best, and indeed the right, solution for a particular problem.
I think it's human nature to look for one-size-fits-all solutions because superficially it appears to simplify things in our often too busy lives, but I also think this might be a false economy, and that actually we'd do better to pause and reflect for a while on the best way to solve a problem.
Bart
-
As they say, 3 Hah-Hah.
I always tell stories about Windows authentication. I am not even mentioning the Domain Trust.
I love cursors.
I set up my servers a certain way that a new DBA described as "No Special Configuration" on purpose that the same new DBA would be comfortable around this server. Forget putting log or backup files on the system drive if you have only 2 physical drives - I am not comfortable with it, I would better ship tr. log backups to the network share as soon as they are completed (unless we have performance issues - in this case I will request more hardware)
I totally agree about naming conventions - these even changed from Visual Studio 6 to Visual Studio.NET
What about the best practice 10 years ago in Database Design to have a database per action: a database for user authentication, a database for state management, a database for user data, a database with views for user access... I have a couple of applications set up like this. I don't think it is recommended anymore.
Some things though would never change and be the best practice: don't leave your password on a sticky note on your monitor.
Regards,Yelena Varsha
-
Good article.
One time our DBA baggage really comes under scrutiny is when we change jobs and confront the accumulation of someone else's paradigms and best practices. It's good then to know why you believe what you believe, so you can justify change -- or accept change.
Lynn Hineman
-
Heh... I used to seriously question cursors… now, I LOVE cursors and RBAR While Loops, too! Even better than that, I LOVE code that looks set-based (no While loops) that actually has performance worse than cursors (usually some wonderful form of triangular join associated with a correlated sub-query). They're all really sweet deals and my friends that sell hardware get about 80% of their repeat business from folks that have performance issues and throw hardware at the problem. The Developer's (in-house and 3rd party) are loving it, too. They know the users will throw hardware at the problem so no need for the Developer's to actually think about writing performance enabled code. They can just crank out oodles of code in a highly productive manner without having to even think about performance. What a deal! And there's even built in obsolescence! As the data grows, so does the need for even more performance... so, about every two years, the customers order more new hardware to fix their lovely performance problems, all without having to actually touch any working code and without ever having to call the customer to see how they're doing! Sweet, huh? And, what the Developers really like is the fact that they no longer have to spend any time justifying cursors to the DBA even for the simplest of tasks… the hardware will take care of it!
The Systems DBA's are loving it, too! Long gone are the days when they have to review code for potential performance issues… they can just promote the code to production without even having to look at it. They know that it can all be handled with hardware! And, if a cursor is responsible for a deadlock, no problem!!! Just buy another server to run that process on!
Management likes hardware solutions a lot because, as everyone knows… hardware is a lot cheaper than good programmers! And, it makes justifying the floor-plan, air-conditioning, cooling, and power consumption budgets so much easier! In fact, Management just loves being able to tell their golfing buddies about the nifty set of 8, 16, and 32 processor boxes they just got… saved oodles by being able to get Developer's at a quarter of the cost because they don't actually have to understand RDBMS's anymore and they have a lot more time to write the GUI code that they're so good at. And, with things like Hibernate, they don't even need to be good at that anymore, either… it'll automatically write the performance challenged code for them! That means even the interviewing process becomes a lot easier and cheaper because you don't need to hire DBA's that know much, either! The savings just don't quit!
But, as much as I like cursors, you're all missing the holy grail of hardware justification… the very mention of it just sends hardware chills up and down my little data-troll spine in fevered anticipation… my hardware buddies are expecting hardware upgrades to triple or quadruple in just the next year or so because of it… AND, you can hire even cheaper programmers because the data just figures itself out... ya just gotta love XML for all the potential savings, don'cha?
--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) -
Funny how this works... everyone seems to say that performance isn't all that important... but, why do they buy the high performance hardware if performance is not important? And why do so many of the job postings for DBA's on this site have things like the following in the job requirements?
• Assist with code enhancements to achieve reliable server performance and ensure database security and data integrity measures are in place
Hmmmm... must be a type-o... they must've meant "Able to buy a pot-wad of hardware without getting writer's cramps on the PO's".
--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) -
Lynn, thats a great comment about changing jobs!
-
Jeff, not sure if you're disagreeing with the intent of the article or just enjoying some humor?
-
Jeff, your point about cursors is taken, but there are situations where they are, in reality, the best solution to the problem at hand. Like almost everything else in the SQL Server world, it depends... Performance and speed is critical, but it isn't the most important consideration. Logical correctness is the most important thing. In arriving at the correct answers cursors are a tool, one that is often abused and misused, but a valuable tool nonetheless when the circumstances dictate.
Andy,
A very timely article, I switched jobs last year and one of the first things I did was to revise my SQL Server Standards document. This is a document that I started at least 8 years ago and have carried forward through those years with many additions and revisions along the way.
One of the most surprising (to me) changes I made was to the formatting section. I, like most people have always used all caps for SQL key words and functions. The developers in my new job had abandoned that practice. Their argument was that keyword highlighting made that practice unnecessary. After discussing it, I agreed to try going with lowercase for sql key words. I expected to not like it and find that readability somehow suffered, but after 6 months of using it, I have yet to find an example where I wished we'd followed the old standard. I really don't miss hitting the shift key all the time...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I grew up with hungarian naming of variables and I still tend to do it!
One of the questions I like to pose for candidates goes something like this:
You've just taken a new job as DBA at a manufacturing company that has one mission critical SQL Server. It's running on hardware purchased two years ago when they converted from an old main frame application. Everyone is very happy with the conversion and have reported no performance problems of any kind. A quick run through of the server on your first day reveals that they are doing full backups once a day, they are using RAID5 with no hot spare, no stored procedures are being used and all the dynamic sql you see in Profiler seems to be cursor driven, and the application runs with the sysadmin permissions. The business wants to next upgrade it's accounting application and move it to SQL to complete the migration from the mainframe. Tell me what things you want to change, why, and what the priority would be.
I see a lot of people give me what I consider very bad answers, but I dont know that they stem from lack of flexibility, poor wording of the example, or just a total belief in best practices.
-
That's a really excellent question, I might steal it! It sets up a situation that lets you distinguish those who have practical experience from those who don't. My answer 5-6 years ago would have been to charge in where angles fear to tread. Today, I'd take a much more cautious approach, fixing only that which needs fixing, after careful and thourough evaluation.
I used to see that kind of approach as complacent, now I know better...
I do have to say that I hate hungarian notation though! Particularly when I see something like:
select intEmpId from tblEmployee
With very few exceptions, I stay away from object type identifiers in SQL.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Nope… you hit the nail on the head, Andy. People DO need to reevaluate what it is they're doing. One of your points hit a particularly sore spot with me (no fault of yours) and I was just having some fun with folks that still manage to justify writing performance challenged code so long as it works. My 3 basic rules are "Make it work, Make it fast, and Make it pretty... and it ain't done 'till it's pretty". Most folks quit at the first one (or less
) and it's a trend that seems to be increasing.So, on the more serious side (gotta try this one more time
), consider this folks…If performance weren't important, there wouldn't be so many posts asking for how to "tune" or "optimize" a query because the one they have right now takes 16-20 hours to run (for example) a batch job while making the heat-sink of 2 to 4 processors glow red-hot in the dark and chattering disk heads as if they OD'd on Red Bull. In the same breath, those folks also identify that the code has cursors, views of views, table variable functions, and all manner of While loops in it making RBAR on steroids. ("RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".) Or how about the ones that say it's taking 10 or 15 seconds to return one record to the GUI? Why is their code in such a deplorable condition? Because they took the easy/quick way out when they first wrote the code… and, it's their policy to do so to meet schedule! It has become their habit to allow performance to play second fiddle and then the scream bloody murder when they bust a string despite the pragmatism at the time they wrote the code.
What I'd really like to see people do is reevaluate the policy of justifying/allowing cursor and loop usage whether it's in batch jobs or GUI's. Yes, for crying out loud
, there are places where some form of a loop is absolutely necessary. But, they're not as frequent as people make them out to be! I've found that people are not only getting really lazy about finding a set based solution, but they've gotten horribly lazy about finding the correct set based solution for scalability and performance. When their really bad "set based" solution goes South for the Winter, they somehow use that cruddy excuse to justify cursors. I've highlighted the word "correct" because folks don't necessarily understand that just because something doesn't have a loop in it, doesn't mean that it's good set based code. A good example of really BAD set based code is when people use triangular joins to create the equivalent of a row number… no loop, no cursor, so it must be set based… Yep… it is, but the performance is hundreds or thousands of times worse than a cursor. The problem with that statement is, it sounds like I'm saying a cursor is justifiable in that case… patently, it is not… even if you're using SQL Server 6.5! But people will still take the cursor route because its easy or maybe they just can't think of a setbased solution at the time. 
And, even if a cursor is absolutely the only way to achieve a certain task, they don't check for performance and scalability! 
Yes… folks should indeed reevaluate what they're doing…
They should start asking things like is it really worth not looking for the correct set based solution and what's the true cost of the rework of performance challenged code in the face of scalability? Are they considering the cost of regression testing and the fact that some guy, gal, or team of people needs to study and map out the process to figure out what it actually does before typing a single keystroke to fix it? Is it really worth cranking out poor performing, scalability challenged code in 20 minutes to meet some ill-conceived schedule that was improperly bid in the first place? Or, is it worth spending 1 to 2 hours on it making sure that you'll never need to touch the code ever again just because some "tipping point" was reached? Yeah, yeah… here's comes more justifications about how I don't understand that the customers like low bids and bosses like jobs done quickly… Horse muffins! Take the time to explain it to the customers/bosses. Ask them how many times other peoples solutions required a Cray as soon as they got more than 10 or 20 thousand customers in their database. And then bid the schedule on the job correctly!Here's something else to ask customers/bosses… "What else do performance problems cause?"
How about crashes and reduced functionality to start with? What's the chances of a spurious deadlock crashing a 16 to 20 hour 4 processor run as compared to the chances of something that does the exact same thing taking only 20 minutes to run on a single processor? What's the cost of that crash? Which month end deadline will that cause folks to miss? Or, will they simply stay up for the next 24 hours trying to catch up (yeah, customers love that)? If they do manage to get a rerun done in time, will there be anytime left to ensure the crash didn't cause any other problems? And, who is your customer/boss going to blame when that crash happens? And, because the code takes so long to run, was any desired functionality left out of the code or "not selected" in the interface by the customer just so it would run in the same bloody shift or day?Seems like everyone cranks about how long it takes to write good, high performance, scalable code so let's examine that facet a bit... How will it ever be otherwise if you don't make it a practice to do so? How will you ever get good enough to write good set based code in a short period of time (sometimes, less than writing a cursor) when you look at problems and simply give up looking for the right solution just because it's easier to write a cursor? Writing good SQL is a lot like playing an instrument… it takes me about a minute to play chop-sticks on the piano because I'm not practiced at it… it takes a well practiced concert pianist the same amount of time to play the lovely, classic, work of art known as the Minute Waltz. Who do you think customers will pay to hear play the piano once they've heard us both play?
Jack Corbett (from above) very appropriately wrote… "I agree that it is all too easy to fall into the trap of using the same methods for everything" and I couldn't agree more. If you think that performance should play second fiddle to code working or meeting some poorly bid schedule, then I believe you've fallen into such a trap… they are equally important in the face of scalability. If you don't think scalability and performance are important, ask your customers… especially the ones with performance problems…
... then check their code for cursors and While loops... you'll find them.
--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) -
Heh... I'd have to say you've outlined an impossible scenario and you need to ask better questions.
Not possible to not have performance problems in such a scenario and, although no performance problems have been "reported", they probably have no idea considering the details in the rest of the question. Would I get the job? Probably not... real hard to offer a job to someone who is running like hell to get away --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) -
Case in point... I'm sure they thought it was pragmatic to do so, at the time...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=386825
... their batch job jumped from 16-20 hours to more than 70... and they only have a quarter million rows.
--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)
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply