Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help! I need a function to look up the geography for an IP address

Status
Not open for further replies.

adventurous1

Programmer
Mar 5, 2004
64
0
0
US
Hi all...

I need help in writing a FUNCTION that will take an IP address and look it up in another table and return its corresponding geography values.(

A function is needed as I have millions of records to lookup and brute force SQL does not perform well.)

source record:

IP Address Date
155.44.33.91 4/1/2011

lookup table:

start_ip end_ip city state zip
155.44.33.1 155.93.22.200 lakeland tx 55495
197.65.39.8 198.33.95.12 plano ca 85167

Can someone help me with an answer? Thanks!
 
Hi

Just a simple query like this could not be optimized neither with a stored function :
Code:
[b]select[/b]
[teal]*[/teal]
[b]from[/b] adventurous1_table
[b]where[/b] [green][i]'155.44.33.91'[/i][/green] [b]between[/b] start_ip [b]and[/b] end_ip
So what should actually happen with the result of the lookup ?


Feherke.
 
Result would be the original ip address and the extended attributes of the ip address, i.e. city, state, zip.
 
Hi

adventurous1 said:
A function is needed as I have millions of records to lookup and brute force SQL does not perform well.
From another point of view : if your current query is slow, post it so we can analyze it. Also post the exact table structure including constraints and indexes.


Feherke.
 
A function will be more efficient going through a collection of records. The query basically is a join on ip address to retrieve extended attributes per above.

select a.ip_address, b.city, b.state, b.zip
from ip_address
where a.ip_address is between b.start_ip and end_ip

There has to be a better way of doing this, as the performance over millions of records is horrible.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top