日期:2014-05-18  浏览次数:20478 次

请教高手如何优化sql语句
sql语句有点长,当数据很多的时候查询很影响速度,请问这个语句可以怎么优化呢?

SELECT T_ITEM.F_DESC, T_ITEM.F_UNIT, SMP_APDTL.F_ID, SMP_APDTL.F_ITEMID, SMP_APDTL.F_DOCQTY, SMP_APDTL.F_TRANSID, 
CONVERT(numeric(18,4), SMP_APDTL.F_NEWUNITAMT) AS F_NEWUNITAMT, CONVERT(numeric(18, 6), 
SMP_APDTL.F_AMT / SMP_APDTL.F_DOCQTY) AS F_UNITAMT, CONVERT(numeric(18, 2), SMP_APDTL.F_AMT) AS F_AMT, 
CONVERT(numeric(18, 2), SMP_APDTL.F_AMT / (1+T_SALESORDHDR.F_TAXRATE) * T_SALESORDHDR.F_TAXRATE) AS F_TAXAMT, 
CONVERT(numeric(18, 2), SMP_APDTL.F_UNITAMT) AS F_FUNITAMT, CONVERT(numeric(18, 2), 
SMP_APDTL.F_UNITAMT * SMP_APDTL.F_DOCQTY) AS F_FAMT, CONVERT(numeric(18, 2), SMP_APDTL.F_AMT) 
- CONVERT(numeric(18, 2), SMP_APDTL.F_UNITAMT * SMP_APDTL.F_DOCQTY) AS F_SUBAMT, 
T_ITEMTRANSHDR.F_TRANSDATE, T_SALESORDDTL.F_ITEMCUSTDESC, T_SALESORDHDR.F_CUSTORDID,  
T_ITEMADJTYPE.F_DESC AS ADJDESC,T_ITEMADJTYPE.F_TRANSTYPE, T_ARDTL.F_ARID,  
SMP_PMPC_PMPCAP_Resource.F_Target_AP_CreateResult as F_APResult,
SMP_PMPC_BPAR_Resource.F_Source_AR_CreateResult as F_ARResult
 FROM T_SALESORDHDR 
INNER JOIN T_SALESORDDTL ON T_SALESORDHDR.F_ID = T_SALESORDDTL.F_SALESID 
INNER JOIN T_ITEM INNER JOIN SMP_APDTL ON T_ITEM.F_ID = SMP_APDTL.F_ITEMID 
INNER JOIN T_ITEMTRANSHDR ON SMP_APDTL.F_TRANSID = T_ITEMTRANSHDR.F_ID 
INNER JOIN T_ITEMTRANSDTL ON SMP_APDTL.F_TRANSDTLID = T_ITEMTRANSDTL.F_ID 
INNER JOIN T_ITEMADJTYPE ON T_ITEMTRANSDTL.F_ADJTYPE = T_ITEMADJTYPE.F_ID
ON T_SALESORDDTL.F_ITEMID = T_ITEMTRANSDTL.F_ITEMID AND T_SALESORDDTL.F_SALESID = T_ITEMTRANSDTL.F_SALESID 
INNER JOIN T_ITEMTRANSTYPE ON T_ITEMTRANSHDR.F_TRANSTYPE = T_ITEMTRANSTYPE.F_ID 
LEFT OUTER JOIN 
 (
T_ARDTL INNER JOIN T_ARHDR ON T_ARHDR.F_ID=T_ARDTL.F_ARID INNER JOIN T_INVOTYPE ON T_INVOTYPE.F_ID=T_ARHDR.F_INVOTYPE
AND T_INVOTYPE.F_ID = T_INVOTYPE.f_hedgeid) ON SMP_APDTL.F_ID = T_ARDTL.F_SMPID  
left join SMP_PMPC_PMPCAP_Resource on SMP_PMPC_PMPCAP_Resource.F_BillDtlID = SMP_APDTL.F_ID 
left join SMP_PMPC_BPAR_Resource on SMP_PMPC_BPAR_Resource.F_BillDtlID = SMP_APDTL.F_ID 
WHERE (SMP_APDTL.F_APID = 3116) AND (T_ITEMTRANSTYPE.F_ISINNERBOARD = 0) 
UNION ALL  
SELECT T_ITEM.F_DESC, T_ITEM.F_UNIT, SMP_APDTL.F_ID, SMP_APDTL.F_ITEMID, SMP_APDTL.F_DOCQTY, SMP_APDTL.F_TRANSID, 
CONVERT(numeric(18,4), SMP_APDTL.F_NEWUNITAMT) AS F_NEWUNITAMT,  
CONVERT(numeric(18, 6), SMP_APDTL.F_AMT / SMP_APDTL.F_DOCQTY) AS F_UNITAMT, CONVERT(numeric(18, 2), SMP_APDTL.F_AMT) AS F_AMT,
CONVERT(numeric(18, 2), SMP_APDTL.F_AMT / 1.17 * 0.17) AS F_TAXAMT, CONVERT(numeric(18, 2), SMP_APDTL.F_UNITAMT) AS F_FUNITAMT, 
CONVERT(numeric(18, 2), SMP_APDTL.F_UNITAMT * SMP_APDTL.F_DOCQTY) AS F_FAMT, 
CONVERT(numeric(18, 2), SMP_APDTL.F_AMT) - CONVERT(numeric(18, 2), SMP_APDTL.F_UNITAMT * SMP_APDTL.F_DOCQTY) AS F_SUBAMT, 
T_ITEMTRANSHDR.F_TRANSDATE, T_ITEM.F_DESC AS F_ITEMCUSTDESC, '' AS F_CUSTORDID, T_ITEMADJTYPE.F_DESC AS ADJDESC,
T_ITEMADJTYPE.F_TRANSTYPE, T_ARDTL.F_ARID , SMP_PMPC_PMPCAP_Resource.F_Target_AP_CreateResult as F_APResult,
SMP_PMPC_BPAR_Resource.F_Source_AR_CreateResult as F_ARResult FROM T_ITEMTRANSTYPE 
INNER JOIN T_ITEM INNER JOIN SMP_APDTL ON T_ITEM.F_ID = SMP_APDTL.F_ITEMID 
INNER JOIN T_ITEMTRANSHDR ON SMP_APDTL.F_TRANSID = T_ITEMTRANSHDR.F_ID 
INNER JOIN T_ITEMTRANSDTL ON SMP_APDTL.F_TRANSDTLID = T_ITEMTRANSDTL.F_ID 
INNER JOIN T_ITEMADJTYPE ON T_ITEMTRANSDTL.F_ADJTYPE = T_ITEMADJTYPE.F_ID ON  
T_ITEMTRANSTYPE.F_ID = T_ITEMTRANSHDR.F_TRANSTYPE LEFT OUTER JOIN (T_ARDTL 
INNER JOIN T_ARHDR ON T_ARHDR.F_ID=T_ARDTL.F_ARID INNER JOIN T_INVOTYPE ON T_INVOTYPE.F_ID=T_ARHDR.F_INVOTYPE