Why is ResultSet from an Oracle stored procedure so slowly restored?

Question

I need to improve the code where the Oracle procedure is stored from a Java program. Currently, the code is really very slow: up to 8 seconds on my development machine. On the same machine, if I directly call an SQL query that makes about the same request and returns the same data, it takes a value of less than 100 μs ...

The code creates a CallableStatement, registers one of the output parameters as an Oracle cursor, and then retrieves the cursor using the operator's getObject method and analyzes it on a 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 find out which part takes so long. Result: the first call to rs.next() takes up to several seconds. The result sets are average, from 10 to several thousand lines. As I said, processing similar result sets coming from a regular PreparedStatement is 10-100 μs depending on size.

Is there something wrong with the code? How to improve it? I will do direct SQL, where it is critical if I have no other solution, but I would prefer a solution that allows me not to 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 

Answer 1

"I thought the procedure was completed, and then it is stored in oracle server memory and finally passed back to the client (java application) through the cursor and result set and JDBC"

This is not true. What the oracle returns as a cursor is basically a pointer to the request (everything is ready with any bind variables). He did not materialize the result set in memory. It can be a massive array of millions / billion rows.

Thus, it can be a slow query that takes a long time to provide results.

Answer 2

Apparently, the stored procedure does some conversion / massaging of the data back and forth (e.g. int ↔ varchar ). This is known to be time consuming for large tables. Make sure you specify the correct data types in the SP arguments and specify the correct data types in the CallableStatement .

Answer 3

How long does it take to execute a 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 arise from a stored procedure.

Answer 4

I had the same problem, we solved (I and the oracle dedicated guy), changing the returned parameter with the cursor to varchar, it was a simple request that is stored in the internal device. it was a huge implementation, I don’t know if this is applicable to your scenario.

here's a 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; 

`