有两张表,表A:8万数据,表B:9万数据
我要作的是,将A.Accountname与B.accountname做Like 比对,语法如下
update A Set flag=1 from a,b where a.accountname like b.accountname+'%'
已经运行3个小时没有出结果了,谁能帮我最大限度的优化这条语句的执行效率?不胜感激,分不够可以单独加!!
我要作的是,将A.Accountname与B.accountname做Like 比对,语法如下
update A Set flag=1 from a,b where a.accountname like b.accountname+'%'
已经运行3个小时没有出结果了,谁能帮我最大限度的优化这条语句的执行效率?不胜感激,分不够可以单独加!!
update #radlog_allgroup set flag3=1,fgad3=#radlog_allgroup.acname from #radlog_allgroup,#tele_upload where #radlog_allgroup.acname like '%'+ #tele_upload.formatedname and #tele_upload.[date]=#radlog_allgroup.logondatetimeupdate #radlog_allgroup set flag4=1,fgad4=#radlog_allgroup.acname from #radlog_allgroup,#tele_upload where #tele_upload.formatedname like #radlog_allgroup.acname +'%' and #tele_upload.[date]=#radlog_allgroup.logondatetime
update #radlog_allgroup set flag5=1,fgad5=#radlog_allgroup.acname from #radlog_allgroup,#tele_upload where #tele_upload.formatedname like '%'+#radlog_allgroup.acname and #tele_upload.[date]=#radlog_allgroup.logondatetime
用charindex(a.accountname,b.accountname)=1 试试
Set flag=1
Where A.id in
(select t1.id
from A t1
INNER Join B t2
on t1.accountname like t2.accountname+'%'
where exists(select 1 from B where charindex(A.accountname, B.accountname)=1)
update A Set flag=1 from a,b where patindex(b.accountname + '%',a.accountname) = 1
update #radlog_allgroup set
flag1=(case when A.formatedname=B.acname then 1 else flag1 end),
flag2=(case when charindex(B.acname,A.formatedname)=1 then 1 else flag2 end),
fgad2=(case when charindex(B.acname,A.formatedname)=1 then B.acname else fgad2 end),
flag3=(case when charindex(REVERSE(B.acname),REVERSE(A.formatedname))=1 then 1 else flag3 end),
fgad3=(case when charindex(REVERSE(B.acname),REVERSE(A.formatedname))=B.acname then 1 else flag3 end),
flag4=(case when charindex(A.formatedname,B.acname)=1 then 1 else flag4 end),
fgad4=(case when charindex(A.formatedname,B.acname)=1 then B.acname else fgad4 end),
flag5=(case when charindex(REVERSE(A.formatedname),REVERSE(B.acname))=1 then 1 else flag5 end),
fgad5=(case when charindex(REVERSE(A.formatedname),REVERSE(B.acname))=B.acname then 1 else flag5 end)
from #tele_upload A,#radlog_allgroup B---
where A.[date]=B.logondatetime
实验了下,两个表都是19000+条记录,Select出结果近5分钟.
create table a(
id int identity(1,1) primary key,
accountname nvarchar(30),
flag int
)create table b
(
id int identity(1,1) primary key,
accountname nvarchar(30)
)insert into a (accountname)
select substring(convert(varchar(50),newid()),0,6) from sysobjects a,sysobjects binsert into b (accountname)
select substring(convert(varchar(50),newid()),0,5) from sysobjects,sysobjects b
select * from a where exists (select 1 from b where patindex(b.accountname+'%',a.accountname)<>0)
update A Set flag=1 from a left join b on a.accountname like b.accountname+'%'
charindex(a.accountname ,b.accountname)>0
update A Set flag=1 from a,b where patindex(b.accountname + '%',a.accountname) >0
create index id_temp_radlog_allgroup on #radlog_allgroup(logondatetime,acname)create index id_temp_tele_upload on #tele_upload([date],formatedname)
对于两个表各有8万数据的情况,还是速度太难以接受,我只希望速度能够控制在2个小时即可
----------------------------------------------------------------------------------
.........
2个小时是没法想象的,加索引语句吧
update语句似乎有优化的余地,现在update 次数太多了
似乎使用游标循环一遍的速度,要比目前全部乘积判断要快
顺手写的,米有测试
这样仅仅是循环9W次,速度应该是可以接受的。估计时间都不会超过半个小时
declare @itemcount int,
declare @Accountname varchar(50)
declare C_Check scroll cursor for select Accountname from 表afetch from C_Check into @Accountname
while @@fetch_status = 0set @itemcount=select count(*) from 表b where Accountname like @Accountname+'%'if @itemcount >0
begin
update A Set flag=1 where Accountname =@Accountname
end
fetch from C_Check into @Accountname
close C_CGDTF
deallocate C_CGDTF
1. 改用exist方式的子查询,子查询的条件是left(b.accountname,len(a.accountname))=a.accountname, 这样改成等值查询应该会快点。
2. 上面方法不行,修改数据结构,在b表增加一个字段(1个字符),把b.accountname的首字符放入该字段,并在该字段上做索引,在查询语句中相应增加首字符相等的筛选条件,如果你的数据首字符是大致均匀分布的话,这种方法可以提高速度。
declare @Accountname varchar(50)
declare C_Check scroll cursor for select Accountname from 表afetch from C_Check into @Accountname
while @@fetch_status = 0set @itemcount=select count(*) from 表b where Accountname like @Accountname+'%'if @itemcount >0
begin
update A Set flag=1 where Accountname =@Accountname
end
fetch from C_Check into @Accountname
close C_Check
deallocate C_Check