Don't ignore your database, use it

Programmers beware - you will suffer serious performance problems if you ignore the database and do too much data manipulation in the code.

A little while ago, we hit a few performance problems with our Metrici Advisor service. The underlying problem is that our database has grown significantly, and that we need to move to more powerful servers.

However, I think it is always a mistake just to throw hardware at performance problems, so I had a look in more detail at the problems.

Most of the problems were straightforward. We needed a few new indexes for the database. We could speed up some of the web pages by using more XSLT, and less other scripting.

By far the biggest problem was how we used the database.

Metrici Advisor is a database-centric application. It has a reasonably complicated database structure. We do not access the database directly from the user interface code. We use a thin layer of Java code to turn a meaningful request into one or more database accesses, and to interpret and return the results. A typical transaction runs a query or two to check permissions, and then a query or two to retrieve results.

To keep the queries simple, we encapsulate some of the commonly used queries, such as permission checking, into reusable methods. These utility routines work well, and simplify the rest of the Java code and database access. However, without thinking through the consequences, I had built complicated code using these methods.

For example, the code which works out what assessment questionnaires someone may use needs to look at licenses, permissions, and versions. To keep this simple, I had split it down in the code. The code looked at all the licenses for the user, then all questionnaires permitted by the license, then version information, and so on.

It was only a few lines of Java code, and relatively easy to follow. It worked fine in most cases. But this sort of code does not scale. The system almost ground to a halt for one of our more active users, who has many separate licenses, because all the code was being repeated for each license.

To fix the problem, we moved all the data access and all the logic to where it belongs - the database. The database is the correct place to select the right data, to merge it, to sort it, to apply business rules. We developed one big query, rather than lots of little queries joined together with code.

The root of the problem is that we mixed different design approaches. We could have built an object-oriented system, with objects that represent the concepts of the system and which efficiently manage their own data access. But we decided instead to build a database application with simple procedural code.

Either approach could work, but a mix does not. We should not have tried to build complicated data access from simple building blocks and expect it to work efficiently, without having put the engineering effort into the building blocks in the first place.

We have to accept the opportunities and limitations of the database-centric design, and build the system accordingly. We have to use the database, not ignore it.

© Copyright 2008 Minimal IT Ltd. See the Minimal IT website for the original newsletter and copyright information.

The next Minimal IT newsletter is planned for 13 January 2008. Have a great holiday. Andrew Clifford.

Dipping my toe in the clouds

Although it is still early days for cloud computing, offerings such as Amazon's Elastic Computing Cloud (EC2) are becoming viable for mainstream use.

We need to upgrade the servers that run our Metrici Advisor service. One option we have been considering is cloud computing, which lets you consume what you need from a "cloud" of computing power. It is tempting for small companies because it combines low startup costs with huge scalability. (The 3Tera website provides an excellent Forrester report on cloud computing, registration required.)

We looked briefly at some of the main players, to see who could provide the equivalent of the servers that we currently use. Most vendors are not there yet, or only offer servers for specific types of web application, rather than providing general-purpose servers.

For example, Google's offering is currently very limited around their technologies. The major hosting provider RackSpace acquired Mosso for its cloud computing. But if you look closely, their "cloud servers" are still in "active development".

We did find one large, generally available, general-purpose cloud computing provider that caters for entry-level needs: Amazon (the people who sell books).

Amazon's cloud computing is branded the Amazon Elastic Compute Cloud (EC2). EC2 allows you to rent virtual servers in the cloud, starting at $0.10 per hour. It can be used alongside their other offerings, particularly their Simple Storage Service (S3) data storage. It provides support for many different operating systems, including both Windows and Linux.

Amazon EC2 is initially daunting, but commissioning new servers always is. You use an Amazon Machine Image (AMI) as a template for your new server, and add your own software and configuration. You can save your configured server as a new AMI, and create more server instances from that. There are options for setting network connectivity, including assigning IP addresses and firewall options. All this is done from your PC using a command line tool.

There are some differences between EC2 and running your own server. The entire server - including all "disk" storage - is deleted when the server is stopped (reboots are OK). There are options to copy data to Amazon S3, or to use the Amazon Elastic Block Store (EBS), which acts as persistent disks for your server.

I'm not much of a systems administrator, but it only took me a couple of hours to create a server and recreate the Metrici Advisor service on this. The new server performed very well, and I can see how the service could be used to build an effective platform for Metrici Advisor.

From what I have seen, Amazon EC2 would work very well indeed for its main targets: organisations that need to commission multiple servers quickly to support large web sites. It would be particularly useful for temporary server needs - such as testing, training, or major data conversion exercises. You could run a seriously large installation using this type of infrastructure, without the cost and delay of providing your own servers.

Although cloud computing is still in its infancy, my little paddle into it has shown me that it can be cheap, flexible and effective. I think it is a technology that is becoming viable for mainstream use.

© Copyright 2008 Minimal IT Ltd. See the Minimal IT website for the original newsletter and copyright information.

Measuring the strategic contribution of IT

You can measure the strategic contribution of an IT system by thinking about two separate things: the strategic contribution of the work supported by the system, and the contribution of the system to the work.

To manage effectively, you need to focus on the important parts of IT, and take issues and risks more seriously in those areas. If you know which parts of your IT are less important, you can safely treat those areas as a lower priority.

Ideally, you want to know how much each IT system contributes to your organisation's strategy. But measuring the strategic contribution of IT is hard. Very few IT systems have a direct financial benefit, and even where they do, financial benefit is only one part of strategy. We often end up with simplistic measures - he who shouts loudest, or the systems owned by the most senior people.

At Metrici, we were faced with this problem when assessing systems at a health care organisation. We needed to adjust the priority of our recommendations to reflect the contribution of the systems to the organisation's strategy.

To achieve this, we asked a few simple questions about each system.

We asked what tasks the system supports - not the functionality of the system, but the human or business activity that the system supports.

We asked how much this task contributes to each of the areas of the organisation's strategy, such as patient care, compliance, finance, risk, and so on. These were not IT questions, but questions about the contribution of the work to each area of the strategy. We gave broad classifications, based on an estimate of the percentage of that strategic objective achieved by the task. The classifications we had were: none; minor (less than 2%); contributing (2% to 5%); major (5% to 10%); highly significant (10% to 20%); and fundamental (20% or more).

Lastly, we asked how much the task required the IT system. We used three grades: always used to perform the majority of the task; always used to complete the task; and sometimes used to complete the task. We made it clear that we were considering the system under normal circumstances, not what the organisation would do if it did not have the system.

We then took the highest task contribution, and cross referenced it to the depth of use of the system to get the overall strategic contribution of the system, using the table below.



Although far from perfect, this is a very practical method. When used across many systems, it gives a quick and defendable indication of the relative contribution of each. The model can be tuned and made more sophisticated, as you need. (One obvious area would be to add up the different strategic values - we just took the highest.)

The key to this approach is to recognise that IT is not of itself important. IT is only important because it helps the organisation perform a task that contributes to the organisation's strategy. Splitting the question into two - the strategic contribution of the work supported by the system, and the contribution of the system to the work - makes it much easier to estimate the strategic contribution of IT.

© Copyright 2008 Minimal IT Ltd. See the Minimal IT website for the original newsletter and copyright information.

How to write easily and effectively

Writing regularly makes writing much easier, helps you think more clearly, and helps you get your ideas across more effectively.

Many writers on the web say that they write because they want to give something back to the community. I am more selfish than that. I write because writing helps me.

• Writing helps me understand things better. If I do not understand something, I write out the little that I do understand, and build on that one small step at a time. My powers of memory and abstract thought are limited. Writing things down is how I think things through.
• Writing frequently helps me communicate more clearly. I used to get frustrated that my colleagues didn't understand me. The more I write, the better I get at explaining things.
• Writing helps me be more confident. I am not a naturally confident person, and I get flustered by difficult questions. Writing about a subject, exploring all the questions I might get asked, helps me be more confident.
• Writing is the best way to get better at writing. We all have to write, whether for long business reports or just a note to your children's teacher. Just like physical exercise, if you keep exercising your writing muscle, all types of writing get easier.
• Writing gives me a voice. Writing lets me put forward my views and contribute to the debate.

To get writing to work for me, I have adopted some rules.

• Write regularly. To force myself to write regularly, I publish this newsletter to a strict timetable and word count. Knowing that other people will read the newsletter keeps me writing. A strict time table and word count gives me the focus I need to finish each newsletter.
• Write about anything. There is no such thing as a bad subject. Writing is about teasing ideas out of the mind, one step at a time. I write about whatever I am thinking about.
• Write properly. Write in full sentences. Be careful with spelling, punctuation and grammar.
• Be clear. Being clear is much more important than being correct. It is better to be wrong, but to be clear enough that your readers can see the error, than to be right, but so obscure that your readers can not understand you.
• Always start with a summary. Writing a summary helps me focus. It is also a courtesy to my busy readers, who can tell at a glance whether the newsletter will interest them.

Writing helps me a lot. If you are like me - if you can not understand or explain your situation, if nobody seems to share you views, if you are not confident, if you find writing hard work - then writing can help you too. Do not worry that nobody is interested in what you write, or that people might disagree, or that you have no style. All these things come after you start writing, not before.

Writing regularly may not make you the world's greatest writer. But it will help you think more clearly, write more easily, and get your ideas across more confidently.

© Copyright 2008 Minimal IT Ltd. See the Minimal IT website for the original newsletter and copyright information.