Email address lookup in SQL Server

We have a large subscriber database and have a lookup based on email address of type nvarchar(100). It sometimes times out probably because of a large scan of it.

var existingEmails =
    _subscriptionsRepository.DataContext
                            .Subscriptions
                            .Where(q => q.Email != null && 
                                        q.Email.ToLower().Equals(model.Email.ToLower()))
                            .ToList();

I want to improve this, but weary of some of the options. Would an index on it be ok, I can handle the additional storage if needed, or a computed column to say grab the domain part. What is a fast way to lookup a row based on an email address of hundreds of thousands?

Answer

Yes, that’s exactly what an index is for. Add an index. However: for an index to work optimally, it needs to be an equality (or at least, range) filter of the values stored in the index; and LCASE(Foo)/LOWER(Foo) etc doesn’t qualify. Assuming that your database is running in case-sensitive mode, it would be better to store the data in the normalized form (lower-case, or whatever) when you store it, then perform the same normalization on the data you’re searching by, allowing you just just use an equality test on the database, i.e.

var normalizedEmail = YourNormalizationMethodHere(model.Email);
var matches = _subscriptionsRepository.DataContext.Subscriptions.Where(
    q => q.NormalizedEmail != null && q.NormalizedEmail == normalizedEmail);

(but even just having the data in an index can help performance, even with functions like LCASE/LOWER, as it can reduce the page reads required)