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

In a recent post I postulated that given the current recession and turbulent economic state, the good developers are staying in their current role rather than interviewing for a new position. This was only really guess work however, so I thought it would be useful to conduct a little market research. I decided to have a few conversations with companies looking to hire and recruitment agents to collate some information.

We recently produced quite a basic job specification with pretty standard requirements, a competitive wage with benefits and asked for several years of relevant experience. Having looked at all of the various job postings I would have thought there would be a wealth of talent looking for an opportunity like this. We were looking for around ten resumes to sift through as a starting point.

The number of available candidates and skills is a very difficult ask at the moment. There are simply not a huge number of candidates available with the required skill set. There isn’t just one problem; the number of candidates and skills are both big problems at the moment, there isn’t enough of either.

Our first thought was that wage might be an issue, so what if we were will to increase the wage offering?

Wages aren’t really the issue. It’s quite a general problem that people are unwilling to move jobs right now. The economic state is not conducive towards it. People just don’t know if the company is going to be around next month, or if they’ll be forced to cut costs. If Microsoft and Sun are having to do it, then who will be next?

But surely if there are scores of people being made redundant there should be talent available for hire.

Typically, most of the people being made redundant on the news aren’t highly skilled technical types. Some of them are but the majority are unskilled. Of those technical staff looking for work, many of them are contract staff who haven’t reach the point of really having to work yet. Some of them need to work, but many of them can “play it cool” for a while yet.

So is the industry really in crisis, or is it actually faring quite well at the moment? Are there tens of thousands of skilled developers out there struggling to find work, or just a few more than usual? I’m sure there must be a large number of skilled people who have recently lost their job, but if so where are they and why don’t they seem to be looking for work?