select a.t1,a.t3,a.t4,a.t5,b.t6,b.t7,a.t8,a.t9,a.t10 from a1,b1 where b.t2 like '%" & str & "%' b.t2=a.t1 order by a.t1 这是我的sql语句
select a.t1,a.t3,a.t4,a.t5,b.t6,b.t7,a.t8,a.t9,a.t10 from a1 a,b1 b where a.t1=b.t2 and b.t2 like '%str%' order by a.t1;或者select a.t1,a.t3,a.t4,a.t5,b.t6,b.t7,a.t8,a.t9,a.t10 into #Temp from a1 a,b1 b where a.t1=b.t2 and b.t2 like '%str%'; select * from #Temp order by t1
select a.t1,a.t3,a.t4,a.t5,b.t6,b.t7,a.t8,a.t9,a.t10 from a1 a,b1 b where a.t1=b.t2 and b.t2 like '%str%' order by a.t1;或者select a.t1,a.t3,a.t4,a.t5,b.t6,b.t7,a.t8,a.t9,a.t10 into #Temp from a1 a,b1 b where a.t1=b.t2 and b.t2 like '%str%'; select * from #Temp order by t1
但有个界面是单独查询a1表的,速度就比较正常, select t1,t3,t4,t5,t8,t9,t10 from a1 where a.t1 like '%" & str & "%' order by t1 oder by t1 一直没明白是咋回事儿。
我在本机上测试过了where b.t2 like '%" & str & "%' and b.t2=a.t1 要比where b.t2=a.t1 and b.t2 like '%" & str & "%' 快
所以先取数据再关联 这样应该是最快的。select a.t1,a.t3,a.t4,a.t5,a.t8,a.t9,a.t10 into #Temp from a1 a where a.t1 like '%str%'; a.t1,a.t3,a.t4,a.t5,b.t6,b.t7,a.t8,a.t9,a.t10 from #temp a,b1 b where a.a1=b.b2 order by a.t1;
select a.t1,a.t3,a.t4,a.t5,b.t6,b.t7,a.t8,a.t9,a.t10 from a1 a,b1 b where a.t1=b.t2 and b.t2 like '%str%' order by a.t1;或者select a.t1,a.t3,a.t4,a.t5,b.t6,b.t7,a.t8,a.t9,a.t10 into #Temp from a1 a,b1 b where a.t1=b.t2 and b.t2 like '%str%';
select * from #Temp order by t1
select a.t1,a.t3,a.t4,a.t5,b.t6,b.t7,a.t8,a.t9,a.t10 from a1 a,b1 b where a.t1=b.t2 and b.t2 like '%str%' order by a.t1;或者select a.t1,a.t3,a.t4,a.t5,b.t6,b.t7,a.t8,a.t9,a.t10 into #Temp from a1 a,b1 b where a.t1=b.t2 and b.t2 like '%str%';
select * from #Temp order by t1
select t1,t3,t4,t5,t8,t9,t10 from a1 where a.t1 like '%" & str & "%' order by t1 oder by t1
一直没明白是咋回事儿。
所以先取数据再关联
这样应该是最快的。select a.t1,a.t3,a.t4,a.t5,a.t8,a.t9,a.t10 into #Temp
from a1 a where a.t1 like '%str%';
a.t1,a.t3,a.t4,a.t5,b.t6,b.t7,a.t8,a.t9,a.t10
from #temp a,b1 b
where a.a1=b.b2 order by a.t1;
说不定是查询时
内存 cpu都跟不上