請問這個sql語法如何改進執行速度? |
|
尚未結案
|
|
kartan_01
一般會員   發表：42 回覆：60 積分：20 註冊：2004-12-08 發送簡訊給我 |
select detcode,sum(amtcr) as sumcr,sum(amtdr) as sumdr,acccod_ord.order_seq from accpos t,OUTER acccod_ord
where exists (select * from accpos t1 where t1.vouno = t.vouno and t1.acccode in ('2112'','2113','2114'))
AND not exists (select * from accpos t2 where t2.vouno = t.vouno and t2.acccode = '2110')
AND not exists (select * from accpos_m t3 where t3.detcode = t.detcode)
AND exists (select * from accpos t4 where t4.detcode = t.detcode and t4.sumry[1,1]='D')
AND sumry[1,1]='D' AND t.detcode=acccod_ord.code
group by detcode,acccod_ord.order_seq order by acccod_ord.order_seq,sumcr DESC,detcode
|
|
Arlung Miao
初階會員  發表：9 回覆：44 積分：25 註冊：2004-08-25 發送簡訊給我 |
朋友，
你的Database是否為Informix。由於我沒有測試環境，所以沒有測試。
另外，你是否可以考慮使用臨時表 以下是個簡單的説明：
1. 從你提供的SQL來看，vouno應該不是一個Unique Key。所以，當你使用select * from accpos t1 where t1.vouno = t.vouno and t1.acccode in ('2112'','2113','2114')時，會返回一個非常大的結果集(如果vouno的重復性非常高的情況下)，造成速度非常慢 2. 從整個語句來看，以下條件似乎是多餘的:
AND exists (select * from accpos t4 where t4.detcode = t.detcode and t4.sumry[1,1]='D') 3. 由於你提出的資訊有限，所以我假設在vouno的重復性比較高（比如整個表使用DISTINCT vouno後只有百來個vouno）的情況下來改寫SQL SELECT detcode, sum(amtcr) as sumcr, sum(amtdr) as sumdr, accod_ord.order_seq
FROM accpos t
, OUTER acccod_ord
--WHERE exists (select * from accpos t1 where t1.vouno = t.vouno and t1.acccode in ('2112'','2113','2114'))
WHERE vouno IN (SELECT DISTINCT vouno FROM accpos WHERE acccode in ('2112','2113','2114'))
-- AND not exists (select * from accpos t2 where t2.vouno = t.vouno and t2.acccode = '2110')
AND NOT IN (SELECT DISTINCT vouno FROM accpos WHERE acccode = '2110')
-- AND exists (select * from accpos t4 where t4.detcode = t.detcode and t4.sumry[1,1]='D')
AND sumry[1,1] = 'D'
AND t.detcode = acccod_ord.code
GROUP BY detcode,acccod_ord.order_seq
ORDER BY acccod_ord.order_seq, sumcr DESC,detcode 發表人 - Arlung Miao 於 2005/05/16 17:20:45 發表人 - Arlung Miao 於 2005/05/16 17:26:01
|
|
ko
資深會員 發表：28 回覆：785 積分：444 註冊：2002-08-14 發送簡訊給我 |
kartan_01 你好:
引言: select detcode,sum(amtcr) as sumcr,sum(amtdr) as sumdr,acccod_ord.order_seq from accpos t,OUTER acccod_ord where exists (select * from accpos t1 where t1.vouno = t.vouno and t1.acccode in ('2112'','2113','2114')) AND not exists (select * from accpos t2 where t2.vouno = t.vouno and t2.acccode = '2110') AND not exists (select * from accpos_m t3 where t3.detcode = t.detcode) AND exists (select * from accpos t4 where t4.detcode = t.detcode and t4.sumry[1,1]='D') AND sumry[1,1]='D' AND t.detcode=acccod_ord.code group by detcode,acccod_ord.order_seq order by acccod_ord.order_seq,sumcr DESC,detcode試著把*改成單一值看會不會好一點 因為這個語法結構是還不錯,除了建proc之外因該沒法度了吧
------
====================== 昏睡~ 不昏睡~ 不由昏睡~ |
發表時間：2005-05-16 15:16:23
IP:61.221.xxx.xxx 未訂閱
系統時間：2024-06-17 6:22:10
聯絡我們 | Delphi K.Top討論版
| 本站聲明 |
|
1. 本論壇為無營利行為之開放平台，所有文章都是由網友自行張貼，如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權，而損及您的利益，請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨，是藍是綠本站無權干涉，但這裡不是政治性論壇！ |