Java/Oracle连接性能问题

Question

我使用以下代码连接到Oracle存储过程，平均jdbcExecutionTime为860ms，执行加上序列化时间为172ms

jdbcExecutionTime与我有关，我知道它可以做得更快，但从我所读到的来看CallableStatement应该是最快的

这里有什么改进性能的提示吗

另外：我将Oracle 11与ojdbc14.jar一起使用

final long jdbcStartTime1 = System.currentTimeMillis(); 
    final long jdbcEndTime1;
    final long jdbcEndTime2;
    Connection con = null;

    logger.info("Loading properties...SYS TIME:"+System.currentTimeMillis());

    InputStream inputStream = blah.class.getClassLoader().getResourceAsStream("properties/jdbc.properties");
    logger.info("Done loading properties...SYS TIME:"+System.currentTimeMillis());
    try {

        PROPERTIES.load(inputStream); //Load the jdbc properties
        Class.forName(PROPERTIES.getProperty("jdbc.driverClassName")).newInstance(); //Load the oracle driver
        //Set the connection 
        logger.info("Starting JDBC connection...SYS TIME:"+System.currentTimeMillis());
        con = DriverManager.getConnection(PROPERTIES.getProperty("jdbc.url"), PROPERTIES.getProperty("jdbc.username"), PROPERTIES.getProperty("jdbc.password"));
        ((OracleConnection)con).setDefaultRowPrefetch(80);

        logger.info("JDBC connection established...SYS TIME:"+System.currentTimeMillis());

        String jobquery =   "{call PKG_TEST.MY_PACKAGE(?,?,?,?,?,?)}";  
        CallableStatement callStmt = con.prepareCall(jobquery);

        callStmt.setString(1,blah);                     
        callStmt.setBoolean(2, blah2);
        callStmt.registerOutParameter(3, OracleTypes.CURSOR);   
        callStmt.registerOutParameter(4, OracleTypes.CURSOR);   
        callStmt.registerOutParameter(5, OracleTypes.CURSOR);   
        callStmt.registerOutParameter(6, OracleTypes.CURSOR);   

        jdbcEndTime1 = System.currentTimeMillis();

        logger.info("Executing stored procedure...SYS TIME:"+System.currentTimeMillis());
        callStmt.execute();
        logger.info("Stored procedure complete...SYS TIME:"+System.currentTimeMillis());

        logger.info("Storing db cursor objects as result sets...SYS TIME:"+System.currentTimeMillis());
        ResultSet cur1 = (ResultSet)callStmt.getObject(3);
        ResultSet cur2 = (ResultSet)callStmt.getObject(4);
        ResultSet cur3 = (ResultSet)callStmt.getObject(5);
        ResultSet cur4 = (ResultSet)callStmt.getObject(6);
        logger.info("Completed storing cursors...SYS TIME:"+System.currentTimeMillis());

        jdbcEndTime2 = System.currentTimeMillis();

        final long jdbcExecutionTime =  (jdbcEndTime1-jdbcStartTime1);
        final long spEx_ResultSetTime = (jdbcEndTime2-jdbcEndTime1);

Answer 1

第一次这样做时，大部分时间将用于加载库（只有在第一次使用库时才会发生）和创建连接。一旦您有了现有的连接，您应该期待a<；10毫秒延迟（比您正在做的更长）