Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Oscar D Garcia » Map IP Address to a Geographical Location
Add to briefcase
53 posts, Page 2 of 6
««12345»»»

Map IP Address to a Geographical Location Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
srcopeland
Posted Thursday, July 16, 2009 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 11:45 AM
Points: 2, Visits: 33
I used combinations of substring and charindex (probably very functionally similar to the parsename route) to rewrite the functions. The performance, when dealing with large amounts of data, increased by leaps and bounds. On one subset of data, a simple query went from several minutes down to 3 seconds.

Very nice article though. I'm currently implementing this at my company as well. Thanks!
Post #754236
Jeff Moden
Posted Friday, July 24, 2009 8:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 32,893, Visits: 26,768
tdcheek (7/10/2009)
IP's fit much better in binary(4) than bigint - the conversion is pretty simple and cuts down heavily on reads and index size


What is your method of conversion? Can you post the code, please?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #759164
Jeff Moden
Posted Friday, July 24, 2009 9:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 32,893, Visits: 26,768
Mark (7/13/2009)
I think both functions can be simplified


create function dbo.ConvertIp2Num(@ip nvarchar(15))
returns bigint
as
begin
	return ((cast(parsename(@ip,4) as bigint)*256+
             cast(parsename(@ip,3) as bigint))*256+
             cast(parsename(@ip,2) as bigint))*256+
             cast(parsename(@ip,1) as bigint)
end


That won't return the correct answer... the octets must be multiplied by powers of 256.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #759169
Mark-101232
Posted Friday, July 24, 2009 9:11 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 1,498, Visits: 18,144
Jeff Moden (7/24/2009)
Mark (7/13/2009)
I think both functions can be simplified


create function dbo.ConvertIp2Num(@ip nvarchar(15))
returns bigint
as
begin
	return ((cast(parsename(@ip,4) as bigint)*256+
             cast(parsename(@ip,3) as bigint))*256+
             cast(parsename(@ip,2) as bigint))*256+
             cast(parsename(@ip,1) as bigint)
end


That won't return the correct answer... the octets must be multiplied by powers of 256.



select dbo.ConvertIp2Num('192.15.10.125') -- gives 3222211197

The nesting of the multiplys gives the effect of multiplying by powers of 256. (Look at the brackets)

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #759185
Jeff Moden
Posted Friday, July 24, 2009 10:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 32,893, Visits: 26,768
@ Mark...

Heh... that's what I get for looking at stuff before the caffeine reaches the brain. Thanks, Mark... nicely done.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #759256
ozkary
Posted Friday, July 24, 2009 12:12 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 10, 2012 12:31 PM
Points: 56, Visits: 129
hi,

yes, fine tuning the queries to get the best performance is always welcome.

For production environment, I suggest to process/publish the conversion of IP to num during the import process. This way applications are not affected by the overhead.

thanks for the feedback

http://ozkary.blogspot.com
http://og-bit.com
Post #759395
Jeff Moden
Posted Friday, July 24, 2009 2:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 32,893, Visits: 26,768
ozkary (7/24/2009)
hi,

yes, fine tuning the queries to get the best performance is always welcome.

For production environment, I suggest to process/publish the conversion of IP to num during the import process. This way applications are not affected by the overhead.

thanks for the feedback


Heh...yes... unless you have to do it on the fly during a session.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #759491
SwePeso
Posted Saturday, July 25, 2009 2:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:26 PM
Points: 2,359, Visits: 3,292
No need to cast either

alter function dbo.ConvertIp2Num(@ip varchar(15))
returns bigint
as
begin
    return	16777216.0 * parsename(@ip, 4)
		+ 65536 * parsename(@ip, 3)
		+ 256 * parsename(@ip, 2)
		+ 1 * parsename(@ip, 1)
end

N 56°04'39.16"
E 12°55'05.25"
Post #759596
SwePeso
Posted Saturday, July 25, 2009 3:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:26 PM
Points: 2,359, Visits: 3,292
And with some error checking

alter function dbo.ConvertIp2Num(@ip varchar(15))
returns bigint
as
begin
    return	case 
			when @ip like '%[^0-9.]%' then NULL
			when @ip like '%.%.%.%.%' then null
			when @ip like '%..%' then null
			when @ip like '%[0-9][0-9][0-9][0-9]%' then null
			else 16777216.0 * parsename(@ip, 4)
				+ 65536 * parsename(@ip, 3)
				+ 256 * parsename(@ip, 2)
				+ 1 * parsename(@ip, 1)
		end
end

Or see this topic from 2006
http://www.sqlservercentral.com/Forums/Topic302100-145-1.aspx

N 56°04'39.16"
E 12°55'05.25"
Post #759599
Jeff Moden
Posted Saturday, July 25, 2009 12:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 32,893, Visits: 26,768
Peso (7/25/2009)

Or see this topic from 2006...


Refreshers are never a bad thing.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #759675
« Prev Topic | Next Topic »

Add to briefcase
53 posts, Page 2 of 6
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.