Skip to content
Advertisement

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.

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?

Advertisement

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.

(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)

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement