Restless C#ding

On a recent project I was told by a colleague about a certain SQL query generated by entity framework, that was ridiculously out of hand. Entity Framework allows you to pretty easily create a simple Data Access to the Table Per (Sub) Type pattern.

What this means is that you may have an inheritance of both a Student and an Instructor, derived from a Person, and query to retrieve a strongly typed object. So here’s where performance & optimization comes in. There’s a couple of ways to query against this data model.

Method 1: Implicit Typing

var query = from p in Persons
            where p.PersonID.Equals(_personID)
            select p;

Method 2: Explicit Typing

var query = from p in Persons.OfType<Instructor>()
            where p.PersonID.Equals(_personID)
            select p;

They seem pretty similar, however there’s quite a significant difference in what gets generated. By using method 2, you wind up letting Entity Framework know exactly what table it’s querying against. Which means your SQL code looks something like this:

SELECT
    PersonID,
    Column1,
    Column2,
    Column3
FROM
    Instructor
WHERE
    PersonID = @PersonID

However when you don’t specify the type, Entity Framework constructs a SQL query intended to make SQL go and figure it out (keep in mind that there’s no automatic discriminator column – it figures out type based off of the primary key – the ID column. More on this in a minute). The generated code looks something like this:

SELECT
    PersonID,
    Column1 as [0x01],
    Column2 as [0x02],
    CASE WHEN [1x01] IS NOT NULL THEN CAST(INT, [1x01])
    ... many more case, casts, for every column in every table ...
    
FROM
    Person
    UNION ALL SELECT 
        PersonID as [1x01],
        Column1 as [1x02],
        Column2 as [1x03]
        UNION ALL SELECT
                ... many more unions for every table ...
WHERE
    PersonID = @PersonID
        

Now you can see that this query gets very complex as a product of:

a) the number of subtypes

b) the number of columns for each type

The query generated gets the Cartesian product of all columns, and looks for the one where the key isn’t null – that’s the “winner” subtype. I imagine (haven’t yet tried this) that having a nested subtype involved here (like BusinessStudent in the linked example above) would cause an even more ugly nesting of the union within another union statement.

Now back to the point of this article – performance. How bad is what we see above? In an empirical example, thanks to JetBrains’ Dottrace and nunit tests I observed averages of:

Method 1: 126ms for the query to run

Method 2: 25ms for the query to run

I had then discovered the benefits of precompiling Entity Framework view code to optimize the SQL generation. This bought me roughly 26% gain in performance for the specific empirical examples.

Method 1: 100ms

Method 2: 20ms

Now we have roughly 80ms to play with – if the code to get from Method 1 to Method 2 (we don’t know the type that we’re retrieving, however we want the optimized query of Method 2) is more than 80ms, then the performance “fix” will be worse than the problem.

So far, given the constraint of EF (for now), and the Table Per (Sub) Type pattern, the only solution that comes to mind is reflection – this would involve a stored type as a discriminator column of sorts, then reflecting on that type, and calling the generic Person.OfType<T>() method via reflection. This costs us an extra query and reflection – neither of which are cheap. A separate empirical example (not the same code as the first) brings the total cost to ~350ms, a net performance loss of 250ms.

Method 1’s performance would have to degrade (through additional columns/subtypes) by ~250ms more in order to justify rolling a custom discriminator and reflecting to grab the subtype.

This was a pretty interesting exercise in when not to make performance optimizations that you know will need to be done long-term.

Told a friend I'd fix their wireless issue after the manufacturer quoted them $1k for a simple wireless fix. Turns out that's not actually (that) crazy (if you consider how overpriced their parts are). This laptop, a compaq presario v6133 (from the v6000 line) has the nice 'feature' of a non-working fan sensor / regulator.

The good is that it runs nice & quiet... the bad is that it'll just burn itself out. Guess what part of the mobo dies first? Nope, not the CPU, not the RAM, and not even the northbridge -- it's the good old onboard WIFI adapter.

What might actually save this laptop is an addendum to the (expired) 1 year warranty, declaring free fix & free shipping for this issue as long as it's w/in 2 years. It's declared as an "HP Limited Warranty Service Enhancement".

Otherwise, it's a BIOS update (to fix the fan control, not the damage already caused), and an external card -- or an ebay of the MOBO.

It's pretty sad, actually -- it's rare that someone drops off their PC that's fully virus and malware protected, with proper restore points & generally in perfect working order.

If you need to check to see if two functions are the same instance, you can just check the variables for equality:

var f = new function() { 
  alert("hello world");
}; 

var f1 = new f();
var f2 = new f();
var boolTest = (f1 == f2);

However, if you need to check to see if the bodies of those functions are equal (or both contain some text, etc -- essentially, just working with the actual language of the function), you can just cast as string to get the text of the function, then check for equivalency:

function fnsAreEqual(f1, f2){
  return String(f1) === String(f2);
} 

var boolTest2 = fnsAreEqual(
  function(){ alert("sameFn"); }, 
  function(){ alert("sameFn"); }
  ); 

updated: fixed JavaScript formatting