同样的SQL语句在Oracle客户端中执行用时0.5s，在TSQLDataSet中执行耗时3分钟

李红波

如题，
有什么解决方法吗？

李红波

具体SQL如下：
select M.SJDWBH AS SJDWBH,M.KB AS KB,M.CYDS AS CYDS,
       M.JQ_QSFMT as JQ_QSFMT,
       M.JQ_KQGZJSFMAD AS JQ_KQGZJSFMAD,
       M.JQ_KQGZJHFAAD AS JQ_KQGZJHFAAD,
       M.JQ_KQGZJHFFVAD AS JQ_KQGZJHFFVAD,
       M.JQ_KQGZJQLST_AD AS JQ_KQGZJQLST_AD,
       M.JQ_DTFRLQB_AD AS JQ_DTFRLQB_AD,
       M.JQ_MAR AS JQ_MAR,
       M.JQ_QHL AS JQ_QHL,
       M.JQ_SDJHFAAR AS JQ_SDJHFAAR,
       M.JQ_SDJHFFVAR AS JQ_SDJHFFVAR,
       M.JQ_GZWHJHFFVDAF AS JQ_GZWHJHFFVDAF,
       M.JQ_GZJQLST_D AS JQ_GZJQLST_D,
       M.JQ_DWFRLQNET_AR AS JQ_DWFRLQNET_AR,
       M.JQ_FRL_K AS JQ_FRL_K
from
(SELECT '-1' as SJDWBH,GYSXX.SJDWBH AS KB,sum(T.CYDS) as CYDS,  
       decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_Mar when ((select js from hyd where cydid=h.cydid)='02')  then (select QSFMT from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else QSFMT end) * T.CYDS)/sum(T.CYDS),2)) as JQ_QSFMT,
       decode(sum(T.CYDS),0,0, round(sum(H.KQGZJSFMAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJSFMAD,
       decode(sum(T.CYDS),0,0, round(sum(H.KQGZJHFAAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJHFAAD,
       decode(sum(T.CYDS),0,0, round(sum(H.KQGZJHFFVAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJHFFVAD,
       decode(sum(T.CYDS),0,0, round(sum(H.KQGZJQLST_AD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJQLST_AD,
       decode(sum(T.CYDS),0,0, round(sum(H.DTFRLQB_AD * T.CYDS)/sum(T.CYDS),2)) AS JQ_DTFRLQB_AD,
       decode(sum(T.CYDS),0,0, round(sum(H.MAR * T.CYDS)/sum(T.CYDS),2)) AS JQ_MAR,
       decode(sum(T.CYDS),0,0, round(sum(H.QHL * T.CYDS)/sum(T.CYDS),2)) AS JQ_QHL,
       decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_SDJHFAar when ((select js from hyd where cydid=h.cydid)='02')  then (select SDJHFAar from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else SDJHFAar end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_SDJHFAAR,
       decode(sum(T.CYDS),0,0, round(sum(H.SDJHFFVAR * T.CYDS)/sum(T.CYDS),2)) AS JQ_SDJHFFVAR,
       decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_GZWHJHFFVdaf when ((select js from hyd where cydid=h.cydid)='02')  then (select GZWHJHFFVdaf from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else GZWHJHFFVdaf end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_GZWHJHFFVDAF,
       decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_GZJQLSt_d when ((select js from hyd where cydid=h.cydid)='02')  then (select GZJQLSt_d from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else GZJQLSt_d end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_GZJQLST_D,
       decode(sum(T.CYDS),0,0, Trunc(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_DWFRLQnet_ar when ((select js from hyd where cydid=h.cydid)='02')  then (select DWFRLQnet_ar from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else DWFRLQnet_ar end) * T.CYDS)/sum(T.CYDS),0)) AS JQ_DWFRLQNET_AR,
       decode(sum(T.CYDS),0,0, Trunc(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_FRL_K when ((select js from hyd where cydid=h.cydid)='02')  then (select FRL_K from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else FRL_K end) * T.CYDS)/sum(T.CYDS),0)) AS JQ_FRL_K
FROM HYD H, GYSXX,
               (SELECT cydid,count(ID) AS CYCS,SUM(case when ((select jszl from hyd where cydid=qclm.cydid)='02') then jz_1 else pz end) AS CYDS,SUM(PZ) AS PiaoZ FROM QCLM WHERE TO_CHAR(QCLM.RCSJ,'YYYY-MM') = '2011-01' GROUP BY CYDID
               UNION
               SELECT cydid,count(ID) AS CYCS,SUM(case when ((select jszl from hyd where cydid=TRAINCOMECOAL.cydid)='02') then jz else piaoz end) AS CYDS,SUM(PiaoZ) AS PiaoZ FROM TRAINCOMECOAL WHERE TO_CHAR(TRAINCOMECOAL.RCSJ,'YYYY-MM') = '2011-01' GROUP BY CYDID
               ) T
WHERE H.CYDID=T.CYDID AND GYSXX.GYSBH = H.KB
Group  BY GYSXX.SJDWBH
union
SELECT GYSXX.SJDWBH as SJDWBH,H.KB as kb,sum(T.CYDS) as CYDS,  
       decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_Mar when ((select js from hyd where cydid=h.cydid)='02')  then (select QSFMT from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else QSFMT end) * T.CYDS)/sum(T.CYDS),2)) as JQ_QSFMT,
       decode(sum(T.CYDS),0,0, round(sum(h.KQGZJSFMAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJSFMAD,
       decode(sum(T.CYDS),0,0, round(sum(h.KQGZJHFAAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJHFAAD,
       decode(sum(T.CYDS),0,0, round(sum(h.KQGZJHFFVAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJHFFVAD,
       decode(sum(T.CYDS),0,0, round(sum(h.KQGZJQLST_AD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJQLST_AD,
       decode(sum(T.CYDS),0,0, round(sum(h.DTFRLQB_AD * T.CYDS)/sum(T.CYDS),2)) AS JQ_DTFRLQB_AD,
       decode(sum(T.CYDS),0,0, round(sum(h.MAR * T.CYDS)/sum(T.CYDS),2)) AS JQ_MAR,
       decode(sum(T.CYDS),0,0, round(sum(h.QHL * T.CYDS)/sum(T.CYDS),2)) AS JQ_QHL,
       decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_SDJHFAar when ((select js from hyd where cydid=h.cydid)='02')  then (select SDJHFAar from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else SDJHFAar end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_SDJHFAAR,
       decode(sum(T.CYDS),0,0, round(sum(h.SDJHFFVAR * T.CYDS)/sum(T.CYDS),2)) AS JQ_SDJHFFVAR,
       decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_GZWHJHFFVdaf when ((select js from hyd where cydid=h.cydid)='02')  then (select GZWHJHFFVdaf from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else GZWHJHFFVdaf end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_GZWHJHFFVDAF,
       decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_GZJQLSt_d when ((select js from hyd where cydid=h.cydid)='02')  then (select GZJQLSt_d from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else GZJQLSt_d end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_GZJQLST_D,
       decode(sum(T.CYDS),0,0, Trunc(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_DWFRLQnet_ar when ((select js from hyd where cydid=h.cydid)='02')  then (select DWFRLQnet_ar from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else DWFRLQnet_ar end) * T.CYDS)/sum(T.CYDS),0)) AS JQ_DWFRLQNET_AR,
       decode(sum(T.CYDS),0,0, Trunc(sum((case when ((select js from hyd where cydid=h.cydid)='03')  then KF_FRL_K when ((select js from hyd where cydid=h.cydid)='02')  then (select FRL_K from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else FRL_K end) * T.CYDS)/sum(T.CYDS),0)) AS JQ_FRL_K
FROM HYD h, GYSXX,
               (SELECT cydid,count(ID) AS CYCS,SUM(case when ((select jszl from hyd where cydid=qclm.cydid)='02') then jz_1 else pz end) AS CYDS,SUM(PZ) AS PiaoZ FROM QCLM WHERE TO_CHAR(QCLM.RCSJ,'YYYY-MM') = '2011-01' GROUP BY CYDID
               UNION
               SELECT cydid,count(ID) AS CYCS,SUM(case when ((select jszl from hyd where cydid=TRAINCOMECOAL.cydid)='02') then jz else piaoz end) AS CYDS,SUM(PiaoZ) AS PiaoZ FROM TRAINCOMECOAL WHERE TO_CHAR(TRAINCOMECOAL.RCSJ,'YYYY-MM') ='2011-01' GROUP BY CYDID
               ) T
WHERE h.CYDID=T.CYDID AND GYSXX.GYSBH = h.KB
Group  BY GYSXX.SJDWBH,h.KB
) M order by kb

李红波

在建模工具中运行时，CPU占用率达到50%以上，CPU的两个核中，一个占用大概90以上，一个占用不到10%

anla

楼主在oracle里写个存储过程吧。

zfang

是数据集执行的吗？请检查一下数据集上是否定义了查找属性（查找数据量大不大，定义的查找属性多不多）
试试用TQuery执行看看。