July 11, 2012 at 3:20 pm
In your experiences, when you get the results of a query, how and who determines if the results are correct? Or perhaps I need to step back further, when you get a projects does it come with sample data and expected results?
What has been your most effective workflow to validate results?
July 11, 2012 at 10:45 pm
Let me take your questions one at a time.
1. In your experiences, when you get the results of a query, how and who determines if the results are correct?
This will vary but technically it is a 3 (or more) step process: 1) developer validates the results based on test data available (see my answer to #2), 2) SIT is performed by an independent testing team with their own data and 3) UAT is performed by the business user or customer, again with data of their own design.
2. Or perhaps I need to step back further, when you get a projects does it come with sample data and expected results?
Rarely do we live in such a perfect world. I would suggest you take a look at these two articles about generating test data. Expected results is entirely another thing - some appropriate deductive logic must be applied to predict what you expect based on the sample data. Sometimes the business users will provide some relevant examples.
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/
There is also the occasion where you're asked to rewrite a query for better performance. Assuming you've got a reasonable test system with some reasonable data, it is relatively easy to compare the results of a rewritten query against the original. Normally I start with the record counts and then proceed to column-wise data checking. The fun part comes in when you identify an issue in the original query! Then you need to prove the rewritten version is correct by identification of the data anomaly that led to incorrect results in the first place.
3. What has been your most effective workflow to validate results?
See my answer to point #1. Add a healthy dose of common sense though.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 12, 2012 at 10:09 am
Lack of a perfect world is the difficulty I am dealing with. Essentially I have certain limits which are not something I can change. (I can and do try to get these changed)
The people involved in any project: The developer, the user/requestor. We are a small company, not enough people for anything else.
Test Systems? We don't need them: The standpoint of the people with the purse strings.
Test data generation: The data and its fields can not function correctly if it is actually random. I am dealing with medical insurance data, for a viable test I would need non-random test data.
Re-writes and upgrades are not really an issue.
What is occuring is a frustration and backlash from the users, whom don't think they should be part of the testing at all. They get frustrated with the "Back and forth" of the testing cycle. They essentially are pressuring my boss, and then me by association to not be a part of the testing/validation at all. Ie..
"I request a project, and I get it once it is ready and 100% correct."
July 12, 2012 at 11:41 am
David Lester (7/12/2012)
Lack of a perfect world is the difficulty I am dealing with. Essentially I have certain limits which are not something I can change. (I can and do try to get these changed)The people involved in any project: The developer, the user/requestor. We are a small company, not enough people for anything else.
Test Systems? We don't need them: The standpoint of the people with the purse strings.
Test data generation: The data and its fields can not function correctly if it is actually random. I am dealing with medical insurance data, for a viable test I would need non-random test data.
Re-writes and upgrades are not really an issue.
What is occuring is a frustration and backlash from the users, whom don't think they should be part of the testing at all. They get frustrated with the "Back and forth" of the testing cycle. They essentially are pressuring my boss, and then me by association to not be a part of the testing/validation at all. Ie..
"I request a project, and I get it once it is ready and 100% correct."
You need to insulate yourself from this mess somehow. Since the users decide if the application is 100% correct or not, and since bugs in production cost them time and frustration, THEY are the ones who need to sign off on it. If they don't want to do testing, get them to sign their names on paper anyway. When they realize that they are essentially writing a blank check, they will be happy to do testing. Tell management you have a solution for the problem of bugs getting into production, and for the problem of users being unsatisfied with the application - you're going to make them sign a paper that says they agree that the application is finished. After all, they are the ones who came up with the list of requirements, right? They are the only ones who can know if the requirements have been met to their satisfaction. It is completely ridiculous for them to expect you to know what will satisfy them - sure, they can come up with a list of requirements (right?) and you can execute on those requirements in a technical way, but from a user perspective you can never know if you did it right, because you are not a user!
Sign-off sheets are your solution to this. If people sign off on things they haven't tested and something goes wrong, it's on them because they signed off and said it was all ok. Eventually they will learn that it's in their best interest to test things.
As for the lack of testing systems... just throw a few test products up in the database and have people start purchasing them and that problem will go away quickly as well. Sometimes you have to show people the error of their ways. Ten customer service incidents probably cost more than a test server, put it that way and management should understand.
And if that doesn't work, just tell them Jasmine thinks they are wrong, I'll set them straight 🙂
July 12, 2012 at 12:06 pm
Jasmine, not only did that make me laugh, but is dead on. It is stunning the level of blaming/refusals etc people can manage when they realize something might hold them accountable for something.
User: "The data is right there, I told you I want all outpatient hospital stays for cardio rehab for the last year, and who went on to have MRI's"
Me: "Outpaitent services do not have people staying the the hospital, do you really want inpatient? By year, are you asking when it was performed, or when it was paid, or the admission date, or the discharge date? What is cardio rehab, the code book lists 2000 codes with cardio in the description. MRI's, there are several sub types, did you want something specific?"
User: "I don't know, I just need it, the data is all there, just use your magic wand and get it to me."
Me: "I don't have enough information to get anything resembling a real answer."
User: "I don't know the answers, just do it."
Me: "Do you know who might have the answers?"
User: "No"
Me: "Do you know what you want to see in the results?"
User: "No"
2 weeks later
User "Why can't you just get it right the first time?"
:crazy:
July 12, 2012 at 12:28 pm
2 weeks later
User "Why can't you just get it right the first time?"
:crazy:
Do you tell them that you did get it right? I got it 100% correct according to my version of the specs. Sadly those were the only specs available. 😉
No test systems....are these people completely deranged???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2012 at 12:44 pm
Yes, yes they are.
I have spent many many years attempting to save them from themselves.
July 12, 2012 at 12:45 pm
David Lester (7/12/2012)
Yes, yes they are.I have spent many many years attempting to save them from themselves.
Not sure if you saw the attachment to my previous post. You should print it out and hang it on your wall. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2012 at 12:52 pm
Many years ago when I was writing COBOL programs to generate ad hoc reports for users I had one user whose requests were less than useful. I usually spent several hours over several days on the phone (we were in separate builds several miles apart) working out the details and then once I had the data right the same thing occurred regarding format of the report.
Neat thing was after doing this for several months this user actually learned what he needed to include in his requests and the time spent clarifying things dropped a lot and his reports were turned around in days rather than weeks.
Unfortunately, shortly after he got trained, he left the company. I had to start all over with his replacement without as much luck.
July 12, 2012 at 1:06 pm
Sean, I had not seen the attachment before, very tempted to print and hang at the enterance to IT...
Lynn, yes, I have two people whom I have been able to reach something of meaningful level "getting it". Their projects go really fast, which of course makes the accountibility dodgers claim unfairness.
I truly hope I am not actually here when the actual disaster hits.
July 12, 2012 at 3:38 pm
Lynn Pettis (7/12/2012)
Many years ago when I was writing COBOL programs to generate ad hoc reports for users I had one user whose requests were less than useful. I usually spent several hours over several days on the phone (we were in separate builds several miles apart) working out the details and then once I had the data right the same thing occurred regarding format of the report.Neat thing was after doing this for several months this user actually learned what he needed to include in his requests and the time spent clarifying things dropped a lot and his reports were turned around in days rather than weeks.
Unfortunately, shortly after he got trained, he left the company. I had to start all over with his replacement without as much luck.
Sure, yeah, when you train people how things are supposed to be, they realize how things aren't supposed to be, and they leave.
July 12, 2012 at 6:42 pm
There is always a test system available:
- Separate schema on the same database server.
- Separate deployed front end accessible by a different URL, also on the same server.
My take is that you need to seek executive sponsorship for your views. Try to get your CEO to realize that users not doing testing, not having a test environment, etc. is thinking like a small business. If your company wants to grow (what CEO doesn't cherish this thought?) they need to start thinking like a bigger company. If you can get the CEO on board, others will fall in line.
If this fails for some reason, you won't make any headway by trying to convince the users of the error in their ways. You need to develop a plan that will allow them to self-realize that they've been proponents of the wrong point of view.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 12, 2012 at 6:50 pm
dwain.c (7/12/2012)
If this fails for some reason, you won't make any headway by trying to convince the users of the error in their ways. You need to develop a plan that will allow them to self-realize that they've been proponents of the wrong point of view.
Difficult sometimes, Dwain, but one of the ways I've been able to do that in the past is by showing them that by using a sandbox, they can go crazy creating 'simple' test pieces for each thing they want to test instead of having to dig through all the possibilities that production has to deal with.
I've even had to host it off my own desktop for a bit until I could get management to buy off on it, though even a SQLExpress instance can be better than nothing, and it's free... well, as long as a DBA's there anyway. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 12, 2012 at 8:10 pm
If it wasn't difficult, then we wouldn't get paid the big bucks! 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply