November 9, 2009 at 8:21 pm
Comments posted to this topic are about the item Is Unit Testing Important?
November 9, 2009 at 8:52 pm
[font="Arial Black"]"Make it work, make it fast, make it pretty... and it ain't done 'til it's pretty."[/font] --Jeff Moden
I not only test each stored procedure, but I test each query in a stored procedure. I test for happy path, not-so happy path, unhappy path, and places that most people don't know exist never mind that there's a path to those places. At the same time, I'm testing for performance and making the code extremely readable along with some substantial comments for the next poor slob that may have to modify my code.
As a result of all my unit and sub-unit testing, I almost never have code come back for a bug or performance problem.
One of the more difficult things to do when testing like that is to convince the managers that it's worth the wait especially if you're the new guy in the company even though they hired you for your years of experience. But it doesn't take long... there's always someone who's willing to risk it all by not testing at all. Those are the people whose time you keep track of along with your own. Then you tell the manager "Remember that project that was finished a month ago? The code was delivered on time for a very aggressive schedule... Ummmm... when are you folks going to be done debugging all the problems you found after release?" They very quickly learn that another "Modenism" rings true...
[font="Arial Black"]"If you want it real bad, that's the way you'll usually get it."[/font] --Jeff Moden
There's also an inmeasurable side benefit to such testing... it's called "innovation". Sometimes developers will come up with some awesome (sometimes totally new) methods for doing things while they're searching for a "fix" to something they found during their own unit testing.
Let me summarize my thoughts... every minute you spend testing during development will save an equal number of hours in post implementation debugging... maybe more. Developers who don't test their own work should be fired. Managers who don't allow enough time for developers to test their own work should be fired. C* upper level management who don't give managers enough time to allow enough time for developers to test their own work should be fired. The Board of Directors and their customers who don't give C* upper level management enough time to... etc, etc.
When it comes to unit testing, s**t flows uphill. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 9, 2009 at 9:43 pm
Nicely put (he said with a smile on his face)
November 9, 2009 at 9:53 pm
Let me start of with saying, it's not important, it's critical.
Managing db's, with odd proc's running against them, is not a task I want to spend my full day on.
I encourage our developers to write "smarter" code, in their proc's and ad hoc queries.
Blocking, locking, multiple sessions, these are the kind of stuff being implemented into our live environment....
because there was no proper testing done. No performance measures, no execution plan reviews.
All of this adds up to performance issues, more time spent on fixing code, if and only when you can pin point the problem afterwards.
Time is always a factor, to push the developers, to code faster, more, with less information - so I do excuse them, but just a bit.
Final word: Test, test, test and once you feel your code is 100%, test again!!
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 10, 2009 at 1:08 am
3 years ago I wrote my first fully unit tested stored procedures that synchronizes (in very twisted way) few tables between two servers. One is in Europe second one in Asia and of course whole synchronization should be done in transaction (distributed of course).
It was my experiment, which took me 3 months to write it, and everyone was blaming me for doing it so long.
But when I installed it on the servers there was only ONE not so critical bug that showed up after 1,5 year or so.
Other than that this stored procedure is working without any problem for 3 years.
That convinced me completely.
November 10, 2009 at 1:39 am
Well, where I work we do write unit tests to make sure everything is working as expected. When I first started doing it it was slow but one becomes fast at this after very little time. It's a good way to ensure code quality and that no one makes a change with unexpected results.
November 10, 2009 at 1:58 am
If someone is intrested how I'm testing stored procedures I want to share.
I have Visual Studio with Resharper.
I create test dll project in c# that is using NUnit as a test framework.
Then I write a tests that
1. Delete test database
2. Create new one
3. Run setup scripts twice - to be sure that it will work correctly also on already installed DB
4. Run each test in a way that all changes done by it are removed after test
The longest part is to write support classes/methods that allow you rollback all your changes from DB after each test (point 4.).
I'm not using transactions for it, because I have many places where I'm testing code that cannot be run under transaction (like db user creation).
In each test I prepare data in database then I run some SQL code and I'm checking results.
November 10, 2009 at 4:27 am
As a developer, of course my focus will be on testing when compared to performance. However, I would back this up by the numerous times when, given the option, the customer would rather go live with something that is slow but working. Optimisation is pointless until you have functionally correct results i.e. you cannot necessarily speed something up then make it correct as the correction may invalidate the optimisations.
In my opinion, never humble chaps and chapesses, this is the DBAs equivalent to developers who believe that the customer would rather an additional feature. Neither additional features not performant code are any good without the code being correct.
Having had a near-flame debate yesterday, I certainly don't want to do the same today!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 10, 2009 at 6:31 am
My career in database goes back to the late 1970's when there was no SQL Server. In those days, you did 90% of your data work in loops. You could watch loops and even build in stops to double-check data as it processed. In essence, you "saw" and tested your work as it processed.
Then along comes SQL. A "Black Box". You stick in a query, and you get data back - but you don't see much in-between.
Test? Unit Test? ARE YOU KIDDING??? To this day I think I am still suffering from some of the early mistrust and panic brought on by SQL many years ago. I don't see how anybody can NOT test. How do you trust SQL, let alone trust yourself???
When you consider (as I have seen many times), that you can mistakenly and yet very simply use say, INNER JOIN when you should have used LEFT OUTER JOIN and this will happily return the WRONG data without a hiccup - YOU'D BETTER TEST!!!
My Rule is simple and absolute - TEST, and do so presuming that you HAVE made a mistake. When you're done with that, pass it up to QA and let them test too. Nothing goes into production until at least two sets of eyes have beaten the tar out of it.
Think about it - in those early days, it was simple to blow things up. But at least when you did that, you knew, ooops, I made a mistake. Now you have SQL where you can screw up big time with a simple valid command, missed punctuation, or other very minor glitch - and SQL will merrily hand you back data - completely wrong data. TEST TEST TEST, and when your done, TEST some more.
November 10, 2009 at 6:40 am
Of course Unit Testing is important! I'm guessing this question was asked as a means of 'stirring the pot.' Some of my best work has come out of fine-tuning stored procedures after unit testing.
I use all of my professional ability and experience to build the best procedure possible. Then, I go into unit testing with a complete lack of confidence in the product, tearing it apart - finding every possible way to break what I have done. That's where you find out what the data is really about. It allows you to anticipate problems that you never would have thought of. It builds your confidence in the product, and puts you in a better position to answer any questions that may arise after the product goes live.
November 10, 2009 at 6:58 am
First, what is techncial debt? I hadn't heard that term before.
Second, I was unit testing before what I was doing was called unit testing. It only makes sense to do it. Why would anyone want to let a stored procedure or any piece of code hit an interface and break because you didn't test the SP well enough?
Third, an efficient query is different from whether the code is logical and works. But, it should be tested for efficiency will data sets large enough with large enough numbers of records to be a good test. This should be part of the unit testing.
November 10, 2009 at 7:07 am
skjoldtc (11/10/2009)
First, what is techncial debt? I hadn't heard that term before.Second, I was unit testing before what I was doing was called unit testing. It only makes sense to do it. Why would anyone want to let a stored procedure or any piece of code hit an interface and break because you didn't test the SP well enough?
Third, an efficient query is different from whether the code is logical and works. But, it should be tested for efficiency will data sets large enough with large enough numbers of records to be a good test. This should be part of the unit testing.
I totally agree that performance testing is part of unit testing to some degree. It may also be considered part of integration testing as unit testing often occurs on infrastructure much different from the target infrastructure yet integration testing or, at the very least user acceptance testing, should occur on identical, or near identical, infrastructure to production.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 10, 2009 at 7:11 am
I can't imagine not unit testing. Unit testing is how I taught myself T-SQL in the first place.
That's not to say that it always catches everything, but it sure catches everything I can think of.
- 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
November 10, 2009 at 7:26 am
I was stirring the pot a little, just to see how many people might agree. good to see most don't.
Technical Debt - http://en.wikipedia.org/wiki/Technical_debt
A good read on it here: http://www.codinghorror.com/blog/archives/001230.html
November 10, 2009 at 7:31 am
Steve Jones - Editor (11/10/2009)
I was stirring the pot a little, just to see how many people might agree. good to see most don't.
You put up a photo with a cheery, innocent looking, smiling face and yet...underneath it all: pure evil!!!
As always, I am relying on the cheery, innocent looking, smiling face to be the real Steve Jones
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply