IP库的优化

前几天拿到一套小程序，里面有个IP库，打开一看，居然还有负数。奇怪的查看了一下它的代码，原来是IPA段大于127的，就减去4294967296，所有就有负数了。看了下字段属性，是长整型的。如果按照原来的做法，access要双精度(double)型，mssql要bigint才放的下。因为int字段的范围是-2,147,483,648 至 2,147,483,647。

算了一下：
128.0.0.0 : 128 * 256 * 256 * 256 - 1 = 2147483647
127.255.255.255 : (127 * 256 * 256 * 256) + (255 * 256 * 256) + (255 * 256) + 255 - 1 = 2147483646
255.255.255.255 : 256 * 256 * 256 * 256 - 1 = 4294967295
那程序中ip计算后没有减一，我的有减一，因此更新数据库时应该是减4294967295.
A段<128时计算程序不变，当>127时，直接减4294967296，这样128.0.0.0,255.255.255.255就从-2147483648到0

想想，这样程序的运行效率肯定能提高，于是用查询IP区域地址的程序试验了一下。
原IP库共262678条记录，ip1和ip2分别是范围，
name  rows  reserved  data  index_size  unused
ip  262678       59472 KB  30896 KB  6528 KB  22048 KB
先重建索引一下 DBCC DBREINDEX (ip)
ip　262678       21952 KB  15480 KB  6432 KB  40 KB
索引情况是：
nonclustered located on PRIMARY  ip2
clustered, unique, primary key located on PRIMARY  ip1


将ip1复制为Ip2数据库，
运行update ip2 set ip2=ip2-4294967295,ip1=ip1-4294967295 where ip1>2147483646
执行了半分钟OK，所影响的行数为 208209 行。看来80%的IP数据都在127之后。
将bigint更改为int字段。
查看下表大小：
ip2  262678       15744 KB  11944 KB  3712 KB  88 KB  
小了30%。

事件查看器  

A段小于127的一个测试
select top 1 country,city from ip2_big where 2130706432 between ip1 and ip2
CPU:78 Reads:442 Duration:0

select top 1 country,city from ip2 where 2130706432 between ip1 and ip2
CPU:16 Reads:44 Duration:0

A段大于127的一个测试
执行：select top 1 country,city from ip where ip1<=3658706089 and ip2>=3658706089
CPU:297 Reads:1076 Duration:296

执行：select top 1 country,city from ip2 where -636261206 between ip1 and ip2
CPU:94 Reads:466 Duration:96

均有明显提高！

换成存储过程
Create proc proc_Ip1
 (@sip bigint)
as
SET NOCOUNT ON
 select top 1 country,city from ip where @sip between ip1 and ip2
SET NOCOUNT OFF

GO
proc_Ip1 3658706089
proc_Ip1 2130706432
CPU:188 Reads:991 Duration:203
CPU:78 Reads:398 Duration:76


Create proc proc_Ip
 (@sip int)
as
SET NOCOUNT ON
 select top 1 country,city from ip2 where @sip between ip1 and ip2
SET NOCOUNT OFF

GO

proc_Ip -636261206
proc_Ip 2130706432

CPU:0 Reads:6 Duration:0
CPU:0 Reads:58 Duration:0

再此明显提高。

就这样了，更新上去。