September 28, 2015 at 9:28 am
xsevensinzx (9/28/2015)
Andrew..Peterson (9/28/2015)
xsevensinzx (9/28/2015)
ZZartin (9/28/2015)
xsevensinzx (9/28/2015)
What makes it 10x worse is that while you can verify everything is good with your end result, someone can take that end result (i.e.: data) and use it in a way that makes it wrong (i.e.: putting it in Excel with their own formulas).
This makes baby jesus cry....
You bought a 6+ figure BI solution and all you use it for is to dump data into excel and then run reports that are wrong......
To some extent. The problem is not being able to keep up with the users and when they want to iterate on existing data.
Excel is a great tool, but like any powerful tool, users can use it incorrectly. Like back in high school, when the football coach decided to use his lawn mower as a hedge trimmer (true story) - His fingers were a bit damaged.
Anyway, IT's job is to ensure the environment is functional. And that the tools, based on the budget, let the business manage and use the data. That does not mean that database staff help, and the ones I know do more than their fair share. But business managers need to take responsibility for the data, and how they use it. Or they will blame the database people when things go wrong, and take credit when the go well.
Unfortunately, that's easier said than done and a no brainer.
When you transition reporting to a centralized solution in a major company, you cannot ideally enforce methodologies like that easy across all users. It's a process, an education that takes time.
Even then, I don't see that changing. When people need something now, especially for a client in a service industry with data reporting, they are very limited on following the same methodologies and process that are in place to prevent fires from happening.
Well the other thing and this kind of goes back to a topic awhile ago on reporting is that when the standard report doesn't show the numbers that someone wants they dump the raw data and tweek it till it matches what they want. But yeah changing the success criteria after the test is run kind of defeats the purpose of testing in the first place....
September 28, 2015 at 10:16 am
However I do believe we need to test our code. We all do test our code, even if it's with a few before/after queries. What I don't get is why we don't just mock up a quick test that we can run in an automated fashion. It's not much more work, and then we can more easily re-run the test later to ensure any refactoring or optimizations we make continue to work as expected
Absolutely. If I have a full set of requirements, I'll take it a step further. Put in test cases that cover all code paths and predict ahead of time what the result should look like for each test case.
Ken
September 28, 2015 at 11:45 am
I prefer BDD over TDD as a business analyst and tester can work together to produce clearer requirements in an ubiquitous language that is human readable by a business person but drives the BDD framework.
We wrote quality code years before such frameworks existed but the frameworks were written as tools to make life easier. Whether you agree or not someone perceived a need and devised TDD as a means to fulfill that need.
I find TDD quite a hard discipline to adopt but the value comes when you extend or reactor code.
I think for DBs the value is in the thought process of identifying things that could go wrong or be suboptimal and devising tests that throw errors if a case is detected.
If you can test for it you can prevent it and possibly start thinking about automatically fixing things
September 28, 2015 at 12:31 pm
roger.plowman (9/28/2015)
TDD is one of those subjects I'm torn on. On the one hand it is unequiocally a good idea, of COURSE you want to test everything!On the other hand, as my company's sole IT person it's nearly impossible--even when you write the code in small testable chunks.
On the VB.NET side the front end has an array of automated tests to make sure all the t's are crossed and i's dotted simply because I don't have any one to QA the program! So automated repeatable tests are pretty much the only testing there can be, short of end-user production complaints. (and oh, how Einstein-like users are in discovering new and interesting ways to trash a system...)
But it mainly tests to make sure all the properties of objects are properly set. Unit tests and integration tests and all the rest are possible because it's all generic, all OOP. You're testing that blanks have been filled out more than you are new code.
In a way that's desirable, of course. The less code you need to write the less that needs testing.
------------
And then there's the T/SQL side. (facepalm)
To the good, each function or SP is pretty much single-purpose by necessity. In theory that makes everything easier to test.
But to the bad, T/SQL is PATHOLOGICALLY MURDEROUS to the concept of generic code. Everything needs to be custom written in every SP or performance just falls off a cliff. Generic code is testable code, folks! The less code the less testing needed!
Not to mention T/SQL is aggressively crude. By which I mean it offers almost no reuseability capabilities, unless you want to go the dynamic SQL route, which is unwise from both a performance and security standpoint.
Yes, there are workarounds, but T/SQL takes an almost gleeful delight in making the programmer do drudge work, copy and paste becomes *desirable* as a technique. (shudder).
In fact, writing programs to create SPs is almost a necessity for any database of any scale. And lets not even mention T/SQL's sheer verbosity. I'm no fan of C-syntax brevity-to-the-point-of-obscurity, but my God! T/SQL is way too close to the COBOL end of the scale for my taste.
In short, TDD in a database is kind of difficult, and the database fights you. It wants a static definition of code structure (constraints, etc) rather than a dynamic one.
Also, TDD works, but as the the editorial points out it requires knowing what you want in the first place. Something users aren't really a fan of... 🙂
Thus my conflicted feelings on the subject.
I don't know how you're implementing TDD in a DB setting, but there are lots of opportunities for generic code. Perhaps it's because you're discarding dynamic SQL as an option: in this particular setting there isn't a good reason NOT to use it. Noone other than your internal folks will access the test code, so there's no security issue; performance of dynamic SQL is bad only if the resulting SQL statement is "bad" (which isn't a dynamic SQL issue, it's a coding issue).
We've used it a fair amount, and you can use a number of techniques to make setting up TDD easier. Possibly the most "effective" one for us was to simply set up a cookie-cutter test script, and save it for each test.
Again - whether you use dynamic SQL to create the test or the cookie-cutter + adjust, save the static SQL and reuse it. Assuming the "test" rolls back what it did after you've checked for success or fail, you can keep running the same test for years to keep checking functional validity.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 28, 2015 at 2:37 pm
Matt Miller (#4) (9/28/2015)
I don't know how you're implementing TDD in a DB setting, but there are lots of opportunities for generic code. Perhaps it's because you're discarding dynamic SQL as an option: in this particular setting there isn't a good reason NOT to use it. Noone other than your internal folks will access the test code, so there's no security issue; performance of dynamic SQL is bad only if the resulting SQL statement is "bad" (which isn't a dynamic SQL issue, it's a coding issue).We've used it a fair amount, and you can use a number of techniques to make setting up TDD easier. Possibly the most "effective" one for us was to simply set up a cookie-cutter test script, and save it for each test.
Again - whether you use dynamic SQL to create the test or the cookie-cutter + adjust, save the static SQL and reuse it. Assuming the "test" rolls back what it did after you've checked for success or fail, you can keep running the same test for years to keep checking functional validity.
Actually I was ranting about the code to be tested, not the testing code. 🙂
But TDD in SQL Server is a still a pain in the rear compared to .Net languages.
September 28, 2015 at 3:05 pm
Jeff Moden (9/27/2015)
First, I loved your observations in the article. It takes so little time to "test-as-you-go" that I don't understand why more people don't do it. To wit, I do things pretty much as you've described. I read the requirements, write a section of code, test it, make adjustments and maybe fix some obviously missing requirements (requires a discussion with the owner of the requirements) and once that section is done and documented, then I can move on to the next section without having to look back at code already completed.
...
Do you save your tests? What if you touch that code next month? Do the same tests get run or just a new one(s)?
September 28, 2015 at 3:08 pm
xsevensinzx (9/28/2015)
I can understand the what the author means in the article, especially in the BI realm on my end. Sometimes there is just not enough time due to last minute requests.
So yes and no. You have time to test, and you can test. You might not test everything, but you can test.
If I tell you to split a name into first/middle/last, I'm guessing you mock up 5-10 samples and split them with an algorithm. You built the algorithm, you run it, you see results. Maybe you do this by eyeball, or maybe you write code.
If we change to "put the middle name with last name when the middle name is "von", and we are deploying today, can you write 3 or 4 more tests to check your algorithm changes? Maybe not, but I'd expect at least one.
In BI, it's hard, but that's the best place to actually write tests for your algorithm to verify it does what you expect. If there are exceptions in the data, you add tests and change things, but you can prove you're testing your code and following specs.
I think far, far too many BI developers get caught up in I test everything or nothing. The people I've seen most often pleased with testing are the BI people, even though many of them end up testing row counts more than actual algorithms.
September 28, 2015 at 3:11 pm
ken.trock (9/28/2015)
However I do believe we need to test our code. We all do test our code, even if it's with a few before/after queries. What I don't get is why we don't just mock up a quick test that we can run in an automated fashion. It's not much more work, and then we can more easily re-run the test later to ensure any refactoring or optimizations we make continue to work as expected
Absolutely. If I have a full set of requirements, I'll take it a step further. Put in test cases that cover all code paths and predict ahead of time what the result should look like for each test case.
Ken
What if you don't have a full set of requirements? I'd say test what you know has to be there, and what you have issues with. I don't really want to test INSERT statements, but I do think that things beyond simple CRUD need tests.
If I don't have full requirements, I test what I write.
September 28, 2015 at 4:21 pm
Steve Jones - SSC Editor (9/28/2015)
Jeff Moden (9/27/2015)
First, I loved your observations in the article. It takes so little time to "test-as-you-go" that I don't understand why more people don't do it. To wit, I do things pretty much as you've described. I read the requirements, write a section of code, test it, make adjustments and maybe fix some obviously missing requirements (requires a discussion with the owner of the requirements) and once that section is done and documented, then I can move on to the next section without having to look back at code already completed.
...
Do you save your tests? What if you touch that code next month? Do the same tests get run or just a new one(s)?
Very much like I did with DelimitedSplit8k, the tests are frequently saved in the header of the code or as inline comments if multiple sections are required. When not practical to do so, they are saved in an SVN project folder. Do understand, though, that sometimes the "test" is the code itself and no separate test needs to be saved.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 28, 2015 at 4:35 pm
roger.plowman (9/28/2015)
In fact, writing programs to create SPs is almost a necessity for any database of any scale. And lets not even mention T/SQL's sheer verbosity. I'm no fan of C-syntax brevity-to-the-point-of-obscurity, but my God! T/SQL is way too close to the COBOL end of the scale for my taste.
Also with performance, every modern implementation of C seems to try to limit performance problems to the actual problems with time and space tradeoffs that are genuinely inherent in the problems to be solved. T-SQL's performance problems seems to be way too inherent in implementation decisions of the developers of the interpreter.
For example, functions are a great abstraction, except of course in T-SQL.
September 29, 2015 at 4:53 am
Jeff Moden (9/28/2015)
like I did with DelimitedSplit8k, the tests are frequently saved in the header of the code or as inline comments if multiple sections are required.
We put them at the bottom of the procedure / function in a comment block starting with
/* TESTRIG
...
*/
so that a simple FIND "TESTR" will jump the cursor down.
Even if the procedure is essentially read only we use a transaction Belt & Braces. Typical procedure template looks something like this:
ALTER PROCEDURE dbo.MySProc
...
RETURN @intRetVal-- Return error number, 0=No error
/* TEST RIG
BEGIN TRANSACTION
EXEC dbo.MySProc
@MyPrimaryKey1= 'PARAM1'
, @MyPrimaryKey2= 'PARAM2'
, @MyPrimaryKey3= 'PARAM3'
, @intDebug=01
--
-- COMMIT
ROLLBACK
SELECT TOP 10 *
FROMdbo.MyTableName
WHERE1=1
--AND MyPrimaryKey1 = 'ABC123'
--
ORDER BY MyUpdateDt DESC
*/
--================== MySProc ==================--
GO
the SELECT on the MyTableName is included as often handy for checking what the data should be! but I guess is quite often never used in some procedures.
We put the test rig inside the procedure code just in case we want to review it in SSMS or with sp_HelpText, but all our procedures are stored in separate source code files - so it could be outside the actual procedure code. We strip all comments / whitespace before deploying to Testing so the buffer-space usage etc. isn't an issue (for us)
September 29, 2015 at 4:55 am
P.S.
@intDebug=01
is laziness! Put cursor at end of line and press BACKSPACE to remote the "1", or same again to append a "1" (to the zero).
All our procedures have final parameter to turn debugging info on/off, although some will have no debugging output until such time as it becomes necessary to add some 😛
September 29, 2015 at 7:15 am
Steve Jones - SSC Editor (9/28/2015)
xsevensinzx (9/28/2015)
I can understand the what the author means in the article, especially in the BI realm on my end. Sometimes there is just not enough time due to last minute requests.So yes and no. You have time to test, and you can test. You might not test everything, but you can test.
If I tell you to split a name into first/middle/last, I'm guessing you mock up 5-10 samples and split them with an algorithm. You built the algorithm, you run it, you see results. Maybe you do this by eyeball, or maybe you write code.
If we change to "put the middle name with last name when the middle name is "von", and we are deploying today, can you write 3 or 4 more tests to check your algorithm changes? Maybe not, but I'd expect at least one.
In BI, it's hard, but that's the best place to actually write tests for your algorithm to verify it does what you expect. If there are exceptions in the data, you add tests and change things, but you can prove you're testing your code and following specs.
I think far, far too many BI developers get caught up in I test everything or nothing. The people I've seen most often pleased with testing are the BI people, even though many of them end up testing row counts more than actual algorithms.
I think far, far too many BI developers get caught up in I test everything or nothing. The people I've seen most often pleased with testing are the BI people, even though many of them end up testing row counts more than actual algorithms.[/quote]
I'm from a BI background, and we did many stub tests, plus a final test run in a small development set of real data.
Then we shared and discussed any questionable results, first among us, then the users.
Sometimes the data wasn't as clean as the users assumed, or we needed clarification on what they were expecting to see in some cases.
In the end, we had some key users in several areas that would be able to help others in their areas understand some of the imperfections.
Since some quirks in the data were due to users not following the process (and the ERP system was missing a rule or edit), there was a question of who should fix it.
BI has some additional considerations, as you have historical data.
So you have attributes and processes that change over time.
Even something like a 4 4 5 Fiscal Calendar could change.
So what happens when you change to closing quarters at month end?
Leads to some interesting conversations, especially with week over week on a year to year basis over history.
Testing and sharing results will be needed to help everyone understand the implications.
September 30, 2015 at 7:16 am
I develop code in a fashion similar to what you have stated. I also find that expectations change as we develop, especially when we start showing the results requested. What I struggle with is trying to create any sort of automated testing. I rarely do anything that is the same, otherwise I would just use what I already have. I do not have my own personal testing environment. It is shared with many others. So the data is ever changing. I can't seem to configure anything that is repeatable. The environment keeps changing.
October 1, 2015 at 10:14 am
Iwas Bornready (9/30/2015)
I develop code in a fashion similar to what you have stated. I also find that expectations change as we develop, especially when we start showing the results requested. What I struggle with is trying to create any sort of automated testing. I rarely do anything that is the same, otherwise I would just use what I already have. I do not have my own personal testing environment. It is shared with many others. So the data is ever changing. I can't seem to configure anything that is repeatable. The environment keeps changing.
You should look at a testing framework. The idea is the data is the same for tests. You need a consistent environment, which is part of the testing. Even when nothing is the same, you can have effects between modules, so having tests ensures the functionality you expect is still working.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply