Something very inspiring happened the other day.
We were in the middle of a test iteration (read that check iteration) comparing our understanding and expectation of the numbers to the ones we were handed by the developement team. I’ve got to confess that after a while, things become a bit monotonous and it is quite easy to lose focus.
That Thursday was one such day; humdrum of the typical activities was bearing down on me when a colleague’s email, requesting help, caught my eye. A new lookup view I had created, had caused her to get animated about a previously matching set of numbers that now no longer matched; and were resulting in many additional rows of data that she did not expect.
To elaborate, I had created a ‘fresher’ version of a UK postcode file that allowed us quick lookup into a typically ‘Geographical’ categories associated with a postcode. These, typically in the education sector, are County, Region, Local Education Authority and Country; There are others but are insignificant in the current context;
The SQL queries that my colleague wrote returned different set of Local Authorities using the new lookup than it’s older equivalent. It js worth noting that I used ‘Local Authorities’ in the last sentence, but ‘Local Education Authority’ before that. These are two different things (those interested can read all about it here and here), however the Local Education Authorities are merely called ‘Local Authorities’ within the education sector. It took her only a gentle reminder from me to ensure she had used Local Education Authorities rather than Local Authorities (as defined in the postcode Geography) and all was good.
What happened next was a testament to my colleague’s total committment and dedication to her work.
After about 10 minutes, I could see her restless; and no doubt she started telling me what she’d tried and how she couldn’t work something out.
She was trying to find out – for herself – ‘why’ the old lookup wasn’t good enough, by investigating what, and by how much, the new lookup was – well, new! I had informed her earlier that while investigating one of the issues she had previously mentioned, I had noticed that there were some new postcodes in a certain region which our postcode file had not picked up and therefore, resulted in an ‘Unknown’ region.
I then decided to speak with the development team to find out if they had a newer version which we could use – hence the resultant new lookup.
My colleague then tried to compare the two.
Initially by doing simple counts:
select count(distinct postcode) from <oldPostcodeTable>; select count(distinct postcode) from <newPostcodeTable>;
She found out that the <newPostcodeTable> had about 189K more postcodes than the old one
So she compared like so:
select old.postcode as OLD , new.postcode as NEW from <newPostcodeTable> new left outer join <oldPostcodeTable> old on new.postcode = old.postcode
Surely, it did give her all new postcodes with many (~ 189K) NULLS; But she wanted only those that weren’t in the old one!
I reminded and demonstrated this scenario using Venn diagrams whilst informing her of the EXCEPT clause which she could use to get this:
select distinct postcode from <newPostcodeTable> except select distinct postcode from <oldPostcodeTable>
As a true tester, she argued on why she cannot achieve the same using the approach she initially took – that of joins; This time, I decided to allow her to do it for herself and suggested that there was no point of using the “old” vs “new” postcode as there were *no* ‘old’ postcodes that had a corresponding ‘new’ postcode; She then took up the challenge and returned back with the same 189K output with the following query
select old.postcode as OLD , new.postcode as NEW from <newPostcodeTable> new left outer join <oldPostcodeTable> old on new.postcode = old.postcode where old.postcode is null;
On the face of it, this query isn’t wrong. It does bring back exactly what she required; but seems a bit overkill to first select everything based on join conditions (in this instance – bring back everything even if it doesn’t match) and then discarding that doesn’t match. EXCEPT on the other hand would compare and display only those that don’t match – thus, in my mind, producing result in a single pass; therefore being quicker.
There was yet another way of achieving what was required – using a sub-query
select distinct postcode from <newPostcodeTable> where postcode not in (select distinct postcode from <oldPostcodeTable>);
I then ran all these three side by side to check its execution time (I could have easily gone into Execution Plan, but that, in this instance, was not necessary). The results were:
EXCEPT query ran in 3 seconds
NOT IN query ran in 9 seconds
JOIN query took 22 seconds to complete!
Thats good learning!
Now personally I’m not a great fan of EXCEPT when it comes to finding exceptions; but I don’t discount it out altogether, it has it’s place where it is the best alternative; this was one of them.
Anyways, upon showing the three ways of achieving the same result, my colleague was then surprised to see the timings on each of them; Immediately the advantage of using one technique over the other was apparent to her, especially when she compared the timings in multiples of the fastest one.
All of a sudden the monotonous afternoon became a rewarding one for my colleague and inspirational for me.
Inspired by my colleague’s steadfastness, persistence and her incredible hunger to learn new things; not just learn, but practice and be good at it! In my humble opinion, this is what makes good testers. I know, if I had been in her position, I would have taken things for granted merely on someone(trusted)’s word and would have continued with my work without wanting to know the why’s or how’s etc; but not anymore.
Perhaps the most important reason why this is inspirational is because this colleague of mine is new to Testing, had little to no SQL skills about 6 months ago and is 64 years old!
Whats more is that she is extremely keen to advance her SQL skills and I’ve committed to provide and all help and support I can. There’s no way you can’t!
And just yesterday, just before she left for a well deserved holiday, she said, “You know, before I joined this team, I couldn’t wait to get out of the workplace for the holidays. Now, whilst I’m happy that I’m going, I can’t wait to get back!”
RESPECT!
ramblings on testing... 