Thursday, January 30, 2014

Land Registry December 2013 data on doogal.co.uk

I’ve imported the latest Land Registry to doogal.co.uk. As ever, little can be inferred from a single month of data but draw your own conclusions. And let me know if you’d like to see this data in some other way.

Tuesday, January 28, 2014

doogal.co.uk is more mobile friendly

I’ve been trying to ignore mobile devices for a long time. Although doogal.co.uk has always been available on mobile phones, so long as you were happy to squint and zoom in and out a lot, it’s never been particularly useable. I’d kind of hoped that as more people started browsing the web from their phones, the phones would get bigger screens and I’d get away without doing anything. Whilst some of the phones have got bigger, it seems a lot of people don’t want to talk into a phone the size of a paperback book. Understandable really, they don’t want to look like dicks.

On top of that, Google keeps telling me my site isn’t mobile friendly and it’s got to a point where a significant percentage of my visitors are using small devices to access the site, so I figured it was time to bite the bullet and fix it. So now if you visit from a smart phone, chances are you’ll see the more mobile friendly version of the site. In fact if you squidge your desktop browser to a small enough size, you’ll also see the mobile layout. Of course, just like the regular desktop site, it’s butt ugly but there is a reason why web design doesn’t appear on my CV!

But if you spot anything broken, please let me know in the comments or email.

Saturday, January 18, 2014

Improving the performance of MySql bulk inserts

When I was trying to improve the performance of my old server, I came across this page. Unfortunately it didn’t help with the old server and just made me realise I needed to upgrade my server hardware.

But once I got my new hardware, I wanted some tweaks to improve the performance of importing postcode and property sales data. Both were taking days to complete. One suggestion from that page was to use SET autocommit=0;. I was a little sceptical, but was willing to try anything to speed up the import.

So all I did was add the statement on the start of every insert and add a counter to my code. After 1000 inserts, I committed the changes, using the following

              if (count % 1000 == 0)
              {
                command.CommandText = "COMMIT";
                command.ExecuteNonQuery();
              }

And this made a huge difference to my imports. Whereas I was importing for days before, now the time taken was down to hours. Maybe I could improve it further by committing the changes even less frequently, but I’m pretty happy with the current situation.

So in conclusion, if you need your data imports to run quicker, start using SET autocommit=0; now!

Thursday, January 02, 2014

November 2013 Land Registry data

I’ve uploaded the latest Land Registry property sales data to my website, covering November 2013. As always, not a lot can be inferred from a single month’s data, but drilling down to individual postcode areas shows fairly different stories for prices and sales over the long term.