SQL query简化

johe07

我想了几天，也尝试了几天，可是我还是没有办法简化到我的query,
现在我简化到它出现了timeout expired, 没有简化之前是不会的。。。


我把我的复杂query列出来，希望哪位大大比较有空的，可以指点我迷津一下。。
可能有错误，我却看不到。

---------------------------------------------------------------------

我是通过view连view，结果拿到我要的答案的。以下是我原本的view

SELECT TOP 100 PERCENT a.section_c, a.po_no, CONVERT(char(10), a.po_date, 101) AS po_date, CONVERT(char(10), b.wrhse_date, 101) AS due_date, CONVERT(char(10), b.due_date, 101) AS wrhse_date, a.supplier_c, b.material_no, SUM(ISNULL(b.po_qty, 0)) AS po_qty, b.po_price, d.material_nm,  d.po_unit, c.supplier_nm, e.section_nm, f.curr_c, b.po_price / f.exch_rate AS po_price_base, b.recv_qty + SUM(ISNULL(g.recv_qty, 0)) AS recv_qty, d.unit_confac_po

FROM dbo.po_hdr a INNER JOIN dbo.po_dtl b ON a.section_c = b.section_c AND a.po_no = b.po_no AND b.po_qty > b.recv_qty INNER JOIN dbo.supplier_mst c ON a.supplier_c = c.supplier_c INNER JOIN dbo.material_mst d ON b.material_no = d.material_no INNER JOIN dbo.section_mst e ON a.section_c = e.section_c INNER JOIN dbo.suppord_dtl f ON c.supplier_c = f.supplier_c LEFT OUTER JOIN dbo.material_recv_gi g ON e.section_c = g.section_c AND d.material_no = g.material_no AND b.po_no = g.po_no AND f.supplier_c = a.supplier_c

WHERE (b.section_c = '2405') AND (a.poclosed_flg = '0') AND (a.poissued_flg = '1') AND (b.poclosed_flg = '0')

GROUP BY a.section_c, a.po_no, a.po_date, b.due_date, b.wrhse_date, a.supplier_c, b.material_no, b.po_qty, b.po_price, d.material_nm, d.po_unit, c.supplier_nm, e.section_nm, f.curr_c, b.recv_qty, f.exch_rate, d.unit_confac_po
ORDER BY a.supplier_c, a.po_no, b.material_no

以上是我system原装的其中一个query, 我不打算更改他。


View 1 -- temp1

SELECT TOP 100 PERCENT dbo.material_mst.material_no, dbo.material_mst.material_nm, dbo.invt_mst.invt_qty, dbo.invt_mst.safetyinvt_qty, dbo.temp3.supplier_c, dbo.temp3.supplier_nm, dbo.material_mst.po_unit, dbo.material_mst.unit_confac_po, dbo.material_mst.rmks, dbo.temp3.StdCost, dbo.material_mst.material_kd

FROM dbo.material_mst LEFT OUTER JOIN dbo.temp3 ON dbo.material_mst.material_no = dbo.temp3.material_no LEFT OUTER JOIN dbo.invt_mst ON dbo.material_mst.material_no = dbo.invt_mst.material_no

ORDER BY dbo.material_mst.material_no

View 2 -- temp2

SELECT dbo.matsupp_mst.material_no, dbo.matsupp_mst.order_ltime, dbo.matsupp_mst.trans_ltime, dbo.Backlog.po_qty, dbo.Backlog.recv_qty, dbo.matsupp_mst.lot_size AS MOQ

FROM dbo.Backlog RIGHT OUTER JOIN dbo.matsupp_mst ON dbo.Backlog.material_no = dbo.matsupp_mst.material_no

WHERE (dbo.matsupp_mst.po_ratio <> 0)

View 3 -- temp3

SELECT dbo.matsupp_mst.material_no, dbo.matsupp_mst.supplier_c, dbo.supplier_mst.supplier_nm, dbo.matsupp_mst.po_price / dbo.suppord_dtl.exch_rate AS StdCost

FROM dbo.matsupp_mst INNER JOIN dbo.supplier_mst ON dbo.matsupp_mst.supplier_c = dbo.supplier_mst.supplier_c INNER JOIN dbo.suppord_dtl ON dbo.matsupp_mst.supplier_c = dbo.suppord_dtl.supplier_c

WHERE (dbo.matsupp_mst.po_ratio <> 0)

View 4 -- temp_result

SELECT TOP 100 PERCENT dbo.temp1.supplier_c AS SUPP, dbo.temp1.supplier_nm AS SUPP_NAME, dbo.temp1.material_no AS MATERIALNO, dbo.temp1.material_nm AS PARTNAME, dbo.temp2.order_ltime AS ORDER_LT, dbo.temp2.trans_ltime AS TRANS_LT, dbo.temp1.po_unit AS UNIT, dbo.temp1.invt_qty AS INV_QTY, dbo.temp2.po_qty - dbo.temp2.recv_qty AS BACK_QTY, dbo.temp1.StdCost AS STD_COST, dbo.temp2.MOQ, dbo.temp1.material_kd AS TYPE, dbo.temp1.unit_confac_po AS UNIT_CONFA, dbo.temp1.safetyinvt_qty AS SAFETYINVT, dbo.temp1.rmks AS REMARK

FROM dbo.temp1 LEFT OUTER JOIN dbo.temp2 ON dbo.temp1.material_no = dbo.temp2.material_no

GROUP BY dbo.temp1.material_no, dbo.temp1.material_nm, dbo.temp1.invt_qty, dbo.temp1.safetyinvt_qty, dbo.temp2.order_ltime, dbo.temp2.trans_ltime, dbo.temp1.supplier_nm, dbo.temp1.supplier_c, dbo.temp1.po_unit, dbo.temp1.unit_confac_po, dbo.temp1.rmks, dbo.temp1.StdCost, dbo.temp2.MOQ, dbo.temp2.po_qty - dbo.temp2.recv_qty, dbo.temp1.material_kd

HAVING (dbo.temp2.order_ltime IS NOT NULL) AND (dbo.temp2.trans_ltime IS NOT NULL)

ORDER BY dbo.temp1.supplier_c, dbo.temp1.material_no

View 5 -- ExptResult

SELECT DISTINCT TOP 100 PERCENT dbo.temp_result.SUPP, dbo.temp_result.SUPP_NAME, dbo.temp_result.MATERIALNO, dbo.temp_result.PARTNAME, dbo.temp_result.ORDER_LT, dbo.temp_result.TRANS_LT, dbo.temp_result.UNIT, dbo.temp_result.INV_QTY, dbo.temp_result.BACK_QTY, dbo.temp_result.STD_COST, dbo.temp_result.MOQ, dbo.temp_result.TYPE, dbo.temp_result.UNIT_CONFA, dbo.temp_result.SAFETYINVT, dbo.temp_result.REMARK

FROM dbo.temp_result CROSS JOIN dbo.ExptResult1

WHERE (NOT (dbo.temp_result.MATERIALNO IN
  (SELECT     exptresult1.materialno FROM exptresult1)))

ORDER BY dbo.temp_result.SUPP, dbo.temp_result.MATERIALNO

View 5就是我要的答案，但是出来的结果，有的material_no 没有group 起来，所以他的inv_qty没有加到起来。

我无论在那里一个view里面group material_no, 他都会timeout expired。
所以我决定简化这5个view.

接下来，就是我简化后的结果。

johe07

我知道你们一定看到头晕了。。。
因为，我也是。。。

--------------------------------------------
自己简化后的结果：
BackLog view我没有做更改。


View 1 -- nMRP1

SELECT DISTINCT TOP 100 PERCENT dbo.material_mst.material_no, dbo.material_mst.material_nm, dbo.invt_mst.invt_qty, dbo.invt_mst.safetyinvt_qty, dbo.material_mst.po_unit, dbo.material_mst.unit_confac_po, dbo.material_mst.rmks, dbo.material_mst.material_kd

FROM dbo.material_mst LEFT OUTER JOIN dbo.invt_mst ON dbo.material_mst.material_no = dbo.invt_mst.material_no

ORDER BY dbo.material_mst.material_no

View 2 -- nMRP2

SELECT DISTINCT TOP 100 PERCENT dbo.matsupp_mst.material_no, dbo.matsupp_mst.supplier_c, dbo.supplier_mst.supplier_nm, dbo.matsupp_mst.po_price / dbo.suppord_dtl.exch_rate AS StdCost, dbo.matsupp_mst.order_ltime, dbo.matsupp_mst.trans_ltime, dbo.matsupp_mst.lot_size AS MOQ

FROM dbo.matsupp_mst INNER JOIN dbo.supplier_mst ON dbo.matsupp_mst.supplier_c = dbo.supplier_mst.supplier_c INNER JOIN dbo.suppord_dtl ON dbo.supplier_mst.supplier_c = dbo.suppord_dtl.supplier_c LEFT OUTER JOIN dbo.Backlog ON dbo.matsupp_mst.material_no = dbo.Backlog.material_no

WHERE (dbo.matsupp_mst.po_ratio <> 0)

ORDER BY dbo.matsupp_mst.supplier_c, dbo.matsupp_mst.material_no

View 3 -- Temp_R

SELECT DISTINCT TOP 100 PERCENT dbo.nMRP1.material_no, dbo.nMRP1.material_nm, dbo.nMRP1.invt_qty, dbo.nMRP1.safetyinvt_qty, dbo.nMRP2.supplier_c, dbo.nMRP2.supplier_nm, dbo.nMRP1.po_unit, dbo.nMRP1.unit_confac_po, dbo.nMRP1.rmks, dbo.nMRP2.StdCost, dbo.nMRP1.material_kd, dbo.nMRP2.order_ltime, dbo.nMRP2.trans_ltime, dbo.nMRP2.MOQ, dbo.Backlog.po_qty - dbo.Backlog.recv_qty AS Back_QTY

FROM dbo.nMRP2 LEFT OUTER JOIN dbo.Backlog ON dbo.nMRP2.material_no = dbo.Backlog.material_no RIGHT OUTER JOIN dbo.nMRP1 ON dbo.nMRP2.material_no = dbo.nMRP1.material_no

WHERE (dbo.nMRP2.order_ltime IS NOT NULL) AND (dbo.nMRP2.trans_ltime IS NOT NULL)

ORDER BY dbo.nMRP2.supplier_c, dbo.nMRP1.material_no

View 4 -- ExptResult1_21

SELECT DISTINCT TOP 100 PERCENT dbo.Temp_R.*

FROM dbo.Temp_R CROSS JOIN dbo.ExptResult1

WHERE (NOT (dbo.Temp_R.material_no IN
      (SELECT     exptresult1.materialno FROM exptresult1)))

ORDER BY dbo.Temp_R.supplier_c, dbo.Temp_R.material_no

当我run View 4的时候，就是出timeout expired. 我单独run 其他view的时候，result都很快出来的。

我不知道我哪里出了问题。。。
( 想很久都不通。。。像我们的淹水一样，水不会通 )

希望有人有时间的话，帮我看看我哪里出了问题。。。

max5007

有时间就帮你慢慢看。。不过尽量不要用DISTINCT

johe07

先谢谢。

为什么尽量不要用distinct?

神仙祖宗

原装query有什么问题？
因为水管转接处太多了，很容易造成水管堵塞，或回流状态 ，或在那儿玩呼啦圈

goatstudio

看到我头昏脑涨...
我想... 这样简化, 很难知道我们是否做对了... 有没有 sample data, 还有 expected data?

johe07

原帖由 <i>神仙祖宗</i> 于 21-12-2006 22:00 发表<br />
原装query有什么问题？<br />
因为水管转接处太多了，很容易造成水管堵塞，或回流状态 ，或在那儿玩呼啦圈<img src="images/smilies/titter.gif" smilieid="31" border="0" alt="" /> <img src="images/smilies/titter.gif" smilieid="31" border="0" alt="" />

<br />
之前的query，result出来的时候。。。material_no有重复的。。。所以变成invt_qty没有sum up 起来。
我在原本的query加入group by之后，它变成timeout expired...
所以，我要简化它所有的query...

我就是知道水管转接处太多了，所以要简化。。。
不然我不会求助了。。。

johe07

原帖由 <i>goatstudio</i> 于 22-12-2006 00:24 发表<br />
看到我头昏脑涨...<br />
我想... 这样简化, 很难知道我们是否做对了... 有没有 sample data, 还有 expected data?

<br />

抱歉，其实我也是study到头昏脑胀。。。。

我会放出我的table structure, 还有sample result.

johe07

这是我用原本的query出来的结果



[ 本帖最后由 johe07 于 22-12-2006 09:02 AM 编辑 ]

johe07

view 里面所有table的structure

1. material_mst


2. invt_mst


3. matsupp_mst


4. supplier_mst


5. suppord_dtl

max5007

原帖由 johe07 于 21-12-2006 12:09 PM 发表
先谢谢。

为什么尽量不要用distinct?

我写错了。。应该是
尽量不要用DISTINCT 当你GROUP BY时

johe07

sample data, 不知道为什么，不过我怎样调，都是那么小
我的sample data要出来的东西是

matsupp_mst.supplier_c as SUPP,
supplier_mst.supplier_nm as SUPP_NAME
material_mst.material_no as MATERIALNO
material_mst.material_nm as PARTNAME
matsupp_mst.order_ltime as ORDER_LT
matsupp_mst.trans_ltime as TRANS_LT
material_mst.po_unit as UNIT
invt_mst.invt_qty as INV_QTY
backlog.po_qty - backlog.recv_qty as        BACK_QTY
matsupp_mst.po_price / suppord_dtl.exch_rate as STD_COST
matsupp_mst.lot_size as MOQ
material_mst.material_kd as TYPE
material_mst.unit_confac_po as UNIT_CONFA
invt_mst.safetyinvt as SAFETYINVT
material_mst.rmks as REMARK

[ 本帖最后由 johe07 于 22-12-2006 11:07 PM 编辑 ]

神仙祖宗

原帖由 johe07 于 22-12-2006 09:14 AM 发表
sample data, 不知道为什么，不过我怎样调，都是那么小
我的sample data要出来的东西是

matsupp_mst.supplier_c as SUPP,
supplier_mst.supplier_nm as SUPP_NAME
material_mst.material_no as MATERIALN ...

你的suppord_dtl在哪儿？你的backlog table在哪儿？？

johe07

原帖由 神仙祖宗 于 22-12-2006 21:47 发表

你的suppord_dtl在哪儿？你的backlog table在哪儿？？

哎呀，写错了。。。我改了。。。。

backlog啊。。。忘了post上去。。。

flashang

TEMP_RESULT 沒有計算卻有 group by ?

TEMP_R 應該很接近最後的答案了, 還有那些需要增加 / 減少的 ?
       為何 ExptResult1_21 還要用 cross join ?

johe07

原帖由 flashang 于 22-12-2006 23:47 发表
TEMP_RESULT 沒有計算卻有 group by ?

TEMP_R 應該很接近最後的答案了, 還有那些需要增加 / 減少的 ?
       為何 ExptResult1_21 還要用 cross join ?

这个cross join 是view自动出来的。。。
因为之前有了一个ExptResult1,
然后我的ExptResult1_21是要在ExptResult1里面没有的data

johe07

我BackLog view里面有用到的另外3个table struture, 其他的，上面已经post了

material_recv_gi


po_hdr


po_dtl