Bad to Good Queries

I got to search for this stuff but its not running good!

A developer was tasked with finding some data and he came to me with the following query.  I am very glad he did so:

1 2 3 4 5 6 7

select m.MemberShipNo, m.FirstName, m.LastName, m.Address1, m.City, m.StateProvince, m.PostalCode, m.PackageId, p.ProductLabel from Members m join Packages p on p.Id = m.PackageId where (FirstName='something' and LastName='something' and Address1='somewhere') or (FirstName='something' and LastName='something' and Address1='somewhere') or (FirstName='something' and LastName='something' and Address1='somewhere') or ....

He recognized that this was going to be at the worst a full table scan,  at best an index scan, and this does not need to run in prod.

As first I said, 'Generally speaking, you will want to do something like this':

1 2 3 4 5 6 7 8 9 10 11 12 13 14

select m.MemberShipNo, m.FirstName, m.LastName, m.Address1, m.City, m.StateProvince, m.PostalCode, m.PackageId, p.ProductLabel from Members m join Packages p on p.Id = m.PackageId where (FirstName='something' and LastName='something' and Address1='somewhere') UNION select m.MemberShipNo, m.FirstName, m.LastName, m.Address1, m.City, m.StateProvince, m.PostalCode, m.PackageId, p.ProductLabel from Members m join Packages p on p.Id = m.PackageId (FirstName='something' and LastName='something' and Address1='somewhere') UNION select m.MemberShipNo, m.FirstName, m.LastName, m.Address1, m.City, m.StateProvince, m.PostalCode, m.PackageId, p.ProductLabel from Members m join Packages p on p.Id = m.PackageId (FirstName='something' and LastName='something' and Address1='somewhere') UNION ...

This will do an index seek (there is an index on LastName.. I put that there after examining queries and the query cache and decided an index with LastName had the closest selectivity to 1 given the columns I wanted to include).  Its important to put your thinking in sets hat on when accessing a database.  IF this then this, If this equals this or this equals that kinds of logic works great in a programming language.  You try to wrap or's in your select and things get nasty pretty quickly.

But it gets worse -- He is doing this for 1048 members.  Just thinking about building that explain plan is enough to give a DBA a nose bleed.

The query also runs for 1 minute and 14 seconds (I ran on reporting while getting the below solution done).  In Oracle Land, where selects don't lock up the database because it's playing around with rollback segments, we don't worry about this as much.  In SQL Server non Read-Committed Snapshot Isolation, a query running for a minute and 14 seconds is fairly egregious.  People are on the phone trying to help customers, trying to get information -- So the phone call goes from 'Alright let me pull that up for you.. Wow this is taking awhile.. How are things?'

I took the 1048 records and put them into a table, and then I did something like this:

1 2 3 4 5 6

select m.MemberShipNo, m.FirstName, m.LastName, m.Address1, m.City, m.StateProvince, m.PostalCode, m.PackageId, p.ProductLabel from Members m JOIN #MemberInfo as mi on m.LastName = mi.LastName and m.Firstname = mi.FirstName and m.Address1 = mi.Address1 join Packages p on p.Id = m.PackageId

This runs in 88ms.