SQL优化

xuweifenglili

下面SQL执行很慢，帮我优化一下
SELECT top 10 PROJECTS_INFO.PROJECT_NO, PROJECTS_INFO.PROJECT_NAME, PROJECTS_INFO.P_DATE_START, PROJECTS_INFO.P_DATE_END,PROJECTS_INFO.CUSTOMER,
PROJECTS_INFO.MITUMORIIRAINO, PROJECTS_INFO.PROJECT_SALE, PROJECTS_INFO.PJTOUROKUHI, PROJECTS_INFO.OYAPJNO, PROJECTS_INFO.PROJECT_PAYOUT,
PROJECTS_INFO.PROJECT_TL, PROJECTS_INFO.SALE_UNIT,PROJECTS_INFO.PAYOUT_UNIT,PROJECTS_INFO.PROJECT_PL, TLS.TL_NAME, PLS.PL_NAME,
PROJECTS_INFO.STOPFLAG,PROJECTS_INFO.QATANNTOU ,QAS.QA_NAME , PROJECTS_INFO.MITUMORIKENTO,PROJECTS_INFO.BICO,
T_PJKIHONNKEIKAKU.KANNRYOUHOUKOKUHI, T_PJKIHONNKEIKAKU.SYOUNINNHI AS IMPLAN_SYOUNINNHI, T_PDSPKIHONNINF.SYOUNINNHI AS
PDSP_SYOUNINNHI, OYAPJ_INFO.OYAPJ_KANNRYOUHI, OYAPJ_STOP_INFO.OYAPJ_STOPFLAG,PROJECTS_INFO.EVALUATE_STATUS  
from  ((((((PROJECTS_INFO LEFT JOIN [SELECT PROJECTS_INFO.PROJECT_NO, EMPLOYEES_INFO.EMPLOYEE_NAME AS TL_NAME FROM PROJECTS_INFO,
EMPLOYEES_INFO WHERE PROJECTS_INFO.PROJECT_TL = EMPLOYEES_INFO.EMPLOYEE_NO ]. AS TLS ON PROJECTS_INFO.PROJECT_NO = TLS.PROJECT_NO) LEFT
JOIN [SELECT PROJECTS_INFO.PROJECT_NO, EMPLOYEES_INFO.EMPLOYEE_NAME AS PL_NAME FROM PROJECTS_INFO, EMPLOYEES_INFO WHERE
PROJECTS_INFO.PROJECT_PL = EMPLOYEES_INFO.EMPLOYEE_NO ]. AS PLS ON PROJECTS_INFO.PROJECT_NO = PLS.PROJECT_NO) LEFT JOIN
[SELECT PROJECTS_INFO.PROJECT_NO, EMPLOYEES_INFO.EMPLOYEE_NAME AS QA_NAME FROM PROJECTS_INFO, EMPLOYEES_INFO
WHERE PROJECTS_INFO.QATANNTOU = EMPLOYEES_INFO.EMPLOYEE_NO ].  AS QAS ON PROJECTS_INFO.PROJECT_NO = QAS.PROJECT_NO ) LEFT
JOIN T_PJKIHONNKEIKAKU ON PROJECTS_INFO.PROJECT_NO = T_PJKIHONNKEIKAKU.PJNO) LEFT JOIN [SELECT PROJECTS_INFO.PROJECT_NO ,
T_PJKIHONNKEIKAKU.KANNRYOUHOUKOKUHI AS OYAPJ_KANNRYOUHI FROM PROJECTS_INFO, T_PJKIHONNKEIKAKU
WHERE PROJECTS_INFO.OYAPJNO = T_PJKIHONNKEIKAKU.PJNO ]. AS OYAPJ_INFO ON PROJECTS_INFO.PROJECT_NO = OYAPJ_INFO.PROJECT_NO )
LEFT JOIN [SELECT PROJECTS_INFO.PROJECT_NO , PROJECTS_INFO.STOPFLAG AS OYAPJ_STOPFLAG FROM PROJECTS_INFO]. AS OYAPJ_STOP_INFO
ON PROJECTS_INFO.OYAPJNO = OYAPJ_STOP_INFO.PROJECT_NO)  LEFT JOIN T_PDSPKIHONNINF ON PROJECTS_INFO.PROJECT_NO = T_PDSPKIHONNINF.PJNO  
where (PROJECTS_INFO.DELETEF =False)   AND ( PROJECTS_INFO.PROJECT_NO  not in  (select top  280 PROJECTS_INFO.PROJECT_NO  
from  ((((((PROJECTS_INFO LEFT JOIN [SELECT PROJECTS_INFO.PROJECT_NO, EMPLOYEES_INFO.EMPLOYEE_NAME AS TL_NAME FROM PROJECTS_INFO,
EMPLOYEES_INFO WHERE PROJECTS_INFO.PROJECT_TL = EMPLOYEES_INFO.EMPLOYEE_NO ]. AS TLS ON PROJECTS_INFO.PROJECT_NO = TLS.PROJECT_NO) LEFT
JOIN [SELECT PROJECTS_INFO.PROJECT_NO, EMPLOYEES_INFO.EMPLOYEE_NAME AS PL_NAME FROM PROJECTS_INFO, EMPLOYEES_INFO
WHERE PROJECTS_INFO.PROJECT_PL = EMPLOYEES_INFO.EMPLOYEE_NO ]. AS PLS ON PROJECTS_INFO.PROJECT_NO = PLS.PROJECT_NO) LEFT
JOIN [SELECT PROJECTS_INFO.PROJECT_NO, EMPLOYEES_INFO.EMPLOYEE_NAME AS QA_NAME FROM PROJECTS_INFO, EMPLOYEES_INFO
WHERE PROJECTS_INFO.QATANNTOU = EMPLOYEES_INFO.EMPLOYEE_NO ].  AS QAS ON PROJECTS_INFO.PROJECT_NO = QAS.PROJECT_NO ) LEFT
JOIN T_PJKIHONNKEIKAKU ON PROJECTS_INFO.PROJECT_NO = T_PJKIHONNKEIKAKU.PJNO) LEFT JOIN [SELECT PROJECTS_INFO.PROJECT_NO ,
T_PJKIHONNKEIKAKU.KANNRYOUHOUKOKUHI AS OYAPJ_KANNRYOUHI FROM PROJECTS_INFO, T_PJKIHONNKEIKAKU WHERE PROJECTS_INFO.OYAPJNO =
T_PJKIHONNKEIKAKU.PJNO ]. AS OYAPJ_INFO ON PROJECTS_INFO.PROJECT_NO = OYAPJ_INFO.PROJECT_NO ) LEFT JOIN [SELECT PROJECTS_INFO.PROJECT_NO ,
PROJECTS_INFO.STOPFLAG AS OYAPJ_STOPFLAG FROM PROJECTS_INFO]. AS OYAPJ_STOP_INFO ON PROJECTS_INFO.OYAPJNO = OYAPJ_STOP_INFO.PROJECT_NO)  
LEFT JOIN T_PDSPKIHONNINF ON PROJECTS_INFO.PROJECT_NO = T_PDSPKIHONNINF.PJNO  
where (PROJECTS_INFO.DELETEF =False)   ORDER BY   PROJECTS_INFO.PROJECT_NO DESC  ) )
  ORDER BY   PROJECTS_INFO.PROJECT_NO DESC

jinguanding

晕..这么长的SQL语句...你写的MSSQL方式吧..都有top 10  

要是相关表中有容量很大的话,最好采用temporary方式实现了..


若真是MSSQL可以考虑使用这样方式
declare @table table (IDH int not null identity(1,1) ........)
但是一定要在一个会话中,否则就失去效果了..同时建议你把内存先调大点..