Welcome to WebmasterWorld Guest from 54.196.232.162

Forum Moderators: open

Message Too Old, No Replies

GEO IP Lookups.

What’s Faster?

     
12:48 am on Sep 30, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Dec 27, 2004
posts:1666
votes: 35


Hi All.

I am working with GEO IP Database(MSSQL) at this point and would like to make some queries a bit faster.

The entire DB is over 100000 records and we look up the country by comparing IP to the Database on every request.

Would it make a difference if we split the MASTER IP Table of DB in 255 tables and compare first octet of incoming IP to a particular Table accordingly, would it make any difference if they were views. Any other recommendations would be welcome.

Just from your experience.

Thanks

Blend27

10:19 am on Sept 30, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 12, 2003
posts:772
votes: 0


I had to refer to the performance notes from a source code file, these are the results from one of my projects:

performance:

single threaded operation with multithreaded libs
@ 550 mhz:

400 million geoip lookups on 4 billion ip address
space takes 31 seconds, or 12,903,225 per second


Notice that the lookups were done over a wide range of ip addresses to make sure that cpu cache effects would play as little a part as possible in the algorithm.

Put it this way: the geoip overhead of processing a log file with 13 million hits in it would be about 1 second.

My db is at the country/region level.

5:34 pm on Sept 30, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 13, 2002
posts:2162
votes: 0


If you are on SQL 2005 you could use the new covered index feature, creating a single index across all the columns being looked up.

That way the result set is fetched direct from the index not touching the database and it really improves lookup times.

Info here:
[odetocode.com...]

6:32 pm on Oct 1, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Dec 27, 2004
posts:1666
votes: 35


Unfortunately we are still on 2000 on one of the boxes, but looks like we will be upgrading from 100,000 records to about 2,200,000, this is why I am thinking of moving to the first part of IP logic and splitting into 255 or perhaps re-aranging the data in the way that top countries of choise(most visits) records are sitting on top and USING TOP Keyword to only get the first record and stop searching.

BTW, when using TOP Keyword does the DB Engine stops after it finds the first match or it still goes thru the rest of the rows?

6:03 am on Oct 30, 2007 (gmt 0)

New User

10+ Year Member

joined:July 19, 2004
posts: 32
votes: 0


Can you guys suggest a good place to get this DB?

I will appreciate your help.

Thanks,
Rajat

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members