create function f_checkIP(@IP varchar(20),@IP_ST varchar(20),@IP_ED varchar(20))
returns bit
as
begin
select @IP = right('00'+ParseName(@IP,4),3)+'.'+
right('00'+ParseName(@IP,3),3)+'.'+
right('00'+ParseName(@IP,2),3)+'.'+
right('00'+ParseName(@IP,1),3),
@IP_ST = right('00'+ParseName(@IP_ST,4),3)+'.'+
right('00'+ParseName(@IP_ST,3),3)+'.'+
right('00'+ParseName(@IP_ST,2),3)+'.'+
right('00'+ParseName(@IP_ST,1),3),
@IP_ED = right('00'+ParseName(@IP_ED,4),3)+'.'+
right('00'+ParseName(@IP_ED,3),3)+'.'+
right('00'+ParseName(@IP_ED,2),3)+'.'+
right('00'+ParseName(@IP_ED,1),3)
if (@IP between @IP_ST and @IP_ED)
return 1
return 0
end
go
select dbo.f_checkIP('4.208.48.215','4.43.69.96','4.208.48.255')
select dbo.f_checkIP('4.43.69.95' ,'4.43.69.96','4.208.48.255')
drop function f_checkIP
go
根据上面例子,需要查询table中匹配的ip段,如下
select top 1 * from table where dbo.f_checkIP(ip, col1, col2) = 1
这个查询,如果查询的ip在表前面点,效率还行,但是 匹配在后面的话,就是 2 、3秒的查询时间,
请问大家,怎么可以优化下。谢
returns bit
as
begin
select @IP = right('00'+ParseName(@IP,4),3)+'.'+
right('00'+ParseName(@IP,3),3)+'.'+
right('00'+ParseName(@IP,2),3)+'.'+
right('00'+ParseName(@IP,1),3),
@IP_ST = right('00'+ParseName(@IP_ST,4),3)+'.'+
right('00'+ParseName(@IP_ST,3),3)+'.'+
right('00'+ParseName(@IP_ST,2),3)+'.'+
right('00'+ParseName(@IP_ST,1),3),
@IP_ED = right('00'+ParseName(@IP_ED,4),3)+'.'+
right('00'+ParseName(@IP_ED,3),3)+'.'+
right('00'+ParseName(@IP_ED,2),3)+'.'+
right('00'+ParseName(@IP_ED,1),3)
if (@IP between @IP_ST and @IP_ED)
return 1
return 0
end
go
select dbo.f_checkIP('4.208.48.215','4.43.69.96','4.208.48.255')
select dbo.f_checkIP('4.43.69.95' ,'4.43.69.96','4.208.48.255')
drop function f_checkIP
go
根据上面例子,需要查询table中匹配的ip段,如下
select top 1 * from table where dbo.f_checkIP(ip, col1, col2) = 1
这个查询,如果查询的ip在表前面点,效率还行,但是 匹配在后面的话,就是 2 、3秒的查询时间,
请问大家,怎么可以优化下。谢
http://blog.csdn.net/roy_88/article/details/41826485