SQL语句直接执行快，在程序中慢

Question

有没有人知道一个SQL语句直接在pl/sql执行很快
但是用PreparedStatement.executeQuery 执行就很慢
会是什么原因呢

Answer 1

我调试代码 发现PreparedStatement.executeQuery 这个地方要卡很久

Answer 2

把java代码贴出来吧，:-) 这样别人怎看呢

Answer 3

SELECT A.EVA_KPI_ID,
       A.EVA_KPI_NAME,
       A.EVA_KPI_WEIGHT,
       ROWID1,
       SUM(NVL(A.F1, 0)) F1,
       SUM(NVL(A.F2, 0)) F2,
       SUM(NVL(A.F3, 0)) F3,
       SUM(NVL(A.F4, 0)) F4,
       SUM(NVL(A.F5, 0)) F5,
       SUM(NVL(A.F6, 0)) F6,
       SUM(NVL(A.F7, 0)) F7,
       SUM(NVL(A.F8, 0)) F8,
       SUM(NVL(A.F9, 0)) F9,
       SUM(NVL(A.F10, 0)) F10,
       SUM(NVL(A.F11, 0)) F11,
       SUM(NVL(A.F12, 0)) F12,
       SUM(NVL(A.F13, 0)) F13,
       SUM(NVL(A.F14, 0)) F14,
       SUM(NVL(A.F15, 0)) F15,
       SUM(NVL(A.F16, 0)) F16,
       SUM(NVL(A.F17, 0)) F17,
       SUM(NVL(A.F18, 0)) F18,
       SUM(NVL(A.F19, 0)) F19,
       SUM(NVL(A.F20, 0)) F20,
       SUM(NVL(A.F21, 0)) F21,
       SUM(NVL(A.F22, 0)) F22,
       SUM(NVL(A.F23, 0)) F23,
       SUM(NVL(A.F24, 0)) F24,
       SUM(NVL(A.F25, 0)) F25,
       SUM(NVL(A.F26, 0)) F26,
       SUM(NVL(A.F27, 0)) F27,
       SUM(NVL(A.F28, 0)) F28,
       SUM(NVL(A.F29, 0)) F29,
       SUM(NVL(A.F30, 0)) F30,
       SUM(NVL(A.F31, 0)) F31,
       MAX(A.COU) ISEND
  FROM (SELECT T.REGION_ID,
               T.EVA_KPI_ID,
               T.EVA_KPI_NAME,
               T.EVA_KPI_WEIGHT,
               MAX(T.ROWID1) ROWID1,
               MAX(T.COU) COU,
               DECODE(T.REGION_ID, 10, ROUND(AVG(T.SCORE), 2)) F1,
               DECODE(T.REGION_ID, 20, ROUND(AVG(T.SCORE), 2)) F2,
               DECODE(T.REGION_ID, 21, ROUND(AVG(T.SCORE), 2)) F3,
               DECODE(T.REGION_ID, 22, ROUND(AVG(T.SCORE), 2)) F4,
               DECODE(T.REGION_ID, 23, ROUND(AVG(T.SCORE), 2)) F5,
               DECODE(T.REGION_ID, 24, ROUND(AVG(T.SCORE), 2)) F6,
               DECODE(T.REGION_ID, 25, ROUND(AVG(T.SCORE), 2)) F7,
               DECODE(T.REGION_ID, 27, ROUND(AVG(T.SCORE), 2)) F8,
               DECODE(T.REGION_ID, 28, ROUND(AVG(T.SCORE), 2)) F9,
               DECODE(T.REGION_ID, 29, ROUND(AVG(T.SCORE), 2)) F10,
               DECODE(T.REGION_ID, 311, ROUND(AVG(T.SCORE), 2)) F11,
               DECODE(T.REGION_ID, 351, ROUND(AVG(T.SCORE), 2)) F12,
               DECODE(T.REGION_ID, 371, ROUND(AVG(T.SCORE), 2)) F13,
               DECODE(T.REGION_ID, 431, ROUND(AVG(T.SCORE), 2)) F14,
               DECODE(T.REGION_ID, 451, ROUND(AVG(T.SCORE), 2)) F15,
               DECODE(T.REGION_ID, 471, ROUND(AVG(T.SCORE), 2)) F16,
               DECODE(T.REGION_ID, 531, ROUND(AVG(T.SCORE), 2)) F17,
               DECODE(T.REGION_ID, 551, ROUND(AVG(T.SCORE), 2)) F18,
               DECODE(T.REGION_ID, 571, ROUND(AVG(T.SCORE), 2)) F19,
               DECODE(T.REGION_ID, 591, ROUND(AVG(T.SCORE), 2)) F20,
               DECODE(T.REGION_ID, 731, ROUND(AVG(T.SCORE), 2)) F21,
               DECODE(T.REGION_ID, 771, ROUND(AVG(T.SCORE), 2)) F22,
               DECODE(T.REGION_ID, 791, ROUND(AVG(T.SCORE), 2)) F23,
               DECODE(T.REGION_ID, 851, ROUND(AVG(T.SCORE), 2)) F24,
               DECODE(T.REGION_ID, 871, ROUND(AVG(T.SCORE), 2)) F25,
               DECODE(T.REGION_ID, 891, ROUND(AVG(T.SCORE), 2)) F26,
               DECODE(T.REGION_ID, 898, ROUND(AVG(T.SCORE), 2)) F27,
               DECODE(T.REGION_ID, 931, ROUND(AVG(T.SCORE), 2)) F28,
               DECODE(T.REGION_ID, 951, ROUND(AVG(T.SCORE), 2)) F29,
               DECODE(T.REGION_ID, 971, ROUND(AVG(T.SCORE), 2)) F30,
               DECODE(T.REGION_ID, 991, ROUND(AVG(T.SCORE), 2)) F31,
               ROUND(AVG(T.SCORE), 2) SCORE
          FROM (SELECT T.REGION_NAME,
                       T.REGION_ID,
                       T.EVA_KPI_ID,
                       T.EVA_KPI_NAME,
                       T.EVA_KPI_WEIGHT,
                       T.COU,
                       T.ROWID1,
                       PKP_EVALUATION.GETKPIVALUE(T.REGION_ID,
                                                  T.EVA_KPI_ID,
                                                  ?,
                                                  ?) SCORE
                  FROM (SELECT *
                          FROM (SELECT *
                                  FROM MANAGE_REGION
                                 WHERE REGION_ID NOT IN (-1, 0, 1, 100)) A,
                               (SELECT A.*,
                                       ROWNUM ROWID1,
                                       (SELECT COUNT(*)
                                          FROM EVALUSTION_KPI_CONFIG
                                         WHERE PARENT_EVA_KPI_ID = A.EVA_KPI_ID) COU
                                  FROM EVALUSTION_KPI_CONFIG A
                                 WHERE A.STATE = '0SA'
                                CONNECT BY PRIOR
                                            A.EVA_KPI_ID = A.PARENT_EVA_KPI_ID
                                 START WITH A.EVA_KPI_ID IN (1, 2, 3)) B) T) T
         GROUP BY T.REGION_ID,
                  T.EVA_KPI_ID,
                  T.EVA_KPI_NAME,
                  T.EVA_KPI_WEIGHT) A
 GROUP BY A.EVA_KPI_WEIGHT, A.EVA_KPI_NAME, A.EVA_KPI_ID, ROWID1
 ORDER BY ROWID1

Answer 4

my god..
查询的表数据量多大？

Answer 5

SELECT A.EVA_KPI_ID, 
      A.EVA_KPI_NAME, 
      A.EVA_KPI_WEIGHT, 
      ROWID1, 
      SUM(A.F1) F1, 
      SUM(A.F2) F2, 
      SUM(A.F3) F3, 
      SUM(A.F4) F4, 
      SUM(A.F5) F5, 
      SUM(A.F6) F6, 
      SUM(A.F7) F7, 
      SUM(A.F8) F8, 
      SUM(A.F9) F9, 
      SUM(A.F10) F10, 
      SUM(A.F11) F11, 
      SUM(A.F12) F12, 
      SUM(A.F13) F13, 
      SUM(A.F14) F14, 
      SUM(A.F15) F15, 
      SUM(A.F16) F16, 
      SUM(A.F17) F17, 
      SUM(A.F18) F18, 
      SUM(A.F19) F19, 
      SUM(A.F20) F20, 
      SUM(A.F21) F21, 
      SUM(A.F22) F22, 
      SUM(A.F23) F23, 
      SUM(A.F24) F24, 
      SUM(A.F25) F25, 
      SUM(A.F26) F26, 
      SUM(A.F27) F27, 
      SUM(A.F28) F28, 
      SUM(A.F29) F29, 
      SUM(A.F30) F30, 
      SUM(A.F31) F31, 
      MAX(A.COU) ISEND 
  FROM (SELECT T.REGION_ID, 
              T.EVA_KPI_ID, 
              T.EVA_KPI_NAME, 
              T.EVA_KPI_WEIGHT, 
              MAX(T.ROWID1) ROWID1, 
              MAX(T.COU) COU, 
              DECODE(T.REGION_ID, 10, ROUND(AVG(T.SCORE), 2),0) F1, 
              DECODE(T.REGION_ID, 20, ROUND(AVG(T.SCORE), 2),0) F2, 
              DECODE(T.REGION_ID, 21, ROUND(AVG(T.SCORE), 2),0) F3, 
              DECODE(T.REGION_ID, 22, ROUND(AVG(T.SCORE), 2),0) F4, 
              DECODE(T.REGION_ID, 23, ROUND(AVG(T.SCORE), 2),0) F5, 
              DECODE(T.REGION_ID, 24, ROUND(AVG(T.SCORE), 2),0) F6, 
              DECODE(T.REGION_ID, 25, ROUND(AVG(T.SCORE), 2),0) F7, 
              DECODE(T.REGION_ID, 27, ROUND(AVG(T.SCORE), 2),0) F8, 
              DECODE(T.REGION_ID, 28, ROUND(AVG(T.SCORE), 2),0) F9, 
              DECODE(T.REGION_ID, 29, ROUND(AVG(T.SCORE), 2),0) F10, 
              DECODE(T.REGION_ID, 311, ROUND(AVG(T.SCORE), 2),0) F11, 
              DECODE(T.REGION_ID, 351, ROUND(AVG(T.SCORE), 2),0) F12, 
              DECODE(T.REGION_ID, 371, ROUND(AVG(T.SCORE), 2),0) F13, 
              DECODE(T.REGION_ID, 431, ROUND(AVG(T.SCORE), 2),0) F14, 
              DECODE(T.REGION_ID, 451, ROUND(AVG(T.SCORE), 2),0) F15, 
              DECODE(T.REGION_ID, 471, ROUND(AVG(T.SCORE), 2),0) F16, 
              DECODE(T.REGION_ID, 531, ROUND(AVG(T.SCORE), 2),0) F17, 
              DECODE(T.REGION_ID, 551, ROUND(AVG(T.SCORE), 2),0) F18, 
              DECODE(T.REGION_ID, 571, ROUND(AVG(T.SCORE), 2),0) F19, 
              DECODE(T.REGION_ID, 591, ROUND(AVG(T.SCORE), 2),0) F20, 
              DECODE(T.REGION_ID, 731, ROUND(AVG(T.SCORE), 2),0) F21, 
              DECODE(T.REGION_ID, 771, ROUND(AVG(T.SCORE), 2),0) F22, 
              DECODE(T.REGION_ID, 791, ROUND(AVG(T.SCORE), 2),0) F23, 
              DECODE(T.REGION_ID, 851, ROUND(AVG(T.SCORE), 2),0) F24, 
              DECODE(T.REGION_ID, 871, ROUND(AVG(T.SCORE), 2),0) F25, 
              DECODE(T.REGION_ID, 891, ROUND(AVG(T.SCORE), 2),0) F26, 
              DECODE(T.REGION_ID, 898, ROUND(AVG(T.SCORE), 2),0) F27, 
              DECODE(T.REGION_ID, 931, ROUND(AVG(T.SCORE), 2),0) F28, 
              DECODE(T.REGION_ID, 951, ROUND(AVG(T.SCORE), 2),0) F29, 
              DECODE(T.REGION_ID, 971, ROUND(AVG(T.SCORE), 2),0) F30, 
              DECODE(T.REGION_ID, 991, ROUND(AVG(T.SCORE), 2),0) F31, 
              ROUND(AVG(T.SCORE), 2) SCORE 
          FROM (SELECT T.REGION_NAME, 
                      T.REGION_ID, 
                      T.EVA_KPI_ID, 
                      T.EVA_KPI_NAME, 
                      T.EVA_KPI_WEIGHT, 
                      T.COU, 
                      T.ROWID1, 
                      PKP_EVALUATION.GETKPIVALUE(T.REGION_ID, 
                                                  T.EVA_KPI_ID, 
                                                  ?, 
                                                  ?) SCORE 
                  FROM (SELECT * 
                        FROM MANAGE_REGION 
                        WHERE REGION_ID NOT IN (-1, 0, 1, 100)
                        UNION ALL
                        SELECT A.*, 
                               ROWNUM ROWID1, 
                              (SELECT COUNT(*) 
                               FROM EVALUSTION_KPI_CONFIG 
                               WHERE PARENT_EVA_KPI_ID = A.EVA_KPI_ID) COU 
                        FROM EVALUSTION_KPI_CONFIG A 
                        WHERE A.STATE = '0SA' 
                        CONNECT BY PRIOR 
                                   A.EVA_KPI_ID = A.PARENT_EVA_KPI_ID 
                        START WITH A.EVA_KPI_ID IN (1, 2, 3)) T 
                   GROUP BY T.REGION_ID, 
                        T.EVA_KPI_ID, 
                        T.EVA_KPI_NAME, 
                        T.EVA_KPI_WEIGHT) A 
GROUP BY A.EVA_KPI_WEIGHT, A.EVA_KPI_NAME, A.EVA_KPI_ID, ROWID1 
ORDER BY ROWID1;我想还有优化的可能，java代码执行慢是因为它还需要内存存放结果，封装到ResultSet等等

Answer 6

PKP_EVALUATION.GETKPIVALUE(T.REGION_ID, 
                                                  T.EVA_KPI_ID, 
                                                  ?, 
                                                  ?)这段东西里面是干什么的？

Answer 7

有时有这样的情况，jdbc执行的效率和plsql里执行的效率不一样，但是也是找不到原因。这里关注一下

Answer 8

解决了吗，我的也是这个问题，在Microsoft sql 2005中运行很快，但是在项目中运行时，会卡在sql的执行

Answer 9

我今天也遇见这样的问题，但是同样的sql另外一个web应用调用就有结果