小三，帮优化一下

Question

原帖：http://topic.csdn.net/u/20110825/17/24dd0ac4-962e-43f8-9fbc-5c7404c6e2a9.htmltb1表只有200多记录，BOM1表有30万记录，但是查询10分钟也出不来。能不能帮优化一下。

Answer 1

create table tb1(SO_NUMBER varchar(50),CUSTOMER varchar(50),ITEM_CODE varchar(50))
set nocount on
insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO10-5257','HALLM-01','99-88551-01030')
insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO10-5341','HALLM-02','98-78496-SP417K')
insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO30-0524','HALLM-03','97-94730-02010')
gocreate table BOM1(PARENT varchar(50),ITEM_CODE varchar(50),BOM_UNIT varchar(50))insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('99-88551-01030','97-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-88551-01030','96-88551-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#8003042-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#6300035-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','20-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','21-00001-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','22-00424-01010','G')insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','#98-78496-SP417K','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','97-78496-SP417K','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','96-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#7678496-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#88-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','94-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','#87-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('94-78496-SP2027','90-78496-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','87-02029-00020','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','02-00002-01011','G')
set nocount off
gocreate function get_proC()
returns @ret table(PARENT varchar(50),ITEM_CODE varchar(50))
as
begin
declare @t table(PARENT varchar(50),ITEM_CODE varchar(50),[level] int,numcode varchar(100))
declare @i int
set @i=1
insert into @t
select a.PARENT,a.ITEM_CODE,@i,a.PARENT+'@'+right(100+@i,2) from BOM1 a join tb1 b on a.PARENT=b.ITEM_CODE
while @@rowcount>0
begin
set @i=@i+1
insert into @t
select a.PARENT,a.ITEM_CODE,@i,b.numcode+'@'+right(100+@i,2) from BOM1 a join @t b on a.PARENT=b.ITEM_CODE
where b.level=@i-1
end
insert into @ret
select e.PARENT,t.ITEM_CODE
from @t t join @t e on left(t.numcode,charindex('@',t.numcode)-1)=e.PARENT and e.[level] = 1
where not exists (select 1 from @t where PARENT = t.ITEM_CODE)
return
end
goselect a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,b.ITEM_CODE CODE,c.BOM_UNIT
from tb1 a join dbo.get_proC() b on a.ITEM_CODE = b.PARENT
join BOM1 c on b.ITEM_CODE = c.ITEM_CODEdrop function get_proC
drop table tb1,BOM1/*****************SO_NUMBER                                          CUSTOMER                                           ITEM_CODE                                          CODE                                               BOM_UNIT
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
SO10-5257                                          HALLM-01                                           99-88551-01030                                     #8003042-01010                                     G
SO10-5257                                          HALLM-01                                           99-88551-01030                                     #6300035-01010                                     G
SO10-5257                                          HALLM-01                                           99-88551-01030                                     21-00001-00010                                     G
SO10-5257                                          HALLM-01                                           99-88551-01030                                     22-00424-01010                                     G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    #98-78496-SP417K                                   G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    #98-78496-SP417K                                   G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    #7678496-00010                                     G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    #7678496-00010                                     G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    #88-78496-SP2027                                   G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    #88-78496-SP2027                                   G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    #87-78496-SP2027                                   G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    #87-78496-SP2027                                   G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    87-02029-00020                                     G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    87-02029-00020                                     G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    02-00002-01011                                     G
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    02-00002-01011                                     G(16 行受影响)
这样子呢？

Answer 2

select distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,b.ITEM_CODE CODE,c.BOM_UNIT
from tb1 a join dbo.get_proC() b on a.ITEM_CODE = b.PARENT
    join BOM1 c on b.ITEM_CODE = c.ITEM_CODE加个distinct

Answer 3

distinct一样的会降低效率 还不如全部字段group by

Answer 4

用函数肯定也不行，直接用存储过程注意数据量大的是BOM表

Answer 5

create table tb1(SO_NUMBER varchar(50),CUSTOMER varchar(50),ITEM_CODE varchar(50))
set nocount on
insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO10-5257','HALLM-01','99-88551-01030')
insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO10-5341','HALLM-02','98-78496-SP417K')
insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO30-0524','HALLM-03','97-94730-02010')
gocreate table BOM1(PARENT varchar(50),ITEM_CODE varchar(50),BOM_UNIT varchar(50))insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('99-88551-01030','97-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-88551-01030','96-88551-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#8003042-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#6300035-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','20-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','21-00001-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','22-00424-01010','G')insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','#98-78496-SP417K','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','97-78496-SP417K','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','96-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#7678496-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#88-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','94-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','#87-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('94-78496-SP2027','90-78496-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','87-02029-00020','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','02-00002-01011','G')
set nocount off
goselect distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,b.ITEM_CODE CODE,b.BOM_UNIT,1 as Level
into #t
from tb1 a join BOM1 b on a.ITEM_CODE = b.PARENTdeclare @Level int
set @Level = 1
while exists (
select 1 
from #t a inner join BOM1 b on a.CODE = b.PARENT
where a.Level = @Level
)
begin
insert #t
select distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,isnull(b.ITEM_CODE,a.CODE) CODE,isnull(b.BOM_UNIT,a.BOM_UNIT) as BOM_UNIT,a.Level + 1 as Level
from #t a left join BOM1 b on a.CODE = b.PARENT
where a.Level = @Level
set @Level = @Level  + 1
endselect 
  SO_NUMBER,CUSTOMER,ITEM_CODE,CODE,BOM_UNIT
from #t where Level = @Level
order by SO_NUMBER,CUSTOMER,ITEM_CODE,CODEdrop table #tdrop table tb1,BOM1
试下直接点的速度

Answer 6

那加索引是必须的先加一个吧BOM1(PARENT)

Answer 7

BOM1(PARENT,ITEM_CODE)
应该是主键吧，如果是，索引就不必了

Answer 8

因为这得到的这个表的SQL还要跟别的表关联查询的，12楼的是写成存储过程还是？

Answer 9

因为我是直接在sql server那里查询的，我现在要把这个结果做成SQL，然后跟别的SQL关联查询，然后在网页上显示出来。

Answer 10

可以写个存储过程，要连接的地方请看标记create proc proc_1
asselect distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,b.ITEM_CODE CODE,b.BOM_UNIT,1 as Level
into #t
from tb1 a join BOM1 b on a.ITEM_CODE = b.PARENTdeclare @Level int
set @Level = 1
while exists (
    select 1 
    from #t a inner join BOM1 b on a.CODE = b.PARENT
    where a.Level = @Level
    )
begin    
    insert #t
    select distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,isnull(b.ITEM_CODE,a.CODE) CODE,isnull(b.BOM_UNIT,a.BOM_UNIT) as BOM_UNIT,a.Level + 1 as Level
    from #t a left join BOM1 b on a.CODE = b.PARENT
    where a.Level = @Level
    set @Level = @Level     + 1
endselect 
  a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,a.CODE,a.BOM_UNIT
  -- 其他表字段
from #t a
--,tb1 b   --其他表   
where a.Level = @Level
--and  ... 连接条件
order by a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,a.CODE
drop table #tgo