__Holly__
asked on
UDF for Fastest Possible IP translation
hey i am working on a LARGE analysis services/datamart project where i need to group by different networks of ip addresses.
i have more than 64,000 IP addresses (especially for the destination fields)
i need to:
1) store these the most efficiently (smallest). isnt integer the smallest way to do this?
2) convert these (to the destination format the most efficiently)
3) roll these up into different networks (ie i think that i want a table where i store:
IP_ADDY PARENT_IP
192.168.5.112 192.168.5.0
192.168.5.113 192.168.5.0
my preferences are to not use any DTS/vba for this (because i am using bcp/bulk insert)
and the UDF that I have previously used seems WWWWWAAAAAAAAYYYYYYY tooo slow.
i have seen people use an extended stored proc to do this (written in C++ i assume)-- is this utterly the fastest way to do this?
i am talking about 300million new records every day.
thanks!!
i have more than 64,000 IP addresses (especially for the destination fields)
i need to:
1) store these the most efficiently (smallest). isnt integer the smallest way to do this?
2) convert these (to the destination format the most efficiently)
3) roll these up into different networks (ie i think that i want a table where i store:
IP_ADDY PARENT_IP
192.168.5.112 192.168.5.0
192.168.5.113 192.168.5.0
my preferences are to not use any DTS/vba for this (because i am using bcp/bulk insert)
and the UDF that I have previously used seems WWWWWAAAAAAAAYYYYYYY tooo slow.
i have seen people use an extended stored proc to do this (written in C++ i assume)-- is this utterly the fastest way to do this?
i am talking about 300million new records every day.
thanks!!
One approach you can take is to store the IP as a varchar column and use the T-SQL PARSENAME function to pull out individual parts of the IP address. Yes, I realize this use of the PARSENAME function is not very orthodox as it is not intended for that purpose, but does do the job very well.
Anthony
Anthony
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, just read your post again and I see that this is for OLAP (should have figured).
Btw, if you go the varchar route, then no UDF is necessary. Otherwise, you could do something like this:
Insert INTO IPTable (
firstOctet,
secondOctet,
thirdOctet,
fourthOctet)
Select
ParseName(fullIPAddress, 1),
ParseName(fullIPAddress, 2),
ParseName(fullIPAddress, 3),
ParseName(fullIPAddress, 4)
From MyTable
Paul
Btw, if you go the varchar route, then no UDF is necessary. Otherwise, you could do something like this:
Insert INTO IPTable (
firstOctet,
secondOctet,
thirdOctet,
fourthOctet)
Select
ParseName(fullIPAddress, 1),
ParseName(fullIPAddress, 2),
ParseName(fullIPAddress, 3),
ParseName(fullIPAddress, 4)
From MyTable
Paul
Do you still need help with this old open question?
She's "deadly with SQL Server & Access" but not very good at maintaining her questions....
LOL, but we can only hope.
ASKER
sorry guys.
THANKS A TON that is exactly what i needed; and i am sure it is faster than all that other BS i was writing by hand.
i work for the big M and i report on internal traffic... the destination IPs have a high cardinality, but the source IPs are a lil less dynamic.
i think that i have something like 800k client ip addresses; but im still not too familiar with the real shape of it.
THANKS A TON that is exactly what i needed; and i am sure it is faster than all that other BS i was writing by hand.
i work for the big M and i report on internal traffic... the destination IPs have a high cardinality, but the source IPs are a lil less dynamic.
i think that i have something like 800k client ip addresses; but im still not too familiar with the real shape of it.
As far as DTS, you can also use the Bulk Insert task within DTS :)
Have you thought about simply keeping the IPs stored as an integer and putting a view on top of the data for the users that add the "IP formatting" on the fly?