August 11, 2009 at 2:23 pm
Comments posted to this topic are about the item Storing IPv4 Addresses for Performance
August 19, 2009 at 4:03 am
Nice article. Very informative.
But at the final function where 128 is subtracted from the result of ParseName, before being multiplied by 16777216 (2^24), could potentially yield negative results, if the first final octet is not above 128.
For example: 127.0.0.1 would result in -16777215.
August 19, 2009 at 5:21 am
1 question:
What if we create the solution using CLR? Is it possible? And what about the performance, since it's compiled?
Thanks!!!
August 19, 2009 at 6:16 am
I store these as four tinyint columns. Also uses just four bytes but is human-readable, does not require all your conversions, has implicit validation, and better meets the 1NF goal of atomicity.
August 19, 2009 at 6:35 am
nicholastjh (8/19/2009)
Nice article. Very informative.But at the final function where 128 is subtracted from the result of ParseName, before being multiplied by 16777216 (2^24), could potentially yield negative results, if the first final octet is not above 128.
For example: 127.0.0.1 would result in -16777215.
That's the whole point. Some IP addresses will map to negative integers. As there are 2^32 IP Addresses and 2^31 non-negative integers in SQL Server we have to use positive as well as negative values.
The idea with four columns - while not bad - has some flaws:
1. There is no merits with 1NF as IP address is a complete being. Would you store postal code with few char(1) columns?
2. If you want to perform table wide bit operation with mask (mask may be from 0 to 32 actually) you will have to either map four tinyints to one int the same way it is presented in the article or map it to bigint. Not good for performance :>
If you want readability - just use view with some ip_int_to_ip_string computed column.
August 19, 2009 at 6:36 am
Darn it HansVE, you beat me to the punch! I was going to say exactly the same thing. I don't see why someone would go to all of the trouble to convert IPv4 values this way, and back again, when it's far easier just to store them as four tinyint values.
August 19, 2009 at 7:40 am
I've always liked storing IP's as binary(4) - still 4 bytes, but simpler conversions:
DECLARE @IpBin binary(4)
DECLARE @IpString varchar(15)
SET @IpString = '101.202.33.44'
--convert string to binary
SET @IpBin =CONVERT(binary(1), CONVERT(tinyint, PARSENAME(@IpString, 4)))
+ CONVERT(binary(1), CONVERT(tinyint, PARSENAME(@IpString, 3)))
+ CONVERT(binary(1), CONVERT(tinyint, PARSENAME(@IpString, 2)))
+ CONVERT(binary(1), CONVERT(tinyint, PARSENAME(@IpString, 1)))
SELECT @IpBin
--convert binary to string
SELECT
CAST(CAST(SUBSTRING(@IpBin,1,1) AS tinyint) AS varchar(3)) + '.' +
CAST(CAST(SUBSTRING(@IpBin,2,1) AS tinyint) AS varchar(3)) + '.' +
CAST(CAST(SUBSTRING(@IpBin,3,1) AS tinyint) AS varchar(3)) + '.' +
CAST(CAST(SUBSTRING(@IpBin,4,1) AS tinyint) AS varchar(3))
August 19, 2009 at 7:49 am
nimdil (8/19/2009)
The idea with four columns - while not bad - has some flaws:
1. There is no merits with 1NF as IP address is a complete being. Would you store postal code with few char(1) columns?
2. If you want to perform table wide bit operation with mask (mask may be from 0 to 32 actually) you will have to either map four tinyints to one int the same way it is presented in the article or map it to bigint. Not good for performance :>
If you want readability - just use view with some ip_int_to_ip_string computed column.
1. You do not know of subnetting? Do you also store first and last names of people together in one column?
2. "Table wide bit operation" can be done using more than one column. Masks also are typically written by octet(s).
A "ip_int_to_ip_string computed column" will impede performance, as will the functions in the article.
The only "flaw" is that predicates, joins, etc. may need to use more than one column, and so more typing. The flaws with using integer are obvious from the need for such an article as this.
August 19, 2009 at 8:24 am
I have to admit the four tiniyints was my first idea but I realized the limitations imposed by that solution immediately, yes its human readable, so what, add the function to your select statement, problem solved.
I believe the binary(4) solution also has merit. I would be curious how it stacks up against the others, since it is just a cast operation and no math.
When the four octets are stored in a single field you are able to do IN operations or much cleaner (and I'm sure faster) correlated sub-queries.
I just see more options with a single column solution and will not be particularly worried about what NF it fits it. Human readability is not a major concern in the DB, if that was then we would never store ID values for a customer id, we would store all the customer information in the table with the order and I think we can agree that is NOT a good solution. Human readability can be achieved with minimal effort if necessary.
Also, as with any problem there is more than one solution, the four tinyints may be just fine for your app, but I know that the next time I need to store IP addresses this article will come to mind..
CEWII
August 19, 2009 at 8:24 am
tdcheek (8/19/2009)
I've always liked storing IP's as binary(4) - still 4 bytes, but simpler conversions:
I like this format as well, since that's essentially what IP addresses are anyway. Like using INT and four separate TINYINT columns, it is efficient on space and has implicit validation. However, it has a few other advantages I like:
1) The queries I perform are very simple and efficient. Most of them are sargable. You don't have the problem of half the address space being negative that you have with INT. It is very easy to say:
WHERE @IPAddress BETWEEN RangeLowerLimit AND RangeUpperLimit
2) When viewed in a query window, it is represented as a hexadecimal sequence. That makes it just as easy to compare two addresses visually as the dotted notation.
3) It's relatively simple to convert to dotted notation if necessary.
Andrew
--Andrew
August 19, 2009 at 8:31 am
Andrew (8/19/2009)
tdcheek (8/19/2009)
I've always liked storing IP's as binary(4) - still 4 bytes, but simpler conversions:I like this format as well, since that's essentially what IP addresses are anyway. Like using INT and four separate TINYINT columns, it is efficient on space and has implicit validation. However, it has a few other advantages I like:
1) The queries I perform are very simple and efficient. Most of them are sargable. You don't have the problem of half the address space being negative that you have with INT. It is very easy to say:
WHERE @IPAddress BETWEEN RangeLowerLimit AND RangeUpperLimit3) It's relatively simple to convert to dotted notation if necessary.
Andrew
I think the conversion to dotted notation is pretty easy either way, but I agree we get some built-in validation. I think the INT solution could work in a WHERE clause, but I think it would be more complex and have to take into account that and 128.0.0.0 we went negative, that being said, I think the binary(4) method is probably better but still all in all a thought provoking article.
CEWII
August 19, 2009 at 8:41 am
tdcheek (8/19/2009)
I've always liked storing IP's as binary(4)
I had considered using binary(4) however since bitwise operations cannot be performed on binary, this made integer my final choice.
HansVE (8/19/2009)
1. You do not know of subnetting?
Subnetting can easily be achieved using bitmasking on integer IPs. The following will yield 192.168.1.0 :
SELECT [dbo].[IPAddressToString]([dbo].[IPAddressToInteger]('192.168.1.1') & [dbo].[IPAddressToInteger]('255.255.255.0'))
HansVE (8/19/2009)
A "ip_int_to_ip_string computed column" will impede performance, as will the functions in the article.
Although using multiple tinyint fields requires no computational conversion, it does require that every ORDER BY, UNION, WHERE, etc operation be done four times, whilst a single integer field requires only one operation in all instances.
Also, if you wish to submit your IP address as a varchar(15) to the stored procedure to be then stored as four tinyint field within the table, you will require the same string splitting conversion as a single integer. Selecting rows to return IP addresses as varchar(15) will also require similar conversions. The only computational difference is mostly the avoidance of a rather efficient bitmask operation, which in my opinion, far outweights the costs of multiple operations when joining, filtering or ordering tables by IP addresses stored as four tinyint fields.
August 19, 2009 at 8:45 am
could you please redo your testing using a char(17) datatype instead of a varchar(17)?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 19, 2009 at 8:48 am
TheSQLGuru (8/19/2009)
could you please redo your testing using a char(17) datatype instead of a varchar(17)?
I'm not sure what would be the point since IP addresses (when used as strings) are not fixed length.
August 19, 2009 at 8:52 am
lhowe (8/19/2009)
Darn it HansVE, you beat me to the punch! I was going to say exactly the same thing. I don't see why someone would go to all of the trouble to convert IPv4 values this way, and back again, when it's far easier just to store them as four tinyint values.
There could be a performance benefit. My own tests a while back indicated that one int comparison was equally efficient as smallint or tinyint, probably because you're using a computer with 32-bit registers. The bit datatype stunk - but this was on SQL Server 2000 or maybe even 7. Still, I prefer to put bit in tinyint.
I agree however that four octets is useful in many ways. There's nothing to stop you storing an int or binary(4) version as well.
I spent a little while devising an encoding of 6 character alphanumeric strings (09AZ) into int, for an indexed key field. I got slightly poorer performance with int than with char(6) even not counting the cost of conversion, which wasn't what I expected. I have increased my knowledge of indexing since, too (apparently non-declared-unique indexes have 8 bytes arbitrarily added to the key??) Perhaps I got it wrong.
Anyway, maybe we should be thinking about IPv6, where the address is, what, 16 bytes? Plus refinements that the Wikipedia page hasn't yet communicated to me. 😉 Binary(16) for that, I think. Although IPv4 is not going away quickly yet.
Viewing 15 posts - 1 through 15 (of 100 total)
You must be logged in to reply to this topic. Login to reply