Robert Chipperfield

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

2008-11-12T08: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-08T10: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-08T10: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-17T17: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-08T12: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-03T20: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-19T10: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-07T09: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-03T10: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!