sql 提高执行效率，加快执行速度，4万多条！求指点！！！

程序介绍：两张表a,b合成一张表ae.

a表两列（OrderID（编号），jgz（甲骨字））6155行。

b表两列(OrderID（编号），yw（一段甲骨字）)45515行，yw（一段甲骨字）里的有a表中的甲骨字构成。

ae表三列(记录编号（一段甲骨字的编号),编号（甲骨字编号），次数 （一段甲骨字中每个甲骨字出现的次数））

目的：提高算法执行效率。45515条记录，我执行的半个多小时。谢谢！！！
create table ae
( 记录编号 int,
  编号 int not null,
  次数 int
)
declare @i1 int,@c1 nvarchar(MAX), @i int,@c nvarchar(1),@c3 int
set @i1=1
while @i1<45515
begin
set @c1=
(select yw from b where OrderID=@i1)
  select @i=1  
  while @i <=len(@c1)  
  begin  
select @c=substring(@c1,@i,1),@i=@i+1  
set @c3=
(select OrderID from a where jgz=@c)
  update ac set 次数=次数+1 where 编号=@c3 and 记录编号=@i1  
if @@rowcount=0  
insert into ac (记录编号,编号,次数) values(@i1,@c3,1)  
end
set @i1=@i1+1
end

SQL code

select b.orderID as 记录编号,a.orderID as 编号,
    len(b.yw)-len(replace(b.yw,a.jgz,'')) as 次数
from a,b
where charindex(a.jgz,b.yw) > 0 

SQL code

select
 b.orderid as 记录编号,a.orderid as 编号,len(b.yw)-len(replace(b.yw,a.jgz,'')) as 次数 from
 a,b 
where
 exists(select 1 from b where charindex(a.jgz,b.yw) > 0)

SQL code

create table tba(orderID int,jgz varchar(10))
insert into tba
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e' union all
select 6,'f' union all
select 7,'g'
go

create table tbb(orderID int,yw varchar(100))
insert into tbb
select 1,'good luck' union all
select 2,'good bye'  union all
select 3,'see you tomorrow'
go

select b.orderID as 记录编号,a.orderID as 编号,
    len(b.yw)-len(replace(b.yw,a.jgz,'')) as 次数
from tba as a,tbb as b
where charindex(a.jgz,b.yw) > 0 

drop table tba,tbb

/*******************

记录编号        编号          次数
----------- ----------- -----------
1           3           1
1           4           1
1           7           1
2           2           1
2           4           1
2           5           1
2           7           1
3           5           2

(8 行受影响)

SQL code

select
 b.orderid as 记录编号,a.orderid as 编号,len(b.yw)-len(replace(b.yw,a.jgz,'')) as 次数 
from
 a 
where
 exists(select 1 from b where charindex(a.jgz,b.yw) > 0)

SQL code

select b.orderID as 记录编号,a.orderID as 编号,len(b.yw)-len(replace(b.yw,a.jgz,'')) as 次数
from a,b
where b.yw like '%'+a.jgz+'%'

用循环效率太低了，循环4万次。