将符合条件的数据读到datareader中.例如: select * from tb where condition
初次接触性能问题,所以无从下手,请大家帮帮忙,提提意见 拿个例子说明一下吧: 比如要查询Ip所对应的Ip 地区,有两个表A、B两个表 A:有 id Ip Ipaddress(默认为0)这些字段 B:有 Ip1 ip2 country 这些字段(这个表数据量大概100万) 首先我得查询Ipaddress=0的所有行,然后用sqldatareader一行一行读出Ip来,然后转换跟Ip1和Ip2进行比较,是否存在Ip1和Ip2这两个字段值里面,存在则提取country出来,进行更新A表的ipaddress字段,大概流程就是这样,我使用我的该方法非常卡,一万条差不多就挂了,请问怎么去优化它,谢谢!
比如要查询Ip所对应的Ip 地区,有两个表A、B两个表 A:有 id Ip Ipaddress(默认为0)这些字段 B:有 Ip1 ip2 country 这些字段(这个表数据量大概100万) 首先我得查询Ipaddress=0的所有行,然后用sqldatareader一行一行读出Ip来,然后转换跟Ip1和Ip2进行比较,是否存在Ip1和Ip2这两个字段值里面,存在则提取country出来,进行更新A表的ipaddress字段 --------------------这一套可以用sql语句来完成的,lz把相应的转化条件说清楚些,大家就可以写sql了
update a set Ipaddress = b.country from a,b where a.Ipaddress=0 and (a.ip = b.ip1 or a.ip = b.ip2)
找出符合条件的country~select country from B where IP1 in(select distinct replace(IP,',','') from A where Ipaddress=0) or I IP2 in(select distinct replace(IP,',','') from A where Ipaddress=0)
TRY: —————————————————————————————————————————— update A set Ipaddress=B.country from A,B where A.Ipaddress=0 and ((cast(PARSENAME(A.IP ,4) as bigint)*(256^4)+cast(PARSENAME(A.IP ,3) as bigint)*(256^3)+cast(PARSENAME(A.IP ,2) as bigint)*(256^2)+cast(PARSENAME(A.IP ,1) as bigint)*256) between (cast(PARSENAME(B.IP1,4) as bigint)*(256^4)+cast(PARSENAME(B.IP1,3) as bigint)*(256^3)+cast(PARSENAME(B.IP1,2) as bigint)*(256^2)+cast(PARSENAME(B.IP1,1) as bigint)*256) and (cast(PARSENAME(B.IP2,4) as bigint)*(256^4)+cast(PARSENAME(B.IP2,3) as bigint)*(256^3)+cast(PARSENAME(B.IP2,2) as bigint)*(256^2)+cast(PARSENAME(B.IP2,1) as bigint)*256))
select * from tb where condition
拿个例子说明一下吧:
比如要查询Ip所对应的Ip 地区,有两个表A、B两个表
A:有 id Ip Ipaddress(默认为0)这些字段
B:有 Ip1 ip2 country 这些字段(这个表数据量大概100万)
首先我得查询Ipaddress=0的所有行,然后用sqldatareader一行一行读出Ip来,然后转换跟Ip1和Ip2进行比较,是否存在Ip1和Ip2这两个字段值里面,存在则提取country出来,进行更新A表的ipaddress字段,大概流程就是这样,我使用我的该方法非常卡,一万条差不多就挂了,请问怎么去优化它,谢谢!
A:有 id Ip Ipaddress(默认为0)这些字段
B:有 Ip1 ip2 country 这些字段(这个表数据量大概100万)
首先我得查询Ipaddress=0的所有行,然后用sqldatareader一行一行读出Ip来,然后转换跟Ip1和Ip2进行比较,是否存在Ip1和Ip2这两个字段值里面,存在则提取country出来,进行更新A表的ipaddress字段
--------------------这一套可以用sql语句来完成的,lz把相应的转化条件说清楚些,大家就可以写sql了
set Ipaddress = b.country
from a,b
where a.Ipaddress=0 and
(a.ip = b.ip1 or a.ip = b.ip2)
string[] Arrs = strIp.Split('.');
strIp = strIp.Replace(".", "");
long Longs = Convert.ToInt64(strIp);
long SqlIp = Convert.ToInt64(Arrs[0]) * 256 * 256 * 256 + Convert.ToInt32(Arrs[1]) * 256 * 256 + Convert.ToInt32(Arrs[2]) * 256 + Convert.ToInt32(Arrs[3]);(.net):先把strIp转化SqlIp,然后通过SqlIp去判断属于哪个地区!!
where
IP1 in(select distinct replace(IP,',','') from A where Ipaddress=0)
or I
IP2 in(select distinct replace(IP,',','') from A where Ipaddress=0)
string[] Arrs = strIp.Split('.');
strIp = strIp.Replace(".", "");
long Longs = Convert.ToInt64(strIp);
long SqlIp = Convert.ToInt64(Arrs[0]) * 256 * 256 * 256 + Convert.ToInt32(Arrs[1]) * 256 * 256 + Convert.ToInt32(Arrs[2]) * 256 + Convert.ToInt32(Arrs[3]);(.net):先把strIp转化SqlIp,然后通过SqlIp去判断属于哪个地区!!这里消耗很大啊,可以考虑增加 strIp 字段,将IP转换为 strIp,再去比较更新
——————————————————————————————————————————
update A
set
Ipaddress=B.country
from
A,B
where
A.Ipaddress=0
and
((cast(PARSENAME(A.IP ,4) as bigint)*(256^4)+cast(PARSENAME(A.IP ,3) as bigint)*(256^3)+cast(PARSENAME(A.IP ,2) as bigint)*(256^2)+cast(PARSENAME(A.IP ,1) as bigint)*256)
between
(cast(PARSENAME(B.IP1,4) as bigint)*(256^4)+cast(PARSENAME(B.IP1,3) as bigint)*(256^3)+cast(PARSENAME(B.IP1,2) as bigint)*(256^2)+cast(PARSENAME(B.IP1,1) as bigint)*256)
and
(cast(PARSENAME(B.IP2,4) as bigint)*(256^4)+cast(PARSENAME(B.IP2,3) as bigint)*(256^3)+cast(PARSENAME(B.IP2,2) as bigint)*(256^2)+cast(PARSENAME(B.IP2,1) as bigint)*256))