CODEWITHSUNDEEP
javaoracle-databasejdbc
Pierre Henry
Pierre Henry

Reputation: 17487

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 parameters 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

Upvotes: 4

Views: 8861

Answers (4)

edestrero
edestrero

Reputation: 516

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;

`

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

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.

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

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

Upvotes: 3

BalusC
BalusC

Reputation: 1109655

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.

Upvotes: 1

Related Questions