Mono Support

The very first tests showed that when loading small tables the speed is practically identical, but the more data comes back, the bigger the gap gets. For example, 2 and 5 seconds when loading 4,000 records versus 5 and 20 seconds when loading 40,000.

Further checks showed the following:
– the System.Data.SqlClient implementation in Mono is roughly 40% slower than in .Net;
– object creation time differs between Mono and .Net by fractions of a percent;
– different collections (linked lists, dictionaries, groups of arrays, or plain ordinary lists) don’t affect the time it takes to load data from the DB, since fetching, type casting, and processing the loaded data overall take an order of magnitude more time than iterating over collections does;
– if more than N elements are being loaded, instead of a normal select * from <..> where <..>, it just runs select * from <..> and then filters the result;

The last item caught my attention. On one hand, it’s all correct — I wrote that code myself and tested it against various databases, comparing this approach’s performance. On the other hand, it turned out that if you remove the check and never load all the elements, the speed gap between Mono and .Net shrinks to 30-40%.
Digging a bit deeper, I found that filtering the results itself wasn’t done very efficiently: there’s an array of IDs that are supposed to be in the resulting list, and for every row of the table it runs a check Array.IndexOf(id) != -1.
This method has a right to exist if IndexOf itself is based on some optimized algorithm (at least a binary search), but it’s completely unacceptable for a linear scan.

Getting to the bottom of it became a matter of principle. I found the implementation of IndexOf in Mono:

public static int IndexOf (Array array, object value, int startIndex, int count)
{
    if (array == null)
        throw new ArgumentNullException ("array");

    if (array.Rank > 1)
        throw new RankException (Locale.GetText ("Only single dimension arrays are supported."));

    // re-ordered to avoid possible integer overflow
    if (count < 0 || startIndex < array.GetLowerBound (0) || startIndex - 1 > array.GetUpperBound (0) - count)
        throw new ArgumentOutOfRangeException ();

    int max = startIndex + count;
    for (int i = startIndex; i < max; i++) {
        if (Object.Equals (value, array.GetValueImpl (i)))
            return i;
    }

    return array.GetLowerBound (0) - 1;
}

As we can see, this is a linear scan. I didn’t bother looking up the implementation of this method in .Net, but I suspect it calls Array.BinarySearch, which makes it several times faster. Either way, if you need to check whether a record exists in a collection, the fastest option is to use a Dictionary<>, which is what I settled on. The result is reasonably acceptable: the Mono server uses a bit more memory and loads about ~40% slower. On top of that, once we dropped Array.IndexOf, the .Net version started loading a couple dozen seconds faster too.

I’ve got just one takeaway: Premature optimization is the root of all evil.