Freight trains are now on the site, albeit with the headcode masked out in schedules, and with a scrambled placeholder on the real-time maps.

As I predicted, this resulted in a massive spike in traffic to the site which has all but disappeared as most people are using other sites such as www.realtimetrains.co.uk. Although it doesn’t have all the polish of OpenTrainTimes, it real-time information and is fast.

So why isn’t OpenTrainTimes as fast as RealTimeTrains?

There are two reasons.

First, the database format I use is robust and mature, but isn’t as fast as I want it to be. I’m in the process of re-engineering the database to be much quicker – something I hoped to launch today, but as I have a full-time consultancy job and an awesome lady in my life, I don’t have much time during the week to sit down and write code. I also underestimated the amount of time it would take to convert the existing database, so I’m running a bit behind schedule.

The new database format is denormalised – something that makes it quick to read, but slightly more difficult to update unless you’ve architected your system properly. Whilst testing, I found I could bring up all the trains at Clapham Junction in a single day in around 2 seconds. It takes more than 30 seconds to do this in the current system. Good news!

I’m hoping to launch the restructured, quicker database, some time this week (possibly next Sunday) once I’ve made sure everything works better than it currently does.

The second reason is that the amount of money I spend on hosting the site is small. The reason www.realtimetrains.co.uk is lightning fast is due, in part, to the fact it’s running on a system that is many times more powerful than I have at my disposal.

I could throw money at the problem, but I have rent, bills and savings. I could introduce adverts on to the site, but given the user base, few people are likely to click them and they’d make the site look ugly – something I’ve tried to avoid. Or I could ask people to donate, or subscribe – but this changes the game entirely and means that I’m obliged to provide a certain level of service.

What’re you going to do?

I’m pinning my hopes on the restructured database making things faster – which means I can work on putting more real-time data on the site. However, if it doesn’t speed things up, then I’m considering throwing in the towel and just showing statistics and real-time track maps. There’s not much point in having the largest and most visibile part of the site being the slowest and most resource intensive!

What else are you planning

After the new database is in place, and if it’s fast enough, I’ll be able to launch a bunch of new features such as email and instant message alerting, tracking trains in real-time and being able to click on the signalling maps to bring up schedule information. Just about everything I want to do relies on having a fast timetable database underneath the site, so it’s a critical thing to get right.

I’ve also got some other ideas up my sleeve which aren’t ready for the public eye yet, as they involve data which isn’t yet open and available to the public.

So – fingers crossed and let’s everything goes to plan!