Maybe you should leave those key lookups alone

So.... In my last post I shined a flashlight on the concept of creating covering indexes to eliminate key lookups for your queries. Turns out that covering indexes are killer to tables that endure constant updates. After adding covering indexes to several of these kind of tables, my database saves came crashing to a halt, overwhelmed with locks. I'm not sure yet what the answer is, but for the time being, I've made amends with those key lookups.

Eliminate those key-lookups

If you are developing for any database involved application that deals with a moderate quantity of data and you stick around long enough, you will find yourself investigating performance. Maybe you operate like our team did on a recent project where we started with some base expectations of performance, moved forward until gross disappointment set it, hacked at performance enhancements until we felt better about things, adjusted our expectations, and moved on. This cycle repeated itself many times and each time, I learned something new about how not to do a variety to things.

I won't spoil the fun for you, but if you are using SQL Server 2005 or greater, I will offer this one thing that I found enlightening: key-lookups in my query execution plans were my failure to understand(or care about) included columns. I would explain more myself, but that has already been done so very well in this post here by Joe Webb.

So this still counts as a blog entry right?

Cheers!

Comparing nullable dates

Sometimes working with Nullable<T> can be like arguing with a pedantic nerd.  And while being able to do this effectively is probably not something you want to advertise in the skills section of your résumé, you can win friends by translating for such challenged communicators.  Just as such, you can win programmer friends by writing extension methods that simplify overly verbose operations with... say... Nullable<DateTime>.

If you accept all that, then I present this here "friend catcher" to you.  Keep the anecdote close to heart that with great power comes great responsibility and always remember that you cannot hold me responsible if you crash Facebook with all the new friends you amass.  I will ignore any emails I receive blaming me so.

/// <summary>
/// Compares two nullable dates using only the day, month, and year.  
/// Ignores hours, minutes, seconds...
/// </summary>
public static int DateCompare(this DateTime? dt, DateTime? compareDate)
{
    DateTime? a = dt.HasValue ? dt.Value.Date as DateTime? : null;
    DateTime? b = compareDate.HasValue ? compareDate.Value.Date as DateTime? : null;
    return Nullable.Compare<DateTime>(a, b);
}