sql server語句優化問題(20分)

[red]SELECT lc_ctr_id,b.div_id,b.dep_id,b.sle_id,b.ic_id," _
+ " 1 AS time_id , " _
+ " (CASE WHEN CONVERT(CHAR(5),a.sl_yea_id * 10 + a.sl_sea_id) " _
+ " IN " + L_PSeaWhere _
+ " THEN 1 ELSE 2 END ) AS sea_id," _
+ " SUM (sl_itmamt * (100 - sl_invdisc ) / 100 ) AS itmamt " _
+ " INTO #tempalldata " _
+ " FROM v_sale_item a,v_location,v_itmast_sle b " _
+ " WHERE sl_inv_date BETWEEN '" & Format(DT_YDate(0), G_SQLDateType) & "' AND '" & Format(DT_YDate(1), G_SQLDateType) & "' " _
+ " AND sl_loc_id = lc_loc_id AND a.sl_itmseq = b.itmseq " _
+ L_Where _
+ " GROUP BY lc_ctr_id,b.div_id,b.dep_id,b.sle_id,b.ic_id, " _
+ " (CASE WHEN CONVERT(CHAR(5),a.sl_yea_id * 10 + a.sl_sea_id) " _
+ " IN " + L_PSeaWhere _
+ " THEN 1 ELSE 2 END )"
[/red]現有問題請教各位﹐這條語句中(v_sale_item a表中有一千萬條記錄﹑v_location表中
有403條記錄﹑v_itmast_sle b表中有7213條記錄)運行起來相當慢﹐請問有無解決好辦法﹐
謝謝﹗

首先你要使用好的索引，你可以用查询分析器看一下，语句中是否用到合理的索引，尽量
减少表扫描。
然后最好不要把CASE语句和SUM放在一起，你可以用临时表把CASE的中间结果放在其中，再
用SUM语句进行汇总，这样也会加快速度。

獨角戲﹐你好﹗
(CASE WHEN CONVERT(CHAR(5),a.sl_yea_id * 10 + a.sl_sea_id) IN L_PSeaWhere
THEN 1 ELSE 2 END ) AS sea_id,
SUM (sl_itmamt * (100 - sl_invdisc ) / 100 ) AS itmamt
以上的語句中﹐(case when﹑﹑﹑)是相對 sea_id 而言﹐
sum是相對 itmamt 而言,
至于你說的"可以用临时表把CASE的中间结果放在其中，再用SUM语句进行汇总"
但他們不是在嵌套中計算﹐能否麻煩你舉個例說說。
謝謝﹗

各位好﹗
請問:1 group by語句和order by語句一起用會不會影響程序的速度?
2 怎樣使用位圖?
謝謝﹗

>>>1 group by語句和order by語句一起用會不會影響程序的速度?
分组再排序，肯定会影响的

請問jrq:
你對這種情況有沒有什么好的建議﹐舉個例說說好嗎?
謝謝﹗

獨角戲﹑jrq,你門好﹗
由于我們公司現在正在處理大量自動化報表﹐需要從多方面考慮sql server的優化
問題﹐我從本論壇了解了許多相關的知識﹐但仍然很缺乏﹐現想聽聽你們的獨家見解﹐你們
是怎樣從一個整體過程來考慮sql的優化的呢?
謝謝﹗(你們不會介意慢點拿分吧)

你可以用视图:
CREATE VIEW a
SELECT lc_ctr_id,b.div_id,b.dep_id,b.sle_id,b.ic_id," _
+ " 1 AS time_id , " _
+ " (CASE WHEN CONVERT(CHAR(5),a.sl_yea_id * 10 + a.sl_sea_id) " _
+ " IN " + L_PSeaWhere _
+ " THEN 1 ELSE 2 END ) AS sea_id," _
+ " sl_itmamt, sl_invdisc " _
+ " FROM v_sale_item a,v_location,v_itmast_sle b " _
+ " WHERE sl_inv_date BETWEEN '" & Format(DT_YDate(0), G_SQLDateType) & "' AND '" & Format(DT_YDate(1), G_SQLDateType) & "' " _
+ " AND sl_loc_id = lc_loc_id AND a.sl_itmseq = b.itmseq " _
+ L_Where _
go
然后用视图再进行sum运算。
你对sl_inv_date有没有进行索引。
要注意用索引。
你有没有用查询分析器看过，语句中是否用过索引，有没有表扫描。

獨角戲﹐你好
v_sale_item a,v_location,v_itmast_sle b 都采用了視圖﹐
sl_inv_date也有进行索引﹐我用查询分析器看过﹐沒有表掃描﹐語句中用過
索引﹐
在采用視圖﹑查询分析器﹑索引這類問題上我都考慮過了﹐可能在整體過程的
算法上有問題﹐可否聽聽您寶貴的編碼經驗。
謝謝﹗

你三个表的数据结构告诉我,目的是统计什么,这样我才能够写出来,你光这样的select语
句是没什么用的.

獨角戲
三個視圖數據結構﹕
[red]v_sale_item a:[/red]
CREATE VIEW [DBO].V_SALE_ITEM AS
SELECT Salegen.Loc_id AS Sl_loc_id,
Salegen.inv_date AS Sl_inv_date,
Salegen.inv_time AS Sl_inv_time,
Salegen.inv_no AS Sl_inv_no,
Salegen.sal_id AS Sl_sal_id,
Salegen.invamt AS Sl_invamt,
Salegen.otrchg AS Sl_otrchg,
Salegen.invdisc AS Sl_invdisc,
Salegen.Remark AS Sl_remark,
Salegen.Sex As Sl_Sex,
Salegen.Age As Sl_Age,
V_Saledet.Sl_itmseq,
V_Saledet.Sl_colszseq,
V_Saledet.Sl_log_id,
V_Saledet.Sl_salseq,
V_Saledet.Sl_salqty,
V_Saledet.Sl_currpri,
V_Saledet.Sl_itmdisc,
V_Saledet.Sl_itmamt,
V_Saledet.Sl_totcost4,
Sl_item,
Sl_bnd_id,
SL_dep_id,
Sl_style,
Sl_div_id,
Sl_yea_id,
Sl_sea_id,
Sl_sea_id2,
Sl_origpri
FROM Salegen,V_Saledet
WHERE Salegen.Loc_id = V_Saledet.Sl_Loc_id AND
Salegen.inv_date = V_Saledet.Sl_inv_date AND
Salegen.inv_no = V_Saledet.Sl_inv_no
v_location:
CREATE VIEW [DBO].V_LOCATION AS
SELECT LOCATION.CMP_ID AS lc_cmp_id,
SUBSTRING(LTRIM(COMPANY.NAME30),1,10) AS lc_cmpname,
LOCATION.CTR_ID AS lc_ctr_id,
MGTCTR.CTRSEQ AS lc_ctrseq,
MGTCTR.NAME10 AS lc_portname,
LOCATION.LGP_ID AS LC_lgp_id,
LOCGRP.NAME10 AS lc_cityname,
LOCATION.LOC_ID AS lc_loc_id,

SUBSTRING(LTRIM(locatdet.NAME20),1,10) AS lc_shopname,
LOCATION.LTY_ID AS lc_lty_id,
LOCTYPE.NAME10 AS lc_typename,
LOCATION.SELLAREA AS lc_sellarea,
LOCATION.ACT_IND AS lc_active,
CONVERT(CHAR(10),LOCATDET.opendate,103) AS lc_opendate
FROM locatdet,LOCATION,LOCGRP,MGTCTR,LOCTYPE,COMPANY
WHERE LOCATION.CTR_ID = MGTCTR.CTR_ID AND
LOCATION.CMP_ID = MGTCTR.CMP_ID AND
LOCATION.CTR_ID = LOCGRP.CTR_ID AND
LOCATION.CMP_ID = LOCGRP.CMP_ID AND
LOCATION.LGP_ID = LOCGRP.LGP_ID AND
LOCATION.LTY_ID = LOCTYPE.LTY_ID AND
LOCATION.CMP_ID = COMPANY.CMP_ID AND
locatdet.loc_id = location.loc_id

v_itmast_sle b :
Create view dbo.v_itmast_sle As
select itmseq, bnd_id, div_id, yea_id, sea_id, dep_id,
case when left(cat_id,1)='B' OR left(cat_id,1)='C' then 'B'
else 'A' end sle_id,cat_id, was_id, fab_id, sea_id2, sty_id, name30, origpri,
RIGHT(CONVERT(char(4), yea_id), 1) + CONVERT(char(1), sea_id) + '-' + CONVERT(char(4), sty_id) As it_itemID,ic_id
from itmast where
dep_id in ('3','7')
and sea_id2=1
union all
select itmseq, bnd_id, div_id, yea_id, sea_id, dep_id,
case when (left(cat_id,1)='A' OR left(cat_id,1)='D') then 'A'
else 'B' end sle_id,cat_id, was_id, fab_id, sea_id2, sty_id, name30, origpri,
RIGHT(CONVERT(char(4), yea_id), 1) + CONVERT(char(1), sea_id) + '-' + CONVERT(char(4), sty_id) As it_itemID,ic_id
from itmast where
dep_id in ('3','7')
and sea_id2=2
union all
select itmseq, bnd_id, div_id, yea_id, sea_id, dep_id,
'Z' As sle_id,cat_id, was_id, fab_id, sea_id2, sty_id, name30, origpri,
RIGHT(CONVERT(char(4), yea_id), 1) + CONVERT(char(1), sea_id) + '-' + CONVERT(char(4), sty_id) As it_itemID,ic_id
from itmast where
(dep_id in ('3','7') and sea_id2 not in (1,2))
or (dep_id not in ('3','7'))

多人接受答案了。