SQL Query - 20mil records - Best practice to return information
- by eqiz
I have a SQL database that has the following table:
Table: PhoneRecords
ID(identity Seed)
FirstName
LastName
PhoneNumber
ZipCode
Very simple straight forward table. This table has over 20million records. I am looking for the best way to do queries that pull out records based off area codes from the table. For instance here is an example query that I have done.
SELECT phonenumber, firstname
FROM [PhoneRecords]
WHERE (phone LIKE '2012042%') OR
(phone LIKE '2012046%') OR
(phone LIKE '2012047%') OR
(phone LIKE '2012083%') OR
(phone LIKE '2012088%') OR
(phone LIKE '2012841%')
As you can see this is an ugly query, but it would get the job done (if I wasn't running into timeout issues)
Can anyone tell me the best way for speed/optimization to do the above query to display the results? Currently that query above takes around 2 hours to complete on a 9gb 1600mhz ram, i7 930 quadcore OC'd 4.01ghz. I obviously have the computer power required to do such a query, but still takes too long for queries.
