SELECT a varchar field on one entry in an 8.2 million entry table - performance help

Question

I have a table with 8.2 million entries in a SQL Server 2005 database. This table stores basic past customer details (referrer, ip, whether they entered via an advertisement, etc) for every customer we have had come to the site. Unfortunately, whoever designed the table (way before I was here, I'm a lowly intern) used a varchar(50) field for the ip address field in the table.

Now, I have been tasked with finding a way to run a query to see if we have an entry for a given ip address. This is the first time I've had to deal with a data set this large and no matter what I do, I can't seem to improve the result speed much.

Here is an example query from what I was running:

SELECT TOP(1) [ID]
FROM [dbo].[SiteVisit]
WHERE [IPAddress] = '61.135.249.118'

This query ran for over 2 min and 31 seconds before I cancelled it. Is there any way to improve the speed of this look up or would it be more advisable to create a new index in the table that stores the INET_ATON values and perform the look up on that?

Thanks!

You got it: add an index to the table for that column. – p.campbell Aug 22 '11 at 14:26 — p.campbell, Aug 22 '11 at 14:26
Creating a new index IS the way to improve the speed :-) – Christian Specht Aug 22 '11 at 14:30 — Christian Specht, Aug 22 '11 at 14:30

score 4 · Answer 1 · answered Aug 22 '11 at 14:26

4

If the IP_Address field were indexed, the lookup would be very very quick.

The recommended index for this would be IP_Address INCLUDE(ID) unless ID is your clustered key, in which case you can exclude it.

answered Aug 22 '11 at 14:26

JNK

58,244
14
113
132

score 3 · Answer 2 · answered Aug 22 '11 at 14:25

3

If you are going to often run the query "do we have this IP address" then you should definitely add an index on IP address. But if this is just a one-off exercise then no.

answered Aug 22 '11 at 14:25

Tony Andrews

121,972
20
211
249

1

+1 Before creating any index, verify that the reads will justify the writes. While the index will improve reads, it will slow down dui's (deletes, updates, and inserts). Don't just create the index for a one-off query. Verify that you really need it. – brian Aug 22 '11 at 14:31

score 1 · Answer 3 · answered Aug 22 '11 at 14:25

Try creating an index on IPAddress. If the only things you need to select are ID based on the IPAddress, and this is a frequent way you search, then you should create an index like this:

create nonclustered index ix_SiteVisit_IPAddress on dbo.SiteVisit 
(IPAddress) include (ID)

SELECT a varchar field on one entry in an 8.2 million entry table - performance help

3 Answers3