The Resplendent Developer

Software Development and Software Quality Assurance

October 10, 2012
by Russell Sinclair
Comments Off on SQL and “where not in” alternative

SQL and “where not in” alternative

This question comes up occasionally, and I usually end up being a tad evasive about it. So, it comes time to post the real answer here:

“Lets say you have a student table and teacher table. How do you write a SQL query to show all teachers that don’t have a student?”

I always say, “Well the first thing that comes to mind is

SELECT t.teacher_id FROM teachers t
WHERE t.teacher_id NOT IN (SELECT s.teacher_id FROM students s)

but I know there is a more performant method.” Then I don’t follow up with the answer.

Yeah, I know…

The above query basically works, but it is slow because it has to pull up a list of all student records, extract the teacher_id, and then take the list and apply it to the teacher table. It is a very slow approach despite being VERY clear to the maintenance programmer. 

So, lets write up the more performant answer now!

SELECT t.teacher_id FROM teachers t
LEFT JOIN students s ON s.teacher_id = t.teacher_id
WHERE s.teacher_id IS NULL

Now the query says “OK, lets do a join and just throw out the places where there is no corresponding teacher_id” It is MUCH, MUCH quicker because it runs as a “single query”, instead of basically breaking down into 2 queries.

However, I would argue that if you were writing SQL for testing purposes, and not production purposes, you would want to use the first of the two examples.

Yeah, I know….

There is a real reason for it. The average QA person, even one who specializes in automation, has very different goals than the average developer. A developer wants to write the fastest SQL query possible because the end user wants their answer and the query may be run hundreds of times a second. On the other hand, a QA Engineer wants the answer, and has a bit more luxury in their time. Furthermore, if the QA is writing code for automation, then their primary goal is correct functionality and maintainability. The QA may need to use the faster solution, but the greater concern is clarity.

Got a better answer than me? Leave a comment to flame me below! I welcome the knowledge!

October 9, 2012
by Russell Sinclair
Comments Off on A Recent Coding Exercise

A Recent Coding Exercise

Recently, in an interview, I was asked to create a routine to do the following:

Given a lower-case, alphabetic string, determine if the letters can be made into a palindrome. If it was, I should return a 1, and if not, return a 0. I was given about 30 minutes to do this and had a very limited compiler to work with. I chose C# as my language and produced the following code.

using System;

public int isPalindrome( string S )
{
  // A Palindrome can have only 1 character with an odd number of occurrances. 
 //Otherwise, each must appear in a multiple of 2.
  int lessthan2 = 0;

  // Look at each letter of the alphabet
  for(char i=’a’;i<=’z’;i++)
  {

   if(S.Contains(i.ToString()))
   {
    int accum=0;
    for(int j=0;j<S.Length;j++)
    {
     if(S[j]==i)
     {
      accum++;
     }
    }
    if(accum%2==1)
    {
     lessthan2++;
    }
   }
   if(lessthan2 >1)
   {
    return 0;
   }
  }
  return 1;
}

 

While the code fit all the requirements and successfully computed the answer (including for strings up to one million characters in length) and was performant, the code was somewhat inelegant. In fact, the hiring manager specifically commented that I iterated through the alphabet and that was an unacceptable solution. While I believe that given the constraints, this was an unfair conclusion, it’s a fair criticism. He also didn’t point out that my check for “Lessthan2” could have been in the same if structure so that the routine could exit sooner. If I’m going to have multiple return statements, there is no reason I can’t do that. So, the question that comes to mind, is how can I make this more elegant?

After thinking about it and refreshing the syntax of the generics, I came up with this on the second pass:

using System;
using System.Collections.Generic;

public int isPalindrome ( string S )
{
  int lessthan2 = 0;

 HashSet<char> letters = new HashSet<char>(S);

  foreach(char i in letters)
  {
   int accum=0;
   for(int j=0;j<S.Length;j++)
   {
    if(S[j]==i)
    {
     accum++;
    }
   }
   if(accum%2==1)
   {
    lessthan2++;
    if(lessthan2 >1)
    {
     return 0;
    }
   }
  }
  return 1;
}

 

HashSet is a nice little generic introduced in .Net 3.5 that accepts a list of items and only keeps the uniques. In my above example, it would take a string such as “aabbcc” and simply save “abc”. This would let us iterate through a possible subset of the alphabet. We can do a tad less clunky by going all the way back to what .Net 1.0 gave us: Regex!

using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;
public int isPalindrome ( string S )
{
  int lessthan2 = 0;
  HashSet<char> letters = new HashSet<char>(S);

  foreach (char i in letters)
  {
   int accum = Regex.Matches(S, i.ToString()).Count;

   if (accum % 2 == 1)
   {
    lessthan2++;
    if (lessthan2 > 1)
    {
     return 0;
    }
   }
  }

  return 1;
}

 

I think that is stronger. However, is it the only one? I’m sure I can dig deeper in regular expressions and come up with an even more elegant approach. Still, is this the most efficient solution? If we dig a little deeper into what .Net 3.5 gave us, we can see there is indeed a much more elegant solution: LINQ!

LINQ (Language Integrated Query) lets us apply a rather powerful querying language to almost anything. So, how would we do that in LINQ?

using System;
using System.Collections.Generic;
using System.Linq;

public int isPalindrome ( string S )
{
  // Create and populate a collection so Linq can do our
  // heavy lifting.
  List<char> ArrayofS =new List<char>(S);

  // Run a Linq query to return every letter where there are
  // an uneven number of instances. A Palindrome, by definition
  // can only have a maximum of ONE uneven number
  var query = from charlist in ArrayofS
   group charlist by charlist into templist
   let count = templist.Count()
   where count %2 == 1
   select new {Value = templist.Key, Count = count};

  // Not a Palindrome
  if (query.Count() > 1)
  {
   return 0;
  }

  // A Palindrome
  return 1;
}

 

Comments? Thoughts? Criticisms? Leave a comment!

October 3, 2012
by Russell Sinclair
Comments Off on First Post – Happy Birthday!

First Post – Happy Birthday!

“Share because you want to. Share because you want to help, but also because you want to help yourself. Share not for the recognition but for the love of teaching.

It takes a village, dear reader, to be a community. It’s you, and me and no one in between. Now, go write, create, commit.”

— Scott Hanselman

While I started out as an English major WAY back when, I left it in favor of software development as I found it to be a place where the subjectiveness of grammar wasn’t punished. After all, if it compiles and works, it’s good. In that case, the code is more like a work of art rather than an emotionless structure of perfectly arranged words. 

For example, in a recent interview, I was asked to provide a coding sample in Ruby using WatiR.

# This is a simple factory class
class Browser
 def Browser.Factory (whichbrowser)    
  case whichbrowser
   when ‘ie’
    browserInstance=SampleApp.new :ie
   when ‘ff’
    browserInstance=SampleApp.new :ff
   else
    raise “Bad Browser Type: #{whichbrowser} not supported at this time”
  end
 # For some reason, WatiR is jumping the gun sometimes.
  # So lets let it wait if it needs
  browserInstance.driver.manage.timeouts.implicit_wait = 10
  return browserInstance
 end
end

 

The code compiled. That is what was important. However, as an “English” major, I might have points taken off for any syntax alternates because it isn’t the way the professor would have done it.

Example:

-4 points because I needed to do it like this

# This is a simple factory class
class Browser
 def Browser.Factory (whichbrowser)    
  case whichbrowser
   when ‘ie’
    browserInstance=SampleApp.new(:ie)
   when ‘ff’
    browserInstance=SampleApp.new(:ff)
   else
    raise “Bad Browser Type: #{whichbrowser} not supported at this time”
  end
 # For some reason, WatiR is jumping the gun sometimes.
  # So lets let it wait if it needs
  browserInstance.driver.manage.timeouts.implicit_wait = 10
  return browserInstance
 end
end

 

See the difference? The point of the above is to demonstrate the usage of the “Factory” design pattern and how I solved the issue of encapsulating settings (the implicit_wait) that isn’t handled in the constructor. Furthermore, the pattern could easily hide specific details that the person who enhances the test automation doesn’t necessarily care about. However, points would have been counted off because I didn’t use an equally valid solution.

Some may say that comparing this to say, the usage of serial commas, isn’t valid as there is obviously a right way to do it. However, Grammar Girl points out what I already knew: this is a style choice.

Hrm, I seem to have started chasing rabbits.

Despite not being an English major, I still enjoy writing. I do so for fun in games I play and have been struggling on that “novel” I’ve been wanting to start writing. After reading Scott’s words, I decided that there really was no time like the present to start sharing what I’ve learned in “Real Life” (hah!), and write up my thoughts and observations on programming, software quality assurance, some cooking, some Crossfit, and maybe a little bit of music.

All of this ties together in “The Resplendant Developer”; apologies to Lynn Rosetto-Casper, host of “The Splendid Table”.

I should also apologize to the great Scott Hanselman for stealing his idea and approach to trumpet the ramblings of my brain in beautiful Austin, Texas.