Storing IPv4 Addresses for Performance

Question

Storing IPv4 Addresses for Performance

Viewing 15 posts - 46 through 60 (of 100 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Hey Jeff - I gotta ask: do you actually WORK for a living?? Maybe you get paid for what you know and not what you do at work? That is the only way I can see your having so much free time to post so damn much stuff here! :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

TheSQLGuru (8/20/2009)
Hey Jeff - I gotta ask: do you actually WORK for a living?? Maybe you get paid for what you know and not what you do at work? That is the only way I can see your having so much free time to post so damn much stuff here! :w00t:

Heh... I'm very effective at multi-tasking not to mention being able to "touch type" at about 80 words a minute... 😛

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Jean-Sebastien Carle (8/20/2009)
I must appologize Jeff, in my haste I misread part of your code. It is indeed an interesting approach that I will explore further.

Very cool. Thank you for the feedback.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jean-Sebastien Carle Old Hand Points: 359 More actions · Answer 4

When selecting data, I believe that combining my approach with Jeff's suggestions results in excellent performance. If the primary stored field for the IP address is still an int, we maintain our query efficiency and our bitwise operand capabilities. If we also forgo the UDF for converting an integer IP to a human readable format (or whatever format is needed) and instead create a computed column for this information, storage space is unaffected and execution time is greatly reduced.

Using the bitmasked division approach to add the computed column, the ADD would look something like this :ALTER TABLE dbo.IPAddresses ADD

IPAddressString AS

CAST((([IPAddress] & 0xFF000000) / 16777216) + 128 AS varchar(3)) + '.' +

CAST(([IPAddress] & 0x00FF0000) / 65536 AS varchar(3)) + '.' +

CAST(([IPAddress] & 0x0000FF00) / 256 AS varchar(3)) + '.' +

CAST(([IPAddress] & 0x000000FF) AS varchar(3))

As a comparitive, I ran the following code to test the differences between the UDF function :SET STATISTICS TIME ON

SELECT [IPAddress], [dbo].[IntegerIPAddressToVarChar]([IPAddress])

FROM [dbo].[IPAddresses]

SET STATISTICS TIME OFF

With the following result :

(100000 row(s) affected)

SQL Server Execution Times:

CPU time = 2156 ms, elapsed time = 4148 ms.

And the computed column :SET STATISTICS TIME ON

SELECT [IPAddress], [IPAddressString]

FROM [dbo].[IPAddresses]

SET STATISTICS TIME OFF

With the following result :

(100000 row(s) affected)

SQL Server Execution Times:

CPU time = 203 ms, elapsed time = 3262 ms.

It's obvious that the computed column is a great choice.

I'm still digging to see how I could further improve the conversion from a string based IP to an integer one, although it may be irrelevant overall.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Very Cool.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

hey, what about using 32 bit fields??

just kidding

😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

TheSQLGuru (8/20/2009)
hey, what about using 32 bit fields??
just kidding
😀

Heh... how about 4 byte ASCII character based? 😛

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jean-Sebastien Carle Old Hand Points: 359 More actions · Answer 8

I'll post an addendum to the article soon discussing the combination of an integer column with a computed human readable column. In the meantime, I retested the four different methods of converting from a varchar(15) to an int using my 100,000 row test table and SET STATISTICS TIME ON/OFF.

Here are the results:

SELECT [dbo].[SUBSTRINGandCHARINDEX]([IPAddressString])

FROM [bdq].[dbo].[IPAddresses](100000 row(s) affected)

SQL Server Execution Times:

CPU time = 2500 ms, elapsed time = 2712 ms.

SELECT [dbo].[PARSENAME]([IPAddressString])

FROM [bdq].[dbo].[IPAddresses](100000 row(s) affected)

SQL Server Execution Times:

CPU time = 2265 ms, elapsed time = 2500 ms.

SELECT [dbo].[ImprovedSUBSTRINGandCHARINDEX]([IPAddressString])

FROM [bdq].[dbo].[IPAddresses](100000 row(s) affected)

SQL Server Execution Times:

CPU time = 2329 ms, elapsed time = 2578 ms.

SELECT [dbo].[ImprovedPARSENAME]([IPAddressString])

FROM [bdq].[dbo].[IPAddresses](100000 row(s) affected)

SQL Server Execution Times:

CPU time = 2171 ms, elapsed time = 2386 ms.

Clive Chinery SSCrazy Points: 2563 More actions · Answer 9

>I've always liked storing IP's as binary(4) - still 4 bytes, but simpler conversions:

Under what licence (if any) are you releasing your code?

May it be included in a project relese under LGPL ?

Clive Chinery SSCrazy Points: 2563 More actions · Answer 10

Both the original article and this thread have been very useful. I now need to reconsider my designs for web databases where audit logging of the apparent IP address is very important.

I commend the suggestion of a computed IPAddressString column.

Jean-Sebastien Carle Old Hand Points: 359 More actions · Answer 11

I have no problem releasing this code under the LGPL. I'll make a mention as such when I made my addendum.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 12

Guys, I just got to thinking about this, but it seems this kind of conversion is right up SQLCLR's alley, once it is in memory I would think it is very fast..

This is effectively the binary(4) method in SQL..

Take string IP Addr and get 4 bytes.. They would be probably SqlBytes in CLR..

Dim _oIP0 As New System.Net.IPAddress(0)

Dim _oIP1 As System.Net.IPAddress

Dim _oAddr As Byte()

_oIP1 = _oIP0.Parse("192.168.1.100")

_oAddr = _oIP1.GetAddressBytes()

Take 4 bytes and get string

Dim _oInBytes As Byte()

ReDim _oInBytes(3)

_oInBytes(0) = 192

_oInBytes(1) = 168

_oInBytes(2) = 1

_oInBytes(3) = 100

Dim _oIP As New System.Net.IPAddress(_oInBytes)

Debug.WriteLine("String IP Addr: " & _oIP1.ToString)

These are just fragments but you can go from the string to varbinary(4) in 5 lines of code and from the varbinary(4) to the string in 2. Once it is loaded I would think it would be really fast..

Thoughts? Flames?

CEWII

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 13

Bump..

Didn't show up in active threads.. How odd..

CEWII

Clive Chinery SSCrazy Points: 2563 More actions · Answer 14

It would be very interesting to benchmark the CLR solution v the TSQL solution.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 15

I was going to see about writing it tonight to see. I just wish I didn't have to do it in VB.net express, I don't have access to my full environment right now..

CEWII