Robert Chipperfield

Accessing SQL Server data from iOS apps

2011-01-14T07:00:00Z

Almost all mobile apps need access to external data to be valuable. With a huge amount of existing business data residing in Microsoft SQL Server databases, and an ever-increasing drive to make more and more available to mobile users, how do you marry the rather separate worlds of Microsoft's SQL Server and Apple's iOS devices?

The classic answer: write a web service layer

Look at any of the questions on this topic asked in Internet discussion forums, and you'll inevitably see the answer, "just write a web service and use that!". But what does this process gain?

For a well-designed database with a solid security model, and business logic in the database, writing a custom web service on top of this just to access some of the data from a different platform seems inefficient and unnecessary. Desktop applications interact with the SQL Server directly - why should mobile apps be any different?

The better answer: the iSql SDK

Working along the lines of "if you do something more than once, make it shared," we set about coming up with a better solution for the general case. And so the iSql SDK was born: sitting between SQL Server and your iOS apps, it provides the simple API you're used to if you've been developing desktop apps using the Microsoft SQL Native Client.

It turns out a web service remained a sensible idea: HTTP is much more suited to the Big Bad Internet than SQL Server's native TDS protocol, removing the need for complex configuration, firewall configuration, and the like.

However, rather than writing a web service for every app that needs data access, we made the web service generic, serving only as a proxy between the SQL Server and a client library integrated into the iPhone or iPad app. This client library handles all the network communication, and provides a clean API.

OSQL in 25 lines of code

As an example of how to use the API, I put together a very simple app that allowed the user to enter one or more SQL statements, and displayed the results in a rather primitively formatted text field. The total amount of Objective-C code responsible for doing the work? About 25 lines.

You can see this in action in the demo video.

Beta out now - your chance to give us your suggestions!

We've released the iSql SDK as a beta on the MobileFoo website: you're welcome to download a copy, have a play in your own apps, and let us know what we've missed using the Feedback button on the site.

Software development should be fun and rewarding: no-one wants to spend their time writing boiler-plate code over and over again, so stop writing the same web service code, and start doing exciting things in the new world of mobile data!

.NET to iOS: From WinForms to the iPad

2010-12-01T07:00:00Z

One of the great things about working at Red Gate is getting to play with new technology - and right now, that means mobile. A few weeks ago, we decided that a little research into the tablet computing arena was due, and purely from a numbers point of view, that suggested the iPad as a good target device.

A quick trip to iPhoneDevCon in San Diego later, and Marine and I came back full of ideas, and with some concept of how iOS development was meant to work. Here's how we went from there to the release of Stacks & Heaps, our geeky take on the classic "Snakes & Ladders" game.

Step 1: Buy a Mac

I've played with many operating systems in my time: from the original BBC Model B, through DOS, Windows, Linux, and others, but I'd so far managed to avoid buying fruit-flavoured computer hardware! If you want to develop for the iPhone, iPad or iPod Touch, that's the first thing that needs to change.

If you've not used OS X before, the first thing you'll realise is that everything is different! In the interests of avoiding a flame war in the comments section, I'll only go so far as to say that a lot of my Windows-flavoured muscle memory no longer worked.

If you're in the UK, you'll also realise your keyboard is lacking a # key, and that " and @ are the other way around from normal. The wonderful Ukelele keyboard layout editor restores some sanity here, as long as you don't look at the keyboard when you're typing.

I couldn't give up the PC entirely, but a handy application called Synergy comes to the rescue - it lets you share a single keyboard and mouse between multiple machines. There's a few limitations: Alt-Tab always seems to go to the Mac, and Windows 7's UAC dialogs require the local mouse for security reasons, but it gets you a long way at least.

Step 2: Register as an Apple Developer

You can register as an Apple Developer free of charge, and that lets you download XCode and the iOS SDK. You also get the iPhone / iPad emulator, which is handy, since you'll need to be a paid member before you can deploy your apps to a real device.

You can either enroll as an individual, or as a company. They both cost the same ($99/year), but there's a few differences between them. If you register as a company, you can add multiple developers to your team (all for the same $99 - not $99 per developer), and you get to use your company name in the App Store. However, you'll need to send off significantly more documentation to Apple, and I suspect the process takes rather longer than for an individual, where they just need to verify some credit card details.

Here's a tip: if you're registering as a company, do so as early as possible. The approval process can take a while to complete, so get the application in in plenty of time.

Step 3: Learn to love the square brackets!

Objective-C is the language of the iPad. C and C++ are also supported, and if you're doing some serious game development, you'll probably spend most of your time in C++ talking OpenGL, but for forms-based apps, you'll be interacting with a lot of the Objective-C SDK.

Like shifting from Ctrl-C to Cmd-C, it feels a little odd at first, with the familiar string.format(.) turning into:

NSString *myString = [NSString stringWithFormat:@"Hello world, it's %@", [NSDate date]];

Thankfully XCode's auto-complete is normally passable, if not up to Visual Studio's standards, which coupled with a huge amount of content on Stack Overflow means you'll soon get to grips with the API.

You'll need to get used to some terminology changes, though; here's an incomplete approximation:

Coming from a .NET background, there's some luxuries you no longer have developing Objective C in XCode:

Generics! Remember back in .NET 1.1, when all collections were just objects? Yup, we're back there now.
ReSharper. Or, more generally, very much refactoring support. The not-many-keystrokes to rename a class, its file, and al references to it in Visual Studio turns into a much more painful experience in XCode.
Garbage collection. This is actually rather less of an issue than you might expect: if you follow the rules, the reference counting provided by Objective C gets you a long way without too much pain. Circular references are their usual problematic self, though.
Decent exception handling. You do have exceptions, but they're nowhere near as widely used. Generally, if something goes wrong, you get nil (see translation table above) back. Which brings me on to.
Calling a method on a nil object isn't a failure - it just returns nil itself! There's many arguments for and against this, but personally I fall into the "stuff should fail as quickly and explicitly as possible" camp.

Less specifically, I found that there's more chance of code failing at runtime rather than getting caught at compile-time: using the @selector(.) syntax to pass a method signature isn't (can't be) checked at compile-time, so the first you know about a typo is a crash when you try and call it.

The solution to this is of course lots of great testing, both automated and manual, but I still find comfort in provably correct type safety being enforced in addition to testing.

Step 4: Submit to the App Store

Assuming you want to distribute to more than a handful of devices, you're going to need to submit your app to the Apple App Store. There's a few gotchas in terms of getting builds signed with the right certificates, and you'll be bouncing around between XCode and iTunes Connect a fair bit, but eventually you get everything checked off the to-do list, and are ready to upload your first binary!

With some amount of anticipation, I pressed the Upload button in XCode, ready to release our creation into the world, but was instead greeted by an error informing me my XML file was malformed. Uh. A little Googling later, and it turned out that a simple rename from "Stacks&Heaps.app" to "StacksAndHeaps.app" worked around an XML escaping bug, and we were good to go.

The next step is to wait for approval (or otherwise). After a couple of weeks of intensive development, this part is agonising. Did we make it? The Apple jury is still out at the moment, but our fingers are firmly crossed!

In the meantime, you can see some screenshots and leave us your email address if you'd like us to get in touch when it does go live at the MobileFoo website.

Step 5: Profit!

Actually, that wasn't the idea here: Stacks & Heaps is free; there's no adverts, and we're not going to sell all your data either. So why did we do it?

We wanted to get an idea of what it's like to move from coding for a desktop environment, to something completely different.

We don't know whether in a year's time, the iPad will still be the dominant force, or whether Android will have smoothed out some bugs, tweaked the performance, and polished the UI, but I think it's a fairly sure bet that the tablet form factor is here to stay.

We want to meet people who are using it, start chatting to them, and find out about some of the pain they're feeling.

What better way to do that than do it ourselves, and get to write a cool game in the process?

Coding By the Sea: The story of SQL Search

2009-11-10T07:58:00Z

SQL Search is a new tool from Red Gate that allows you to search SQL Server database schemas, instantly locating objects. This is the story of its development.

Back at the start of October, Neil Davidson, one of Red Gate's CEOs, posted a request along these lines on our internal forum:

We're going to rent a house (probably on the Suffolk coast, far away enough to get some distance from the office but near enough to be convenient) for a week, fill it with a project team, give them a project and see what they can achieve in a week. I'm looking for:

- 2 developers
- 1 tester
- 1 UX
- 0 project managers

If you fit the bill, have a sense of adventure, are willing to try something new and are free from Sunday November 1st to Saturday 7th November then send me an e-mail. We'll expect you to work fairly solidly for the extended week. This isn't a 9-5 thing.

You will be living in the house, and you can't bring partners. In return, we'll pay all your expenses (including a small beer allowance), you'll get to work with people you might not have worked with before, in a way you haven't worked before, and produce something cool. It'll be fun.

Chapter 0 – The Prequel

Well – there’s an offer you don’t get every day! After the team was announced (Alex, Dom, Nagashree and myself), we had a quick chat to decide on what project we were going to attack. Clearly this was an important decision: we wanted something sufficiently interesting that it’d produce a worthwhile result, but at the same time, have some chance of being achievable in the week We settled on a search tool for SQL Server, with the aim of being able to release it as a free add-in for SQL Server Management Studio, trying to eliminate some of the pain points with locating and navigating around objects in a database’s schema.

Sunday 1^st November quickly came round, and we set about packing the cars. I would have love to have seen the look on the Security Guards’ faces watching five computers, about ten TFTs, countless post-it notes, a whiteboard, and two office desks leaving the building mid-afternoon on a Sunday. I guess they must be used to us by now…

A couple of hours’ drive later, we made it to our accommodation for the week, and what a sight it was. A beautifully converted barn, big enough to sleep 12, with everything you could want, including table football, wood-burning fire, amazing kitchen, and two of the best chairs I’ve seen, all presented wonderfully. The solid wood dining table was soon turned into our office, though, filled with IT gear. The webcam installation meant Cat5 wrapped around the oak beams, and cable ties on the iron railings, and the enormous chairs turned into office seating for the week.

Chapter 1 – The Planning (Monday, 0900)

Monday morning arrived, and with only five days to go, we needed to rapidly specify exactly what we were going to aim for in the week. The post-its came out, and got distilled into three groups of problems we were trying to solve. These were turned into our sheet of requirements that we had to fulfil if the project was going to be a success. Some were technical, but most were user-driven: “I want to be able to quickly locate a code snippet within my database, and see it in context.”

Knowing roughly what we had to do, Alex and I got down to some prototyping of the more technically challenging bits of the system, integrating into Management Studio and being able to rapidly and efficiently search potentially large database schemas. Dom started putting the sketches we’d all come up with during the planning discussions into something firmer (a great relief, given the quality of my drawings at least), and Nagashree began planning the areas of testing we’d be focussing on.

Chapter 2 – Early Development (Tuesday)

With development underway, tasks were being added to our development backlog (one of the tables we’d bought, standing against a wall), being developed, and moving over to the testing area (nicknamed “Testlog”), and then finally to a “Done” area. Inspired loosely by Scrum, but with an absolute minimum of overhead, we knew exactly when the project was going to complete – Friday night – and rather just needed to make sure we got everything that needed to be done completed in the time.

The first milestones arrived soon after on Tuesday, with an initial algorithm for indexing schemas being completed at the same time as a the first draft of the UI, allowing results to be displayed. Crowded around Alex’s (three…) monitors, we were truly amazed by just how responsive it was – type-down search over AdventureWorks with no noticeable delay at all.

Chapter 3 – Later Development, Testing and Bug Fixing (Wednesday – Friday)

There was still plenty to do for the rest of the week – lots of UI polish, and making incremental updates to the indexes without having to read the whole schema every time you want to search, whilst allowing new objects to show up as soon as they’re added to the database. The latter took somewhat longer than we’d hoped, but nothing that a few late nights and more coffee couldn’t handle.

As we headed into the latter half of the week, we needed to make sure we were going to have something shippable by Friday. We opted for a simple solution: the backlog got a big line drawn across it, and everything that had to be in the product went above the line and things that were merely nice to have went below. We started at the top, and worked down. Sure, we thought of new features we’d like to put in throughout the whole week, but as long as they got added in the appropriate place on the backlog, that was fine.

Chapter 4 – The Final Push

By Friday afternoon, the area above the must ship line was looking reassuringly empty, but there were still a couple of critical bugs and testing remaining. Still, the end was in sight, and we were pretty confident we could make it and still get some sleep before Saturday morning.

At this point we decided to give those back at the office, who’d been watching the webcam and Twitter feed all week, a chance to sink their teeth into the product. Normally this results in a whole load of bugs we weren’t expecting, and no small number of outright crashes. The latter case certainly held true – David Atkinson managed an unhandled exception before he even got as far as searching, but generally feedback was very positive: “Really liking the tool guys - well done. It's not coming off my machine now”; “I love the way that you index everything so the results are seemingly instant, almost working like a filter. Nice one.”

A quick trip to Tesco to pick up some Champagne for later, the last bugs closed, off, and we were ready for a toast. Sadly the joys of Windows Vista meant that by this time it had frozen slightly, so it was more an alcoholic celebratory slush-puppie (apparently better known as a Slurpee or Icee for you guys in the US!), but it was well-received nonetheless.

All that was left was to pack everything up, ready for an early start on Saturday morning, and head back to normality.

Chapter 5 – The Sequel

So, back in the office, lots of people asked how the week went. From the team’s point of view, it was a huge success – we achieved what we set out to, in the time available, and we still don’t want to kill each other despite spending a week together.

Make no mistake – it wasn’t a 40-hour week, and it wasn’t something I could do again this week without a break, but it really was quite outstanding. Weeks and projects like this are what, for me, software development is about: taking a real problem, analysing it, solving it, turning it into a product, and getting it out there. No extraneous meetings. No messing around. No politics. (Not much sleep.)

As for the product, we’re hoping you can be the judge of that yourselves – we’ve got a bug hunt this afternoon, and then we’ll be doing a private beta release, and hopefully a public release shortly afterwards assuming all goes well.

Could you develop all products like this? Probably not. Some are just too large to get anything meaningful done in a week. Could a lot of new products benefit from this kind of approach? Absolutely – as a way of kick-starting a new team, getting everyone really involved in the project, getting some early technical investigation done, or a first EAP out the door, I think this is an amazing kind of opportunity. It probably can’t be done too often, or it won’t have the special feeling that means you don’t mind coding late into the night in pursuit of a challenging goal, but once in a while, it can remind you just why you work in the software industry in the first place.

And so back to our toasts that we had on Friday night: to SQL Search, to Red Gate, and to Neil – thank you.

Cloning objects the quick and dirty way

2009-09-25T09:09:00Z

Working in .NET, a lot of the objects I use implement ISerializable - very handy when it comes to throwing them around using .NET Remoting and the like. In my slightly more evil (or is it lazy?) moments, I tend to "re-purpose" this for a lightweight clone mechanism...

It's quite common when writing test code to be able to do a deep copy of an object - maybe to keep a copy of its state before performing an operation to then compare it again later, or maybe to "subclass" an existing object, overriding a given property with something else. Even preventing certain properties going over the wire or to disk because they aren't required, all without the original object knowing anything of the devious things going on.

The problem is, there's no generic deep copy facility available in .NET (correctly so), and I generally don't want to have to implement IClonable on everything as well. What to do?

Assume I have a couple of little classes, Outer, and Inner, where Outer has a property of type Inner:

    [Serializable]
    class Outer : ISerializable
    {
        public Inner Inner { get; set; }

        public Outer() { }

        public Outer(SerializationInfo info, StreamingContext context)
        {
            Inner = (Inner)info.GetValue("Inner", typeof(Inner));
        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("Inner", Inner);
        }
    }

    [Serializable]
    class Inner : ISerializable
    {
        public Inner() { }

        public Inner(SerializationInfo info, StreamingContext context) { }

        public void GetObjectData(SerializationInfo info, StreamingContext context) { }
    }

My first solution was along these lines - very compact, and seemed to work:

    class ClonedOuter : Outer
    {
        public ClonedOuter(Outer o)
            : base(GetSerializationInfo(o), new StreamingContext ())
        { }

        private static SerializationInfo GetSerializationInfo(Outer o)
        {
            SerializationInfo info = new SerializationInfo(typeof(Outer), new FormatterConverter());
            o.GetObjectData(info, new StreamingContext());
            return info;
        }
    }

The theory went that Outer would serialize its state to the SerializationInfo I provided in GetSerializationInfo, and that would then get passed to the deserialization constructor of Outer via the base(...) call, giving a nicely duplicated object. And it did! But then I hit a problem:

            Outer clone1 = new ClonedOuter(original);
            Console.WriteLine("clone1.Inner == original.Inner: " + (clone1.Inner == original.Inner)); // Prints true!

Whilst the Outer object was being cloned, the Inner one was not - it was simply the same object. The reason for this is hinted at in the documentation: "Objects are reconstructed from the inside out." The GetObjectData of Inner wasn't even being called, let alone being used.

Back to the drawing board. This time, a slightly more "authorized" use of serialization:

        public static Outer Clone(Outer o)
        {
            BinaryFormatter bf = new BinaryFormatter();
            using (MemoryStream ms = new MemoryStream())
            {
                bf.Serialize(ms, o);
                ms.Seek(0, SeekOrigin.Begin);
                return (Outer)bf.Deserialize(ms);
            }
        }

Here we actually force the object graph to be serialized to a MemoryStream (basically a wrapper around a byte[]), and the results are as expected. Much better. But it's not perfect: I quite like the ability to have a "copy constructor" of the form "new ObjectyThing(ObjectyThing source)", and the static Clone method doesn't give me the opportunity to subclass the cloned copy.

So then you combine both approaches:

    class ClonedOuter2 : Outer
    {
        public ClonedOuter2(Outer o)
            : base(GetSerializationInfo(Clone(o)), new StreamingContext())
        { }

        public static Outer Clone(Outer o)
        {
            BinaryFormatter bf = new BinaryFormatter();
            using (MemoryStream ms = new MemoryStream())
            {
                bf.Serialize(ms, o);
                ms.Seek(0, SeekOrigin.Begin);
                return (Outer)bf.Deserialize(ms);
            }
        }

        private static SerializationInfo GetSerializationInfo(Outer o)
        {
            SerializationInfo info = new SerializationInfo(typeof(Outer), new FormatterConverter());
            o.GetObjectData(info, new StreamingContext());
            return info;
        }
    }

I dread to think of the efficiency of this... :-)

Test-driven scalability in Exchange Server Archiver

2008-12-15T11:41:00Z

I’ve recently been working on Red Gate’s new Exchange Server Archiver tool. Earlier on in the development process, I was developing part of the Archive Service (the component responsible for extracting messages from Exchange Server and passing them over to be stored).

As with all Red Gate projects, we involve testers from a very early stage, and try to use automated tests wherever possible. In this particular case, Jon had written a test harness for my code that worked roughly as follows:

Log on to Exchange, insert some number of messages, then log off
Run an archive using a particular set of conditions
Log back on, retrieve the message stubs, and compare them to the expected result, and to the now-archived messages in our store

All this started out running rather nicely, but over time, the system started to show signs of stress. We got up to around about the four thousand test case mark, by which time it was taking an unacceptably long time to do what seemed like a simple operation. Specifically, three minutes to insert a message. What!?

My first move was to try and reproduce the problem on my own system, but in the true spirit of evil bugs, everything worked fine – seven seconds! The usual candidates of rebooting servers, checking for available memory and so on didn’t help, and sending messages from Outlook worked fine.

Somewhat confused, we grabbed the beta version of ANTS Profiler 4 and installed that on Jon’s machine. I was involved in the development of ANTS Profiler 3, so was very interested to see how AP4 was looking!

We profiled the tests, which included the archive service code as well, and grabbed the results. The new call tree instantly showed the problem: the message insertion was responsible for 99% of the time taken, and 80% of that was spent in the code that populates the folder tree of a mailbox. Within that, the call tree just went straight off the edge of the screen – it was behaving far more recursively than I’d ever have expected. Surely this wasn’t right?

A quick trip back to Outlook revealed the cause: one of the other tests had created a 250 folders nested one inside another, and my code was repeating most of the same work for every folder, rather than just doing it the once that was actually necessary. In a nutshell, as soon as the number of folders in a mailbox became large, the performance went downhill rapidly.

Whilst not quite believing Jon’s assertions that a 250-deep folder structure was a valid use case, something still had to be done. A quick tweak of the code to bring the repeated effort out of the loop to where it should have been, and the test took ten seconds to run – nearly twenty times faster than before!

Before ANTS Profiler, I would have resorted to a liberal spattering of Debug.WriteLine (DateTime.Now) statements in an attempt to see where the time was being spent. And no doubt got very confused when it appeared so many times! Instead, I had all the information in front of me, and solving the problem took a matter of minutes. It also saved hours of time spent executing tests, which I can only imagine left more time for Jon to think of even more evil things to throw at the system…

Phew... we made it! (Well, the first half at least)

2008-11-12T02:58:00Z

A little over a year ago, as I was finishing up on SQL Multi Script, Colin, our head of Product Management wandered over for a chat about what we were going to get up to next... something completely different...

Over the course of the next few weeks, we went out and visited a number of sysadmins in the Cambridge area, and chatted to even more on conference calls. The goal was quite simple: find out what it was they were doing, day in, day out, that was taking up their time, and causing them pain. It was a great experience for me as a developer, and we gained a lot of useful information as well, narrowing it down to a few options, mostly in the Exchange Server area.

Chatting to Richard Mitchell, who would be project managing things, whatever "things" turned out to be, we considered the idea of a data management tool - everyone we spoke to was getting hassled about mailbox quotas, struggling with server performance, or having to trawl through backups to recover lost messages. Wouldn't it be a pretty ambitious project to take on, though? After all, even those people with archiving solutions already didn't seem very happy with them, so it must be a difficult problem to solve...

"How hard can it be?" he asked. "Surely it's just something to pull data out of Exchange, somewhere to store it, and a way of viewing it... we'll be done by next week!" That meeting was when Exchange Server Archiver was born.

Of course, it turns out it isn't quite that simple, especially when you actually want the software to be scalable, robust, and easy to use - the last one in particular being sorely missed by a lot of the people we spoke to, who'd had to pay others to come in and set their systems up for them. But, a year on from first concepts, we've just made a public beta release available.

It's a major milestone for us, and one that we've been looking forward to achieving for a long time. But it's by no means over yet - there's still lots we know we need to do, but more importantly, there's going to be things we don't know we need to do yet, and that's where we're really looking for your input in the beta programme.

What did we get right, but more importantly, what did we miss? What doesn't quite feel right, or doesn't quite behave as you expect? What did you spend five minutes looking for, only to find it somewhere else?

We'd appreciate any comments you have over on the beta forum, where you'll also find the release notes, and a whole load more details.

Event, sit! Event, staaaay...

2008-05-08T04:45:00Z

One of the great features in .NET is its event model. I came from a Java world before I started developing C#, and whilst they achieve the same effect using "listeners", I think C# definitely does it more slickly. That's not to say I wouldn't like to be able to do anonymous inner classes in C#, but that's another story. Back to event handlers.

A while back when I was working on ANTS Profiler, I chatted to a few customers using the memory profiler, a lot of whom were struggling to work out why their applications apparently had memory leaks. A lot of the time it turned out to be event handlers not getting unhooked, and stopping objects (usually user controls) getting garbage collected as a result.

Fast forward to now, and I'm currently working with a COM object model that also has events on it. .NET does a pretty good job of generating COM Interop wrappers, and so you can pretty much use these as if they were .NET objects. However, there's a few gotchas when it comes to event handlers, as we'll see in a moment.

First, let's have a quick recap of the way event handlers tie in with the garbage collector when we're in managed code. Here's a quick bit of sample code:

    public class ClassWithEvent
    {
        public event EventHandler Evt;

        public void Poke()
        {
            if (Evt != null)
                Evt(this, new EventArgs());
        }

        ~ClassWithEvent()
        {
            Console.WriteLine("Being garbage collected");
        }
    }

    public class OtherClass
    {
        public static void Main()
        {
            WeakReference wr = MakeCWE();

            GC.Collect();
            GC.WaitForPendingFinalizers();

            if (wr.IsAlive)
                Console.WriteLine("CWE still alive after GC!");
            else
                Console.WriteLine("CWE died in a garbage collection :-(");
            Console.WriteLine("\nPress enter to continue...");
            Console.ReadLine();
        }

        static WeakReference MakeCWE()
        {
            ClassWithEvent cwe = new ClassWithEvent();
            cwe.Evt += new EventHandler(cwe_Evt);
            cwe.Poke();
            return new WeakReference(cwe);
        }

        static void cwe_Evt(object sender, EventArgs e)
        {
            Console.WriteLine("Event fired!");
        }
    }

What's going on here? The main entry point, OtherClass.Main(), calls MakeCWE() to create a new instance of ClassWithEvent, and hook in an event handler to its event. We check that the event fires, and then return a WeakReference to that object. The idea of the WeakReference is that we can see whether the object is still alive, without preventing it from being garbage collected. (An aside: we need to create the object in its own method, so the local variable "cwe" goes out of scope before we run the garbage collection.)

Running the code shows the object being created, the event fired, and then when the GC runs, the ClassWithEvent object gets collected. This means the event handler we hooked in is now disconnected. A little surprising at first maybe, but thinking about it, if there are no other references to the ClassWithEvent object, there's no way it can fire its event, so there's no need to keep the event handler connected.

Changing the code slightly, and adding an event on OtherClass, and causing ClassWithEvent to hook up to this event in its constructor, changes this significantly. At this point, it can't get garbage collected any more, because OtherClass's event may still fire, causing "stuff" to happen within ClassWithEvent.

This is exactly what tends to bite developers working with Winforms - a user control is created, hooks itself into the some of the parent Form's events, and some time later, it gets discarded by removing it from the Form's Controls collection. However, the user control is still hooked into the Form's events, so even though you've no way of getting at the object, it's still sitting there in memory, unable to be garbage collected until the Form is. The solution here is to unhook any event handlers in a Dispose method, and call that Dispose method after removing the control from the Form.

So far so good. But what happens when you aren't using nice .NET objects, but rather COM objects?

I had one object, let's say Foo, which I kept a reference to throughout the life of my application. It had a property, Bar, which returned an object that had an event on it. So, I got my Foo, and did something like:

    foo.Bar.Evt += Bar_Evt

And everything worked nicely, for a while. But a little later, the event would mysteriously stop firing. Some head scratching later, it turned out that this mystery happened whenever a garbage collection ran, and that if I kept a reference to the value of the Bar property in a member variable, the event would stay around.

When you get a reference to a COM object in .NET, you actually get a wrapper around it. So if you access one of its properties which returns another COM object, you get a wrapper around that, and so on. The wrapper around the Foo object doesn't itself reference the wrapper around the Bar object, even though the Foo COM object does reference the Bar COM object. Phew.

Now, as we saw earlier, just having an event handler hooked up to an object doesn't stop that object being garbage collected. What this means is that my Bar wrapper was being garbage collected, there being no references to it any more, even though the underlying COM object was still there...

With hindsight, everything makes sense, and you can see why it happens like it does. Unfortunately, that wasn't enough to stop me being rather confused for a while yesterday!

The pain of software installation

2008-01-08T04:00:00Z

At Red Gate, we have a big focus on usability - we try and make all our products as easy as possible to use, on the basis that if someone is trying to solve a problem, we should be helping to solve the problem rather than giving them more problems to solve just trying to use our software.

Now, maybe thinking about it constantly has sensitised me to poor usability, but sometimes you hit something that makes you wonder.Yesterday afternoon I was looking into a piece of software, and spotted a demo version available for download. The actual download process was straightforward enough, which is a positive start (how many companies make it unnecessarily hard just to get hold of their software?), but then the fun began. I'll admit I was expecting a fair amount of configuration in the setup process, but after:

Two hours,
Over one hundred steps later,
Wizards nested within wizards nested within wizards nested within wizards (I don't exaggerate - at one stage there were four wizards all visible at once!),
Check lists that don't tell you what you've done so far,
Having to debug SQL Scripts that don't work because I so much as dared have a period in a username,
Having to enter the same password about forty times,
And several reboots after the thing crashed out and wouldn't then let me back in...

...I still hadn't managed to get the system working. Like I say, I can appreciate it if I need to give a fair amount of information: if that's what's needed, that's what's needed. But I have to say, I'm now thoroughly put off the product, even if it does have the features I want

Your installation process is the first thing a potential customer sees, so it needs to be good. If they can't get the software working, there's little chance they'll buy it.

Finally, I'll leave you with a photo Marine took towards the end, when the sight of yet another wizard had just about got to me :-)

Should you always use a clustered index?

2007-12-17T11:50:00Z

In one of his recent editorials, Steve Jones of SQLServerCentral wondered about whether every table evar should have a primary key, and in doing so, mentioned the often-quoted advice of also making sure every table has a clustered index.

If you already know about the difference between a clustered and non-clustered index, you can safely skip the next couple of paragraphs. If not, here goes:

Take the example of a phone book. The actual data - that is, the name, address and phone number records - is ordered by the name. If you want to look up Joe Bloggs's phone number, you open the book somewhere near the middle, maybe see the names there start with "M", but "Bloggs" is before "M", so you go a bit earlier in the book. You keep narrowing it down until you find the entry labelled Bloggs, and that's it - all the data for that record is right there. That's a bit like a clustered index.

On the other hand, a book might have a table of contents, sorted alphabetically. If you want to find out about llamas, you search the contents for llamas, which probably then gives you a page number, at which point you go to the page, and there's the data about llamas. The difference here is that you've had to do an extra bit of indirection - following the page number pointer - in order to get to the data. You can probably now see that while you can have as many tables of contents, ordered in any way you like, one set of data can only be physically arranged in one way. This means you can have many non-clustered indexes, but only one clustered index on a table.

OK, back to the original point of the post. In SQL Server, if you've only got a non-clustered index on a table, but no clustered index, then the "pointers" in the non-clustered index actually point to the physical location in the file on disk where the data resides. On the other hand, if you've also got a clustered index, then the non-clustered index contains instead the key into the clustered index. This means that whenever you access a record through the non-clustered index, you must first traverse that, then traverse the clustered index as well - more expensive than simply going to the row directly.

So, I was wondering, how expensive is this extra indirection? In the true spirit of micro-benchmarks, the following data should be treated with an appropriate amount of suspicion.

Firstly, I created six tables, each with three integer columns named [one], [two] and [three] (logical!). The test would be to read five million rows from each of these tables, ordered by the value in column one. The data in columns [one] and [two] was random and uncorrelated, and the data in [three] was equal to that in [one]. Before inserting the data, I created the following sets of indexes:

None at all!
A clustered index on the "wrong" column ([two])
A clustered index on the "right" column ([one])
A clustered index on [two] and a non-clustered index on [one]
A clustered index on [three] and a non-clustered index on [one]
A non-clustered index on [one]

After doing this, I restarted the instance of SQL Server in order to clear any cache it might have, then read the contents of the tables using a quick C# application I knocked up. Some of the results really surprised me:

Read 5000000 rows
ClusteredWrongColumn: 15241.072ms
Read 5000000 rows
ClusteredRightColumn: 4229.28ms
Read 5000000 rows
ClusteredAndNonclustered: 19376.368ms
Read 5000000 rows
NonclusteredAndSimilarClustered: 10259.92ms
Read 5000000 rows
NonclusteredOnly: 11011.792ms
Read 5000000 rows
NoIndexes: 17856.96ms

Re-running the application without restarting SQL Server changed the results somewhat, presumably due to caching (the RAM footprint of the instance had gone from 400MB before the first run to 1.3GB afterwards):

Read 5000000 rows
ClusteredWrongColumn: 17715.984ms
Read 5000000 rows
ClusteredRightColumn: 3837.68ms
Read 5000000 rows
ClusteredAndNonclustered: 17120.752ms
Read 5000000 rows
NonclusteredAndSimilarClustered: 14614.512ms
Read 5000000 rows
NonclusteredOnly: 10588.864ms
Read 5000000 rows
NoIndexes: 13752.992ms

So, having the clustered index on the right column obviously beats having it on the wrong column, and having a non-clustered index beats having none at all. So far so good.

Having the non-clustered index be on "similar" (OK, identical, but I bet it'd work with not-quite-identical-but-close data as well) data to the clustered was also clearly better than when they were totally uncorrelated, and this also makes sense: the page cache will get much less thrashed than if you're jumping around all over the place in the clustered index rather than reading it more-or-less sequentially.

As I suspected, a non-clustered index without a clustered index underneath it performed significantly better: ten seconds rather than fifteen to twenty.

However, there was one bit that really surprised me: the ClusteredAndNonclustered result was similar in the second run, and significantly worse in the first run, than the ClusteredWrongColumn result. I guess SQL Server must have done an in-memory sort of the table when the non-clustered index didn't exist, which meant fewer disk reads in order to get the non-clustered index when it did exist. That'd also explain the much closer results in the second run.

What was the point of this post? Curiousity mainly, but it's also worth realising that a clustered index isn't always the best thing in the world for every table, especially if you're searching over a wide variety of very different fields, rather than just doing lookups on one field for the majority of the time. You need to look at each case on its own, and understand exactly what the queries are you're running before making a decision.

Finally, I should mention that this isn't the whole story. My tests used a load of data inserted into an empty database, then queried. Your databases probably have a whole lot more update and delete operations going on, and they can have a big impact on the way data ends up being laid out on disk - yet another variable to consider!

It's a <strike>boy</strike> new version of SQL Data Compare!

2007-10-08T06:33:00Z

Well, after five months of design, development, testing, documentation, training, and countless other activities that I've almost certainly forgotten to list, SQL Data Compare 6 is out.

Richard's been blogging quite a bit about the release over the last few months, so I don't think there's much point in me listing the all the cool new features again, but I'd really recommend grabbing a copy and giving it a go.

Personally, my main involvement has been with the "read from backup" technology that we've introduced, and it's been a really exciting project to work on, and has given me a real appreciation of some of the quite cunning design decisions in SQL Server.

I think the titles of some of the bugs raised sum up the complexity of the problem quite nicely: "SQL 2000 Non-clustered indexes sharing variable-width key columns with unique clustered indexes fail" was a particularly good example. I'm not sure how many times I've thought I've understood how something worked, only to find that assumption to be completely wrong when you throw in another factor!

If you'd asked me at the start of the project, I would've predicted that the alpha would throw up huge numbers of issues, which is why we originally planned to have both an alpha and a beta. In the end though, there turned out to only be a couple of serious ones, and so we decided to go full steam ahead towards the release. I think this is a great testament to all the testing that happened before the alpha - Chris, thank you!

As with all software, I'm sure there'll be the odd issue here and there, but I'm confident this is going to be the best version of Data Compare yet.

So, what are you waiting for? Go and play!

The (slightly obscene) way to speed up loops

2007-10-03T14:46:00Z

Last night as I wandered around some sites on Java optimization, I came across an example of loop optimization that felt so wrong, yet unfortunately does work!

The reasoning goes as follows: both C# and Java perform array bounds checking, whether you like it or not. Therefore, if you're looping over all the items in the array, don't bother testing for the termination condition - just keep going until you get an index out of bounds exception.

So, rather than your traditional code along the lines of this:

int[] arr = new int[size];
for (int i = 0; i < arr.Length; i++)
{
	arr[i] = i;
}

You instead end up with:

int[] arr = new int[size];
start = DateTime.Now;
try
{
	for (int i = 0; ; i++)
	{
		arr[i] = i;
	}
}
catch (IndexOutOfRangeException)
{ }

To my mind, this goes well and truly against two often-quoted design patterns: 1) code for readability, and 2) exceptions are expensive.

Leaving aside point 1) for a moment, 2) is an interesting one. Yes, exceptions are (relatively) expensive, but if your array is, say, ten million elements, the cost of throwing one exception can be outweighed by the gain of not performing ten million comparisons. If your array is only five items, chances are the exception will be more expensive.

Some results: to run the code as above on an array of size 100,000,000, the "conventional" method took 625ms, and the "exception"method 609ms. Another interesting point was that looping backwards over the array (i.e. comparing against zero rather than the array's length) was actually slower, at 656ms.

When I originally tried this at home under Java, I got significantly more different results - with the "exception" method being three times quicker at times. However, I've been unable to reproduce this on my work machine, so it could be something slightly weird going on.

I hope no-one reading this post is now running for their code to go and replace all their loops with try..catch...IOOBE's now - I wouldn't recommend it. Sure, it might be a little faster, but I'd go for the more obviously correct code any time.

And you should always remember the 80:20 rule: chances are, 80% of your program's execution time is in 20% of the code. Chances are, "i < arr.Length" isn't part of that 20%.

(Lastly, a caveat: this post uses "micro-benchmarks"; tiny snippets of code that probably don't bear any resemblance to real life in almost all cases. I don't promise that these results hold in different situations!)

Can you Break The Code?

2007-09-19T04:22:00Z

Fancy winning some cool goodies?

Red Gate has just launched its "Break The Code" prize draw, where you can win one of the following prizes:

A year's subscription to Safari Books Online (worth $550)
One ANTS Profiler Pro license (worth $495)
Red Gate goodies

All you need to do is figure out how to decode the following text:

WW91IHdpbGwgbm90IG1ha2UgeW91ciBhcHBsaWNhdGlvbiBydW4gZmFzdGVyIGJ5IGRlY2lwaGVy
aW5nIHRoaXMgY29kZS4gQW5kIGluc3RlYWQgb2YgdHJ5aW5nIHRvIHByb3ZlIHRvIHlvdXJzZWxm
IGhvdyBjbGV2ZXIgeW91IGFyZSwgd2h5IGRvbid0IHlvdSBzaG93IHlvdXIgYm9zcywgY2xpZW50
cywgYW5kIHBlZXJzIHdoYXQgYSBjb21wZXRlbnQgZG90bmV0IGRldmVsb3BlciB5b3UgYXJlLCBi
eSBwcm9maWxpbmcgdGhlIHBlcmZvcm1hbmNlIG9mIHlvdXIgY29kZSBiZWZvcmUgc2hpcHBpbmcg
eW91ciBhcHBsaWNhdGlvbi4gVGhhdCB3YXksIHlvdSB3aWxsIGNhdGNoIHVwIGFueSBwb3RlbnRp
YWwgcGVyZm9ybWFuY2UgaXNzdWVzIHdheSBiZWZvcmUgaGFuZCwgYW5kIHdheSBiZWZvcmUgYW55
b25lIG5vdGljZXMgYW55dGhpbmchIEZpcnN0IHJhdGUgZGV2ZWxvcGVycyB0ZW5kIHRvIGtub3cg
aG93IHRoZWlyIGNvZGUgcGVyZm9ybXMgYmVmb3JlIGxldHRpbmcgdGhlaXIgcHJvZ3JhbSBvZmYg
aW50byB0aGUgd2lsZC4gRG8geW91PyA=

Anyone who sends in the correct answer will be entered into the draw - so time to start hacking!

More details at http://www.red-gate.com/products/ants_profiler/break_the_code.htm...

THE PRIZE DRAW ENDED ON SEPTEMBER 30TH.

Random names and the Birthday Paradox

2007-09-07T03:08:00Z

I'm currently in the middle of giving Red Gate's sample "Widgets" database a bit of a face lift, in preparation for the new version of SQL Data Compare 6 that's coming soon. As part of this, I wanted to populate a pretty generic "Contacts" table with some sample names.

So, off I go to Mr Google, and find some lists of the Top {n} Baby Names, Top {n} Surnames and so on, and make myself a spreadsheet with both of those lists. Then combine that with a few RAND() calls and VLOOKUP()s, and I have my list of names. All good.

However, we have testers here, and they're rather good. Out of my 60 odd names that I generated, there were quite a few duplicates - not just duplicated surnames, but duplicated firstname AND surname combinations. This surprised me quite a bit - I had about 50 surnames, and 25 first names, which I figured should give me 1,250 possible combinations. So why so many duplicates in a set of only 60 results?

Being rather early on a Friday morning, I opted for the brute force method of attacking the problem, and just added some more names - now I've got 100 surnames and 70 first names. But still, the duplicates were there. At this point I added a COUNTIF() function to the end of each result row so I could see the scale of the problem at a glance, and it was quite scary.

Upping the number of generated names to 199, I got the following distribution of appearances:

Once: 26
Twice: 66
Three times: 63
Four times: 24
Five times: 20

To put it another way, I only had 90 unique results out the 199 I'd generated. Ouch.

I've been well and truly bitten by the Birthday Paradox here (the thing that states that if you have more than 23 people in a room, the chances of two of them sharing the same birthday are more than 50%). A quick back-of-the envelope calculation suggests that with the size of my data sets, I'll reach the 50% probability point after about 25 results.

So, what to do?

If I was coding this in C#, my first instinct would be to generate a combination, check if it was already in the set of results, and throw it out if it was, but the results I've got above should demonstrate that this is probably a lot less efficient than you might think.

A more interesting option is this: you have n possible combinations, and want m results. For result i, you generate a random number between (n/m)*i and (n/m)*(i+1). This guarantees you won't get duplicates, and still gives you a pretty good random distribution, albeit not perfect.You could probably improve this with more complex distributions if you want truly random data, but that's beyond my knowledge of statistics.

Say hello, static constructor?

2007-08-03T04:12:00Z

When does the static constructor of your .NET class actually get called?

Sounds like it should be pretty obvious really - at least going on the side of "how late can it possibly be called". Most of us are used to the idea that static constructors are called before any other methods on that class are called. I know I hadn't given it too much more thought than that.

According to the MSDN page on static constructors, they will be "...called automatically to initialize the class before the first instance is created or any static members are referenced".

However, what I didn't realise is the implications this had when you start calling static methods on subclasses of your types. Consider the following code:

static class Program
{
	static void Main()
	{
		Sub.SayHello();
		Sub.NoOp();
		Sub.SayHello();
		Console.ReadLine();
	}
}

class Super
{
	protected static string m_String = "";

	static Super()
	{
		Console.WriteLine("Super..cctor() called");
	}

	public static void SayHello()
	{
		Console.WriteLine(m_String);
	}
}

class Sub : Super
{
	static Sub()
	{
		Console.WriteLine("Sub..cctor() called");
		m_String = "Hello world!";
	}

	public static void NoOp()
	{
	}
}

Now, if you run that, I would have expected to see something along the lines of:

Super..cctor() called
Sub..cctor() called
Hello world!
Hello world!

But no! What you actually get is:

Super..cctor() called
<Not set yet>
Sub..cctor() called
Hello world!

In other words, calling a static method on a subclass that is defined on the superclass does not cause the subclass's static constructor to execute! Only when the first member of the subclass is accessed is the static constructor called.

Now, the interesting thing is that if you look at it in Reflector, you'll see that the compiler has actually called Super.SayHello() rather than Sub.SayHello() as in the original source:

private static void Main()
{
    Super.SayHello();
    Sub.NoOp();
    Super.SayHello();
    Console.ReadLine();
}

I guess this is a performance win, since the inheritance hierarchy can be traversed at compile-time rather than design-time, but I'm a bit sceptical. Does this really honour the contract that the static constructor will be called before "any static members are referenced"? Let me know what you think!