優化一條SQL

[yingqing]

select billno,itemno,matno,(select matnm from bmt_mat b where b.matno= a.matno),
unitnm,pqty+lqty as qty,plus from mit_bill_detail a
where a.matno = 'AA0019001010' and a.facno = 'WHT03111034' and facid = 'A'
union all
select c.billno,c.itemno,c.matno, (select matnm from bmt_mat d where d.matno= c.matno),
c.unitnm,(select qty from mit_bill_attach where billno = c.billno and seq = c.seq and facno ='WHT03111034' and facid = 'A' ),plus from mit_bill_detail c
where c.matno = 'AA0019001010'
and c.billno in (select billno from mit_bill_attach where
facno ='WHT03111034' and facid = 'A' )

誰能夠幫我優化一下上面的語句，感覺寫得好累贅啊

[yingqing]

呵呵，自己up

[powar]

将in改为exists性能会好很多.
c.billno in (select billno from mit_bill_attach where
facno ='WHT03111034' and facid = 'A' )
改成:
exists (select billno from mit_bill_attach where
c.billno = mit_bill_attach.billno
and facno ='WHT03111034' and facid = 'A' )

[railgunman]

增加索引

[liuyi8903]

稍作个修改,你试一下!要学会利用查看执行计划来进行调整.
另外,可以适当地建立索引列.
SELECT BILLNO,
ITEMNO,
MATNO,
(SELECT MATNM FROM BMT_MAT B WHERE B.MATNO = A.MATNO),
UNITNM,
PQTY + LQTY AS QTY,
PLUS
FROM MIT_BILL_DETAIL A
WHERE A.MATNO = 'AA0019001010'
AND A.FACNO = 'WHT03111034'
AND FACID = 'A'
UNION ALL
SELECT C.BILLNO,
C.ITEMNO,
C.MATNO,
(SELECT MATNM FROM BMT_MAT D WHERE D.MATNO = C.MATNO),
C.UNITNM,
(SELECT QTY
FROM MIT_BILL_ATTACH
WHERE BILLNO = C.BILLNO
AND SEQ = C.SEQ
AND FACNO = 'WHT03111034'
AND FACID = 'A'),
PLUS
FROM MIT_BILL_DETAIL C
WHERE C.MATNO = 'AA0019001010'
AND EXISTS (SELECT BILLNO
FROM MIT_BILL_ATTACH
WHERE FACNO = 'WHT03111034'
AND FACID = 'A'
AND C.BILLNO = MIT_BILL_ATTACH.BILLNO)

[wupangzi]

对楼主的语句，由于不知道，你设计库的表主要目的！只能考虑语句上优化！如有问题，请说明！
从语句上，只能分两句进行优化！
1：
select billno,itemno,matno,
(select matnm from bmt_mat b where b.matno= a.matno),
unitnm,pqty+lqty as qty,plus
from mit_bill_detail a
where a.matno = 'AA0019001010' and a.facno = 'WHT03111034' and facid = 'A'
2：
select c.billno,c.itemno,c.matno,
(select matnm from bmt_mat d where d.matno= c.matno),
c.unitnm,
(select qty from mit_bill_attach where billno = c.billno and seq = c.seq and facno ='WHT03111034' and facid = 'A' ),plus
from mit_bill_detail c
where c.matno = 'AA0019001010' and c.billno in
(select billno from mit_bill_attach where facno ='WHT03111034' and facid = 'A' )
对第一个语句：只有一处可以优化，就是对条件里，把能过滤最大数据，或者有索引的放到最后。
对第二个语句：
首先看到：
c.billno in
(select billno from mit_bill_attach where facno ='WHT03111034' and facid = 'A' )改为exists
(select billno from mit_bill_attach where c.billno = billno and facno ='WHT03111034' and facid = 'A' )
,接着如果，子查询条件中的facno ='WHT03111034' and facid = 'A' 能外移的话，一定要放到外面，这样可以快很多！条件上，也应该遵从，条件多的放到后面（视具体编译看，可以放最前最后调试看）。

如果想最优化，可以把表结构和各表之间的管理贴出来，让大家讨论一下！

[北极海hein]

同意 wupangzi(无本之木) ( )

[yingqing]

謝謝大家，給分，哈哈…………