Why is retreiving a ResultSet from Oracle stored procedure soooo slow ?

Question

I have to improve some code where an Oracle stored procedure is called from a Java program. Currently the code is really really slow : up to about 8 seconds on my dev machine. On the same machine, if I call directly a SQL query that does about the same treatment and returns the same data, it takes under 100 ms...

The code creates a CallableStatement, registers one of the output parameters to be an Oracle cursor, and then retreives the cursor using the getObject method of the statement and parse it to ResultSet :

cstmt = conn.prepareCall("{ call PKG_ESPECEW.P_ListEspece( ?, ?, ?, ?, ?, ? ) }");
cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
[...]
cstmt.executeQuery();                           
rs = (ResultSet)cstmt.getObject(4);
rs.setFetchSize(1000); //supposed to help ?

options = new HashMap<String, String>(1000);
rs.next() //added that to measure exactly the length of the first call

while(rs.next()) {
    [...]
}

I put some timestamps in the code to know which part is taking so long. The result : the first call to rs.next() is taking up to various seconds. The result sets are average, from 10 to a couple thousands rows. As I said before, handling similar result sets coming from a regular PreparedStatement takes 10-100 ms depending the size.

Is anything wrong with the code ? How to improve it ? I'll do direct SQL qhere critical if I haen't any other solution, but I'd prefer a solution tha allows me to not rewrite all the procedures !

Edit : here is the definition of the stored procedure :

PROCEDURE P_ListEspece(P_CLT_ID IN ESPECE.ESP_CLT_ID%TYPE,     -- Langue de l'utilisateur
                        P_ESP_GROUP_CODE IN ESPECE.ESP_CODE%TYPE,-- Code du groupe ou NULL 
                        P_Filter IN VARCHAR2,                   -- Filtre de la requête
                        P_Cursor OUT L_CURSOR_TYPE,             -- Curseur
                        P_RecordCount OUT NUMBER,               -- Nombre d'enregistrement retourne
                        P_ReturnStatus OUT NUMBER);              -- Code d'erreur

Answer 1

Apparently the stored procedure is doing some data conversion/massaging forth and back (e.g. int <--> varchar). This is known to take a lot of time in case of large tables. Ensure that you've declared the right datatypes in the SP arguments and are setting the right datatypes in CallableStatement.

Answer 2

Hi Pierre,

How long does it take to execute the procedure outside of java ? Check with a script like this in SQL*Plus:

var ref refcursor
var cnt number
var status number
exec p_listespece (xx, yy, zz, :ref, :cnt, :status);--replace with actual values
print :ref

If it takes more than 10-100 ms, your problem may come from the stored procedure

Answer 3

"I thought the procedure was executed, then it's result stored in oracle server's memory, and finally transmitted back to the client (the java app) through the cursor and result set and JDBC"

That's incorrect. What oracle returns as a cursor is basically a pointer to a query (all ready with any bind variables). It has not materialized the result set in memory. It could be a massive result set of millions/billions of rows.

So it could well be a slow query that takes a long time to deliver results.