create proc [dbo].[UpdateIp]
(@CustomerID bigint)
as
declare @id1 bigint,@id2 bigint
select top 1 @id1=CountID from Count_CustomerInfor where Ipaddress='0'
select @id2=max(NewCountID) from (select top 1000 CountID as NewCountID from Count_CustomerInfor where Ipaddress='0' ) TempTable
update Count_CustomerInfor
set Ipaddress=ip.country from Count_CustomerInfor,ip
where Count_CustomerInfor.CustomerID=@CustomerID and Count_CustomerInfor.Ipaddress='0' and
(Count_CustomerInfor.CountId between @id1 and @id2) and
Convert(bigint,PARSENAME(Count_CustomerInfor.CountIp ,4))*16777216+Convert(bigint,PARSENAME(Count_CustomerInfor.CountIp ,3))*65536+Convert(bigint,PARSENAME(Count_CustomerInfor.CountIp ,2))*256+Convert(bigint,PARSENAME(Count_CustomerInfor.CountIp ,1))
between ip.IP1 and ip.IP2-----------------------------
create proc [dbo].[UpdateIpAddress]
(
@CustomerID bigint
)
as
declare @CountID bigint ,@CountIp bigint
DECLARE Flow_CUR CURSOR FOR
select top 1000 CountID from Count_CustomerInfor where CustomerID=@CustomerID and Ipaddress='0'
OPEN Flow_CUR
fetch next from Flow_CUR into @CountID WHILE (@@FETCH_STATUS) = 0 BEGIN
update Count_CustomerInfor
set Ipaddress=ip.country from Count_CustomerInfor,ip
where cast(PARSENAME(CountIp,4) as bigint)*16777216+cast(PARSENAME(CountIp,3) as bigint)* 65536+cast(PARSENAME(CountIp,2) as bigint)*256 +cast(PARSENAME(CountIp,1) as bigint) between ip.IP1 and ip.IP2 and CountID=@CountId FETCH NEXT FROM Flow_CUR into @CountID
END
CLOSE Flow_CUR
DEALLOCATE Flow_CUR------------------
第一个直接批量处理,第二我用游标,而且1000条循环一次
原本第一个会比较快,但是第一个执行的时间比第二个慢10多分钟,甚至更多!真的不知道是什么问题,贴上大家看看,帮忙一下!
(@CustomerID bigint)
as
declare @id1 bigint,@id2 bigint
select top 1 @id1=CountID from Count_CustomerInfor where Ipaddress='0'
select @id2=max(NewCountID) from (select top 1000 CountID as NewCountID from Count_CustomerInfor where Ipaddress='0' ) TempTable
update Count_CustomerInfor
set Ipaddress=ip.country from Count_CustomerInfor,ip
where Count_CustomerInfor.CustomerID=@CustomerID and Count_CustomerInfor.Ipaddress='0' and
(Count_CustomerInfor.CountId between @id1 and @id2) and
Convert(bigint,PARSENAME(Count_CustomerInfor.CountIp ,4))*16777216+Convert(bigint,PARSENAME(Count_CustomerInfor.CountIp ,3))*65536+Convert(bigint,PARSENAME(Count_CustomerInfor.CountIp ,2))*256+Convert(bigint,PARSENAME(Count_CustomerInfor.CountIp ,1))
between ip.IP1 and ip.IP2-----------------------------
create proc [dbo].[UpdateIpAddress]
(
@CustomerID bigint
)
as
declare @CountID bigint ,@CountIp bigint
DECLARE Flow_CUR CURSOR FOR
select top 1000 CountID from Count_CustomerInfor where CustomerID=@CustomerID and Ipaddress='0'
OPEN Flow_CUR
fetch next from Flow_CUR into @CountID WHILE (@@FETCH_STATUS) = 0 BEGIN
update Count_CustomerInfor
set Ipaddress=ip.country from Count_CustomerInfor,ip
where cast(PARSENAME(CountIp,4) as bigint)*16777216+cast(PARSENAME(CountIp,3) as bigint)* 65536+cast(PARSENAME(CountIp,2) as bigint)*256 +cast(PARSENAME(CountIp,1) as bigint) between ip.IP1 and ip.IP2 and CountID=@CountId FETCH NEXT FROM Flow_CUR into @CountID
END
CLOSE Flow_CUR
DEALLOCATE Flow_CUR------------------
第一个直接批量处理,第二我用游标,而且1000条循环一次
原本第一个会比较快,但是第一个执行的时间比第二个慢10多分钟,甚至更多!真的不知道是什么问题,贴上大家看看,帮忙一下!
解决方案 »
- 这个字符怎么更新掉?
- SQL写好财务报表后,转存储过程老是遇到问题,请教转存储过程的一般思路.
- 求大神帮忙:Sql server 2005 客户端正常,服务器很慢问题
- SQL查询 紧急求教!!
- A字段记录不同的产品名称,B字段产品的加工顺序号;想把不同产品名称对应最大顺序号的行里的C字段更新为NULL。求一个SQL语句,拜托了
- sql问题:高分等你拿(300)!!!!!!!!!!!!!
- 如何在insert一些特殊字符?如单引号,逗号,加号等
- 数据库系统选择
- sos
- 大神帮我看下这个sql语句
- 触发器问题
- 【门外汉】:用delphi+sql server 编译的程序是否可以在没有安装sql server的电脑上运行?
游标比批量处理来的快,所以求大家帮忙看看是不是我批量处理的sql语句有问题-----------实在没分,不然就加上去了,麻烦大家看看了