Why is retrieving a ResultSet from Oracle stored procedure so slow?

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 development machine. On the same machine, if I directly call an 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 paramet开发者_运维问答ers to be an Oracle cursor, and then retrieves 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 do I improve it? I'll do direct SQL where critical if I haven't any other solution, but I'd prefer a solution that allows me to not rewrite all the procedures!

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

"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.

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.

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.

I had the same problem, we solved (me and the oracle dedicated guy) by changing the returned parameter from a cursor to a varchar, that was the plain query the stored was executing internally. this was an huge implementation, I don't know if this is applicable for your scenario.

here's the snippet :

`

String sql = "call MyStored(?,?,?,?)";
CallableStatement st = Conn.prepareCall(sql);
st.setInt(1, 10);
st.setInt(2, 20);
st.setInt(3, 30);
st.registerOutParameter(4, OracleTypes.VARCHAR);

st.execute();

String query = (String) st.getObject(4);
Statement stmt = Conn.createStatement();
rs = stmt.executeQuery(query);
[...]
//work with resultset
[...]
stmt.close();
stmt = null;

`