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

[adventurous1]

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!

 

[feherke]

Hi

Just a simple query like this could not be optimized neither with a stored function :

[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.

http://free.rootshell.be/~feherke/

 

[adventurous1]

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

 

[feherke]

Hi

That is not a reason to use stored function. There is nothing a function can do and a regular query can not.


Feherke.

http://free.rootshell.be/~feherke/

 

[feherke]

Hi

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.

http://free.rootshell.be/~feherke/

 

[adventurous1]

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.