where里面尽量不要使用函数。可以将数据库中的startip,endip转换成000.000.000.000格式直接查询select * from theip where '224.208.48.215' < startip and endip > '224.208.48.215'这样效率上去了。
上面写错了。 select * from theip where startip < '224.208.48.215' and endip > '224.208.48.215'
那偶好人做到底吧:偶说了, 在theip表里添加一个字段: IP_TYPE, 如: IP_ADDRESS IP_TYPE 122.14.12.47 A 2.34.178.84 A 129.89.35.61 B .....在IP_TYPE上建CLUSTERED INDEX. 在你的代码里判断前三位是什么数字: (下面是pseudo code) declare @type char(1) if left(@IP_address, 3) between 0 and 127 then @type = 'A' else if left(@IP_address, 3) between 128 and 191 then @type = 'B' eles if left(@IP_address, 3) between 192 and 223 then @type = 'C' else raiserror ('not a valid IP')select * from theip where IP_TYPE = @type and dbo.f_checkIP('224.208.48.215',startip,endip)=1 ------- 这样是不是清楚一点?
像上面说的一样加入字段type(0.1.2) 如果把数据库数据改成000.000.000.000 格式的话,就可以 create proc IP_Check ( @IP varchar(50)) as declare @IP_3 varchar(10) set @IP_3= right('00'+ParseName(@IP,4),3) 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) if(right('00'+ParseName(@IP,4),3) between '0'and '127') select * from theip where type=0 and @IP between startip and endip else if(right('00'+ParseName(@IP,4),3) between '128'and '191') select * from theip where type=1 and @IP between startip and endip else select * from theip where type=2 and @IP between startip and endipgo exec IP_Check '194.208.48.215' 时间不到1秒
根据你的function, 你是在对theip表进行全扫描, 时间可不就是很长.你能不能先判断输入IP的前三位, 或前六位, 或前九位, (就是根据A, B, C网段来做), 然后在用你的算法, 这样应该会快很多.
晕. 0~127是A网的, 128~191是B网的. 192~223是C网的. 这个你应该清楚吧.
假如输入的IP 是122.14.12.47 先判断它的前三位好像同样要全表扫描吧。
说说你的看法
select * from theip where startip < '224.208.48.215' and endip > '224.208.48.215'
IP_ADDRESS IP_TYPE
122.14.12.47 A
2.34.178.84 A
129.89.35.61 B
.....在IP_TYPE上建CLUSTERED INDEX.
在你的代码里判断前三位是什么数字: (下面是pseudo code)
declare @type char(1)
if left(@IP_address, 3) between 0 and 127
then @type = 'A'
else if left(@IP_address, 3) between 128 and 191
then @type = 'B'
eles if left(@IP_address, 3) between 192 and 223
then @type = 'C'
else
raiserror ('not a valid IP')select * from theip where IP_TYPE = @type and dbo.f_checkIP('224.208.48.215',startip,endip)=1 ------- 这样是不是清楚一点?
然后把IP_type, sub_type1和sub_type2建clustered index, 这样的话应该只扫很少一部分数据了.
如果把数据库数据改成000.000.000.000 格式的话,就可以
create proc IP_Check
( @IP varchar(50))
as
declare @IP_3 varchar(10)
set @IP_3= right('00'+ParseName(@IP,4),3)
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)
if(right('00'+ParseName(@IP,4),3) between '0'and '127')
select * from theip where type=0 and @IP between startip and endip
else if(right('00'+ParseName(@IP,4),3) between '128'and '191')
select * from theip where type=1 and @IP between startip and endip
else
select * from theip where type=2 and @IP between startip and endipgo
exec IP_Check '194.208.48.215'
时间不到1秒