Embedded databases are proving pretty popular with many teams finding their usage extremely appealing. Many embedded databases have impressive features; easy to integrate into the software, low administration requirements, very low footprint and generally extremely fast. It is for this last reason that some people look to these databases, but it is also for this reason that they need to be handled with care.

Just like OpenOffice, a previous project I worked on used an embedded version of HSQLDB. We had started to have several performance problems so engaged in some profiling to find potential areas for improvement. After staring at several screens full of SQL debug, one thing became apparent; our system was producing a huge number of SQL statements. This was accompanied by the realisation that it was also producing a huge number of exactly the same SQL statements.

As the embedded database was extremely fast, there had never been any performance problems before. In fact the database was not the bottleneck in many of the application operations. As the application grew more complex however, performance problems began to arise quite quickly. These problems only got worse when the numbers started to increase. Complexity + Scaling != Success. The extemely fast embedded database was now starting to be a real problem, some serious changes needed to be made.

Many of the database performance problems were resolved quite quickly by simply re-learning the most basic best practices, just because the embedded database was quick these should not have been ignored;

• Connection pooling can improve performance
• Statement caching can improve performance
• Batching statements can improve performance
• Performing SQL n + 1 selects are a bad idea regardless of how quick the database is
• Caching common SQL results can improve performance
• The database needs to be profiled to see exactly what SQL is getting executed against it

It may be possible to ignore these issues early in the project, but as the complexity increases and the application is expected to scale they can begin to be exposed. Embedded databases performance can be impressive, but make sure you handle with care.

Success! We’ve just released a new version of our software, this calls for a celebration. All the team march down to the local bar for a well earned beer or an orange juice for the designated driver. But what’s that annoying noise in my jacket pocket, my mobile phone’s ringing and it’s the boss. Some customers have just upgraded and now they are reporting serious problems with their systems, looks like we better get ready to play the blame game.

For those people who have never played the blame game before, we need to set out the rules. The rules are actually quite simple, if in doubt seek the advice of a seasoned player. The first rule of the blame game is, it’s never your fault. The second rule of the blame game is, it’s never your fault. The third rule of the blame game is, it’s always some one else’s fault. So now we’ve established the rules, we need to work out some tactics to work out whose fault it actually is.

The first candidate for blame always has to be the last person to leave the company. Anything that’s wrong can easily be blamed on them, after all they can’t defend themselves so they make an ideal target. The next candidate can be chosen from any of the temporary staff you currently employ, the shorter the contract the better. If you aren’t going to have to see these people for much longer, this should work out quite nicely. The next candidate is anyone that’s working for the company in another location (if it’s another country this works even better). You might have to work with these people again, but you’re probably not going to see them on a daily basis, it shouldn’t be too awkward. Now this is where your choices start to become a little tricky, you’re down to the people that you work with everyday.

You have to keep this simple and align yourselves with the strongest members of the team, after all this is survival of the fittest. Fit yourself nicely into the crowd and look for anyone that doesn’t quite fit, this is real back to basics school ground behaviour. Pick off the weak and vulnerable first as these shouldn’t put up much of a resistance, then work your way up the power hierarchy until you find your patsy. So there you have it, those are the basic tactics for the blame game you might have to tweak as appropriate within your organisation but the general approach should apply.

The most important decision you face when presented with the blame game is do you really want to play? If you decide that it’s really not for you, the best way to deal with it is to simply throw out all of the rules, rip the board in half, stamp your feet as hard as you can and shout out loud, “I refuse to play”. It is often said that; “we work in a blame free culture, until something goes wrong”. Are you really interested in finding someone to blame when something goes wrong? Will it really help the customer solve their problem any sooner if you’ve found the person to carry the can for the failure? In the time it takes to sit there and rack your brains for potential candidates to take the blame, you could already be investigating the cause of the problems and attempting to fix them. In his recent post Sean Landis said;

Successful Agile development presupposes that team members will all act like adults. That’s a euphemism for being competent and professional. Agile teams are expected to accept a high level of responsibility and accountability. When they don’t, things can fall apart really fast.

Regardless of how bullet proof a process claims to be, there are always going to be issues that weren’t found prior to the release. If issues are always going to occur, should teams really be allowed to turn on each other in response to them, will this really lead to a better working relationship? The most important thing is that problems are dealt with promptly and professionally when they arise. Instead of trying to assign blame, people should be responsible for their own actions, they need to learn from the experience and measures should be put in place (if possible) to prevent this happening again. Next time your team is out celebrating your latest release and your mobile phone starts to ring, do you really want to start playing the blame game?

The BBC reported on Tuesday that the recent disruptions caused by the heavy snowfall could cost UK businesses about £1bn (or $1.4bn as of 05/02/2009). It was also estimated that about 20% of the UK’s working population, or 6.4 million people, were absent from work. The bad weather has continued this week and is forecast to continue into the weekend. With the huge potential loses to the economy how can technology help?

Currently the roads are covered in snow, buses, trains and planes are cancelled and I’m stuck at home, and I’m costing my employer money. Some employers might expect you to turn up regardless of the weather. Some employers might take the “snow day” out of of next months wages. Some employers provide the means to let their employees work from home. It is obviously not logistically possible for every business to allow working from home, but what about the ones that can? In a quote from the BBC report, Keith Tilley said;

“Employers need to do everything they can to allow staff to work from home and ensure that those that do manage to travel to work are well looked after, with overnight accommodation if necessary.”

Many businesses are turning to technology to allow their employees to not only work from home during periods of bad weather but also during peak demand. These virtual call centres are proving extremely popular with more businesses turning to this technology to provide a more permanent solution. Many businesses could benefit from this approach but lack the technology to actually make it possible. Is this technology too expensive, too complicated or is there simply a lack of knowledge about it’s usage?

A quick search in Google yields a range of solutions that can make working from home very possible, one of the best is a VPN. At one end of the spectrum is OpenVPN which retails at £0 (or $0). It’s open source; download it, install it, configure it and you’re ready to go. There is obviously an associated learning curve, but for most people with a technological persuasion it should be quite straight forward. If you don’t want to have to install and maintain software, another top result is Barracuda SSL VPN which starts to retail at £1250 (or $1800). It’s an appliance; turn it on, configure it and you’re ready to go. Both solutions provide the means to be virtually on the network without having to be physically in the office. Access can be provided to email, file servers, internal Web sites and office workstations. As a developer I can access CVS, SVN, our Maven repository, Hudson build system and any other resource I use during my normal working day. I can be just as productive (often more so) as if I’m really in the office.

There are a whole range of products that allow employees to work from home, but there really should be something to suit every businesses budget. Some businesses worry about letting employees work from home, but even if the technology is only used for extreme or emergency purposes, at least a business can suffer near zero downtime. Even if the technology is only used once or twice a year, it can still provide a return on investment for the smallest of companies. We currently have six team members connected to our SSL VPN, our business is counting the cost of the bad weather, but fortunately for us it should be somewhere near £0 ($0).

The SQL n + 1 selects problem is extremely common but I have often found that many people have either never heard of it or simply don’t understand it. It is actually very easy to introduce a problem like this into your code, but it’s also very easy to resolve as well. Problems like this are best explained with an example; so imagine we have a table called users and another called user_roles. These tables are setup with a one-to-many relationship, meaning that one user (e.g. jsmith) can have many roles (e.g. Administrator, Auditor, Developer). Many people might implement something like this;

public Iterable<User> allUsers() {
    final String selectUsers = "select users.username, users.email, " +
        "users.last_password_change from users";
    return getJdbcTemplate().query(selectUsers, new Object[] {},
                new ParameterizedRowMapper<User>() {
        public User mapRow(ResultSet resultSet, int rowNumber) throws SQLException {
            String username = resultSet.getString("username");
            String email = resultSet.getString("email");
            Date lastPasswordChange = resultSet.getDate("last_password_change");
            User user = new DefaultUser(username, email, lastPasswordChange);
            addRolesToUser(user);
            return user;
        }
    });
}

private void addRolesToUser(final User user) {
    final String selectUserRoles = "select role_name from user_roles where username = ?";
    getJdbcTemplate().query(selectUserRoles, new Object[] { user.getPrincipalName() },
                new RowCallbackHandler() {
        public void processRow(ResultSet resultSet) throws SQLException {
            String rolename = resultSet.getString("role_name");
            user.addRole(rolename);
        }
    });
}

Reviewing the code we can see one query is executed to retrieve the users, the problem here is for each user another SQL statement needs to be executed to retrieve the roles. If the first query retrieved one user, this would require one additional query to retrieve the roles. If the first query retrieved a hundred users, this would require one hundred additional queries to retrieve the roles. The pattern will always be the same, one query for the users and n queries dependent on the number of users found, thus n + 1. Although this solution is functional, it does result in many unnecessary SQL statements being executed.

select users.username, users.email, users.last_password_change from users;
select role_name from user_roles where username = ?;
select role_name from user_roles where username = ?;
select role_name from user_roles where username = ?;
...

Shared resources are typically the bottleneck in most applications, so expensive or unnecessary SQL should be avoided if possible. As the application attempts to scale, this bottleneck can become extremely problematic and severely inhibit application performance. Fortunately this is a simple solutions to this problem; introducing a join into the query.

public Iterable<User> allUsers() {
    final String selectUsers =
        "select users.username, users.email, users.last_password_change, user_roles.role_name "
            + "from users left join user_roles on (users.username = user_roles.username)";
    final Map<String, User> users = new HashMap<String, User>();
    getJdbcTemplate().query(selectUsers, new Object[] {}, new RowCallbackHandler() {
        public void processRow(ResultSet resultSet) throws SQLException {
            String username = resultSet.getString("username");
            if (!users.containsKey(username)) {
                String email = resultSet.getString("email");
                Date lastPasswordChange = resultSet.getDate("last_password_change");
                User user = new DefaultUser(username, email, lastPasswordChange);
                users.put(username, user);
            }

            String rolename = resultSet.getString("role_name");
            if (!StringUtil.isNull(rolename)) {
                User user = users.get(username);
                user.addRole(rolename);
            }
        }
    });
    return users.values();
}

Although the code and SQL statement are slightly more complex that the original example, it results in much fewer SQL statements being executed. Instead of the n + 1 statements executed in the first example, one statement is executed that fetches all the required data. This typically results in much improved performance and as the numbers scale the improvement in performance can become much more apparent.

select users.username, users.email, users.last_password_change, user_roles.role_name
    from users left join user_roles on (users.username = user_roles.username);

As with all performance optimizations the most important thing is to measure the effect of the improvement. Performance optimizations aren’t always predictable so by taking measurements before and after the change, you can accurately know if you have actually improved the performance (or made it worse). A SQL join may be the most appropriate way of solving a problem such as this, but there are other alternatives such as caching the data instead. Although the SQL n + 1 selects is an extremely common problem, is not always well understood and is often still found within code. It is very easy to introduce a problem like this into your code, but it’s also very easy to resolve as well. Next time you are viewing your debug output, see if you can spot SQL n + 1 selects.

References

Database access using Spring JdbcTemplate
Preventing the n + 1 select problem when using Hibernate

Hiring new staff can be a long and drawn out process, at the end of which you hope you’ve found the right candidate. Vetting resumes, collating a list of potential candidates, telephone screening and then eventually bringing them in for an interview…….. so what’s the plan? This is the most important decision you’re going to make about your interview process; do you give them the list of technical questions, some example code or should you include the the rubik’s approach?

The List of Technical Questions

The candidate is presented with a list of technical questions that start with basic questions and slowly move towards more difficult ones. These could be about language specifics, API’s they claim to know or anything technical that is related to their potential position. Anyone with a basic knowledge of development principles stands a good chance of getting a reasonable score with the basic questions. Most people can memorise answers to the general technical questions, but does that really give you an insight into their ability?

The Example Code Test

The candidate is asked to write some general purpose code or possibly something resembling code they might be expected to work on. Anything general should be quite straight forward for the candidate, but anything that expects them to write code to specific API’s could produce undesirable results. If the candidate has claimed to have a good working knowledge of an API they have no excuse, but if they didn’t use the API yesterday, last month or ever, should that really sway your hiring decision? Is this candidate really better or worse than the one before?

A Different Way?

Joel Spolsky keeps his criteria for hiring staff quite simple; smart, and gets things done. If we approach hiring with such simple criteria; development is about solving problems and a good developer needs to excel at this regardless of their chosen language. They need a natural aptitude to understand a problem, break it down and arrive at a a solution. Presenting a candidate with technical questions or example code rarely tests those natural problem solving abilities in any great deal.

Rubik’s Research

A recent batch of company branded merchandise contained a single rubik’s cube. Over the course of a couple of months, the rubik’s cube was passed around the office, each member of the team having differing degrees of success. One team member was a rubik’s cube wizard, spinning and flicking the squares around to complete the puzzle in what seemed like seconds. This team member also happens to be exceptional at their job and has amazing problem solving skills. This team member is not a developer, but I have no doubt that if they decided to turn their hand to it, they would be an exceptionally productive one.

Some of the other team members just couldn’t break the problem down and struggled to find the patterns that advanced the puzzle. Even after training from the rubik’s cube wizard and written instructions on how to solve the puzzle, some team members still couldn’t progress from the jumbled mess. Some of these individuals could be classified as average (not exceptional, but not bad) developers and this puzzle really seemed to highlight the distinction.

The rubik’s cube is only one example of a problem solving challenge (some would argue one of the hardest), but even when supplied with the answers it still provides a good challenge. Fan’s of the classic game show Krypton Factor might already have an idea of the kind of challenges a candidate could undertake; from the impossible to the absurd. The idea here is simply that by augmenting a normal interview with a puzzle element, it may add some insight into the candidates puzzle solving approach.

Conclusion

The difference between average, good and excellent developers can often be traced back to their aptitude to solve basic problems. If team members are given the solution to problems but still can’t progress further, does this give us an insight into their general analytical approach? Problem solving skills can be taught to some degree, but does the rest just come naturally, is there only so much you can teach? Typical interviews often only touch on this ability and don’t look at it from a pure approach.

Puzzles like the rubik’s cube are a great way to test an individuals problem solving abilities, potentially putting them on a level playing field. These kind of puzzles force individuals to look for patterns, understand the process and apply it; after all isn’t that what development is all about? Next time you have a candidate in for an interview, should you include the the rubik’s approach?

Note: I have tried to find more information on this subject but as yet I’ve found very little real research. I’d be interested to hear about the links between problem solving and programming ability.