现在有一批从table A提取的id号(很不规则的,是客服手工提取的),要与table B做jion,但id号太多了,有10万之多
我用以下语句进行运行,爆慢无比
select aa.id ,bb.name from
(select 1 as id union all
select 1 as id union all
...
select 10000 as id ) as aa
innet join table B as bb
on aa.id=bb.id
测试了下,主要问题出现在
select 1 as id union all
select 1 as id union all
...
select 10000 as id 这一段话上,求优化方法···
表说用in,那个更慢
我用以下语句进行运行,爆慢无比
select aa.id ,bb.name from
(select 1 as id union all
select 1 as id union all
...
select 10000 as id ) as aa
innet join table B as bb
on aa.id=bb.id
测试了下,主要问题出现在
select 1 as id union all
select 1 as id union all
...
select 10000 as id 这一段话上,求优化方法···
表说用in,那个更慢
select id
into #A
from (
select 1 as id union all
select 1 as id union all
...
select 10000 as id) as a
绝对要比你的语句要快
select id,name from B where B.id in(/*id们*/)
if object_id('tempdb..#tb_Collect')is not null
drop table #tb_Collect
Goselect 1 as id Into #tb_Collect union all
select 1 as id union all
...
select 10000 as idCreate clustered index ix_#tb_Collect_id On #tb_Collect(id)Select a.id,b.Name from #tb_Collect As a
Inner join tb_B As b On a.id=b.id
elect 1 as id union all
select 1 as id union all
...
select 10000 as id寫入一個table AA中,在table AA的id字段創建一個聚集索引,再與table B關聯。
select b.ID,b.Name From b where exists(select 1 from AA where ID=b.ID)