Voice of the DBA

Category: Editorial

  • Don’t Use Code Coverage

    I got asked in a seminar recently how to perform code coverage for T-SQL. There aren’t a lot of options, but there is a project from Ed Elliot called SQLCover that looks to examine how much of your code gets called from a test. There may be other methods, but my question back was why does code coverage matter? There were a few reasons given by various people, but ultimately the original questioner said that their boss wanted a report.

    That’s fine, and it’s a valid reason to try and measure code coverage, but is that an effective use of developer time? I don’t think so, especially as trying to meet some code coverage goal is fraught with all sorts of issues. Apart from various good points on Stack Overflow, no shortage of people have blogged about the problems of relying heavily on a code coverage metric. I won’t repeat all the arguments, but I’ll give a few of my own.

    First, someone pointed out that you want to be sure all your code is tested. I disagree with that, especially in many  long dev cycle applications. There are all sorts of enhancements I’ve seen in many applications that never get used. While you should certainly check them for security issues, if the features aren’t being used, remove them. Since most of you don’t implement tests for security issues, I’m not sure there’s value in telling you to write a unit test for logic or functionality.

    The other issue with code coverage is that anyone other than a developer using this metric will put pressure on the developers to write more tests. Some developers may write good tests, but I wouldn’t be surprised to have tests written that always pass and aren’t checked for correctness. Tests like these are misleading and potentially create more bugs since anyone refactoring code might think their changes haven’t caused an issue if the test passes. Bad tests, IMHO, are worse than no tests.

    I do think that code coverage can be valuable. If a developer is looking to ensure that some part of their code is being checked, especially in long stored procedures, then code coverage can guide them to a place that might need  testing. Since most code needs more than one test, covering more than one case, code coverage doesn’t help. I’d prefer a developer spent more time thinking about how to build better tests (or experimenting) than looking at coverage reports.

    And if you need a code coverage report, try my technique for building one

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Why Don’t You Unit Test Code?

    First, I’ll apologize to those that do use formal, automated tests in advance. I’m not really writing for you, and I’m sure you can teach me more about testing. Perhaps you can comment about how and why you do this, or even write some articles to help others.

    For everyone else, why don’t you test in a repeatable fashion? Let me know with a comment today.

    One thing I’ve noticed as I’ve talked DevOps and testing for a number of years is that so many developers agree testing is a good idea, but claim they have no time, no support from management, or no need to repeat tests once code is written. Sometimes I hear all three excuses at the same time. The same people will also admit that they have problems with code at times, and that quality could be improved.

    Perhaps it’s me, but I’ve never had my boss sit there and watch me code. He or she doesn’t know how I might write code. I certainly write a query, run it, and look at a bunch of rows and see if I think the code is correct. Perhaps I need to run other queries to verify values in the results. However, I could also build a quick test with something like tSQLt, like the one I set up for checking COALESCE that does the same thing. With my unit test, I can run it over and over, modifying the test as I discover new edge cases to validate. Which way do you want to write code?

    Even if I have a set of data I need to assemble, it doesn’t take much longer for me to write a test, especially when I need to also solve the problem. I find that putting a test together forces me to slow down and think about the rules for my code. Since I need to come up with some result set, building that into a test of some sort is fairly easy and let’s me double check how I think data will change. I can then repeat the test over and over as I modify code. I can even grab sample data from production and use that as the basis for my unit test if I need to check a specific entity’s values.

    I know many things I write in SQL might not be worth a test. Often the simple CRUD queries and basic aggregations aren’t things I’d test, but when my query becomes complex, includes APPLY or outer joins, and logic to decide how to filter or format data, wouldn’t a unit test make sense? What about if you want to be sure you’re processing the correct rows from a large CUBE or ROLLUP operator? Certainly if I’m fixing a bug, writing tests makes sense, at least I think so.

    Let me know what you think today.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Teaching Quickly

    I’m a constant learner. I try to regularly experiment and try new things at work and in the rest of my life. For work, sometimes this means working outside of the other hours and commitments I have to get better at manipulating data. Sometimes I go deep and sometimes I just want the general gist of how to solve a problem I’m having. Time constraints come into play at times because there’s never enough. Even with limited time, I do want to understand the reasons why things work, or why I should make a choice for some function/method/framework/pattern/etc.

    I’ve been working with R a bit this summer, trying to learn a bit more. I’ve tried tryr.codeschool.com as a guided tour, but sometimes it doesn’t help me understand enough of the details. Even as I experiment, I’m going through the motions. I purchased R in a Nutshell, and just got Stephanie Locke’s Working with R. I’m hoping these books will give me a more thorough grounding in R.

    As I’m learning, I always want to ensure I understand the implications of how I may write scripts. What do my choices mean for quality, performance, etc. Sometimes trying to understand why slows me down, and I don’t get much done. That’s fine, and maybe that’s for the better. However, I don’t want to learn slowly by going through all the pain that someone else went through. I want to know how someone would write code today, and why they do so.

    I ran across this post on learning the hard way. It discusses the way some people try to teach others, and I’ve been through some of this as I’ve tried to learn various technologies. The teacher sometimes wants to assume I am going through their pain and can sympathize, but often I’m learning something new for the first time. I’m sure I’ve been guilty of this as well, perhaps trying to level set or show an old (or poor) practice to emphasize why it’s better to write code in a new way.

    The thing is that my time is precious, and I shouldn’t have to work through the mess of the way things used to work to build good habits today. What’s new to someone might not be new to all, and certainly at some point, all code isn’t new anymore. I’d rather teachers, and me, sometimes focus on giving us good habits today. Teach us the right way now, and spend time on error handling, testing, or other good habits. Certainly there are cases where we need to know about the changes to systems, but explain this is an upgrade section. And then tell me what’s different, don’t wax nostalgic on the way you once were forced to find innovative solutions.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Rewrite Your Code Tomorrow

    One of the things that many developers learn over time is that they write better code with practice. In fact, some of the time a developer will find that if they write a bit of code to solve a problem, they’ll come up with a better solution a day or two later, as they’ve had a chance to think about the problem. Even while moving on to other code, their brain will continue to examine the previous solution, inherently knowing the approach wasn’t the best.

    Many of us see this with our code over time. If we look back at something we wrote a year or two ago, many of us cringe. At least, most of us cringe, especially if we constantly look to improve our skills. We see that old code as poorly written, in a way that we might never want to repeat in our career. I think that’s a good sign that we are moving forward and learning better patterns over time. If you don’t view any of your old code as poorly written, I’d stop and get a second opinion. You certainly might have written some great routines that stand the test of time, but more often than not, I find places where I’d improve code. Having someone else review my work might help me build better skills.

    One of the things I heard recommended recently was that developers solve some problem, commit the code in their VCS, and then tomorrow, redo the work. Start the day by deleting that section of code (that proc, method, class, etc.). Then move forward and rewrite the solution, not trying to remember what was there, but by resolving the issue. Plenty of application developers find they’ll write similar logic, but better structured code. Even if you don’t believe this, conduct an experiment on a small section of your code.

    I don’t know if we’d come up with the same results with T-SQL as many of us tend to solve the problems the same way across a short period of time. However, maybe you’d like to post a mock up of your challenge and solution, and ask for some opinions. We have great forums at SQLServerCentral and some really sharp SQL developers. You might get the validation that you built a great solution, or maybe you’d get some ideas on how to improve both your code and your skills.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and Libsyn.