Table field instead compute it's call a custom function

Transact-SQL
1

Hi Sir/Madam,

I have a table with LU_IP2Loc with contains 2 fields:

  1. ipnum_FROM
  2. ipnum_TO

I want to create extra 2 fields which using my custom function like compute method style below:

alter table LU_IP2Loc
add ipstr_from nvarchar(max) AS dbo.fn_getIPAdd(ipnum_FROM)

alter table LU_IP2Loc
add ipstr_to nvarchar(max) AS dbo.fn_getIPAdd(ipnum_TO)

create function dbo.fn_getIPAdd(@what as int) returns nvarchar(max)
As
BEGIN
return (select IP=
convert(varchar(3), (@what/16777216) & 255) + '.'
+ convert(varchar(3), (@what/65536) & 255) + '.'
+ convert(varchar(3), (@what/256) & 255) + '.'
+ convert(varchar(3), @what & 255))

END

But, I can't do that. Any good solutions to perform like compute field method?

Please advise.

Regards,
Micheale

2

Use this:

alter table dbo.LU_IP2Loc
add ipstr_from  AS dbo.fn_getIPAdd(ipnum_FROM)

alter table LU_IP2Loc
add ipstr_to  AS dbo.fn_getIPAdd(ipnum_TO)

One remark : returns nvarchar(max)?
an IP address could be 15 length

1 Like
3

Hi,

I manage to do so. But the performance is super slow. Do you have any idea how to optimize the speed for compute fields? As the partition key is non-deterministic.

ALTER function [dbo].[fn_getLoc2IPRecNo](@IP_Address nvarchar(18))
returns int
WITH SCHEMABINDING
As
Begin
Return (select top 1 RecNo from dbo.LU_IP2Loc k where ((256 * 256 * 256 * CAST(PARSENAME(@IP_Address, 4) AS float) + 256 * 256 * CAST(PARSENAME(@IP_Address, 3) AS float) + 256 * CAST(PARSENAME(@IP_Address, 2) AS float) + CAST(PARSENAME(@IP_Address, 1) AS float))) between k.ip_FROM and k.ip_TO)

End

alter table dbo.IP_Master
add ip2loc_RecNo AS dbo.fn_getLoc2IPRecNo(IP_Address)

select IPRecord_No, IP_Addresses from IP_Master => Very fast for large data - within few seconds execution
**select IPRecord_No, IP_Addresses, ip2loc_RecNo from IP_Master => Took more than few minute for large **
data execution

Please advise.

Regards,
Micheale

4

  • Try with index on it

  • try the approach with CROSS APPLY like:

    select
    IPRecord_No, IP_Addresses, ca.ip2loc_RecNo
    from IP_Master
    cross apply
    (select
    convert(varchar(3), (IP_Addresses/16777216) & 255) + '.'
    + convert(varchar(3), (IP_Addresses/65536) & 255) + '.'
    + convert(varchar(3), (IP_Addresses/256) & 255) + '.'
    + convert(varchar(3), IP_Addresses & 255)
    )ca(ip2loc_RecNo )

and execution plan it's important also the dm_os_waiting_tasks