Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Map IP Address to a Geographical Location Expand / Collapse
Author
Message
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
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 @ 2:58 PM
Points: 36,760, Visits: 31,215
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #759164
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 @ 2:58 PM
Points: 36,760, Visits: 31,215
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #759169
Posted Friday, July 24, 2009 9:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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
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 @ 2:58 PM
Points: 36,760, Visits: 31,215
@ 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #759256
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: Wednesday, August 21, 2013 8:04 AM
Points: 56, Visits: 133
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
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 @ 2:58 PM
Points: 36,760, Visits: 31,215
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #759491
Posted Saturday, July 25, 2009 2:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399
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
Posted Saturday, July 25, 2009 3:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399
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
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 @ 2:58 PM
Points: 36,760, Visits: 31,215
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #759675
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse