Oracle问题 ,我都搞了三天了,帮个忙吧
表HY_HYJJDATA大约有200多万的记录,下面的语句在PL/SQL里执行很快,一/两秒,用c#调用OracleClient执行就慢的要死,要1分钟,哪位高手知道怎么解决,谢谢
WITH
AGG AS (SELECT sl,kssl,updatedate,areacode,industrycode,groupingcode
FROM HY_HYJJDATA
WHERE updatedate >= to_date('2005-11-8', 'yyyy-mm-dd')
AND updatedate <= to_date('2007-11-8', 'yyyy-mm-dd')
AND ( industrycode = '06' Or industrycode = '0610' Or industrycode = '0620' Or industrycode = '0690' )
AND ( areacode LIKE '00%' Or areacode LIKE '12%' Or areacode LIKE '11%')
AND groupingcode = '00'
)
SELECT *
FROM (SELECT t.tempdate,
T_0000.SL as f_00000,
T_0000.KSSL as f_10000,
T_0100.SL as f_00100,
T_0100.KSSL as f_10100,
T_0200.SL as f_00200,
T_0200.KSSL as f_10200,
T_1000.SL as f_01000,
T_1000.KSSL as f_11000,
T_1100.SL as f_01100,
T_1100.KSSL as f_11100,
T_1200.SL as f_01200,
T_1200.KSSL as f_11200,
T_2000.SL as f_02000,
T_2000.KSSL as f_12000,
T_2100.SL as f_02100,
T_2100.KSSL as f_12100,
T_2200.SL as f_02200,
T_2200.KSSL as f_12200,
T_3000.SL as f_03000,
T_3000.KSSL as f_13000,
T_3100.SL as f_03100,
T_3100.KSSL as f_13100,
T_3200.SL as f_03200,
T_3200.KSSL as f_13200
FROM (SELECT DISTINCT updatedate AS tempdate
FROM agg
ORDER BY updatedate DESC) t
LEFT JOIN (SELECT updatedate, SL, KSSL
FROM agg where areacode LIKE '00%'
AND groupingcode = '00'
AND industrycode = '06') T_0000 ON t.tempdate =
T_3200.updatedate)
WHERE rownum <= 100
问题补充:先不说设计思路
重点是,在PL/SQL里执行很快,用OracleClient连就慢
在adapter FILL时非常慢
都是客户端执行,和网络没关系