homepage Welcome to WebmasterWorld Guest from 54.167.185.110
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
GEO IP Lookups.
What’s Faster?
blend27




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

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

 

plumsauce




msg:3465088
 10:19 am on Sep 30, 2007 (gmt 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.

aspdaddy




msg:3465296
 5:34 pm on Sep 30, 2007 (gmt 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...]

blend27




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

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?

rajatgarg




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

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

I will appreciate your help.

Thanks,
Rajat

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved