1

it's been a while. I'm currently struggling on optimizing the performance of my application. Here's a snippet similar to what I currently have.

A Stored procedure with this query:

SELECT field_a, field_b, field_c
FROM table_a
WHERE field_a = param_a
AND field_b = param_b;

In Java, I have this ArrayList of SampleParameterClass with attributes of paramA and paramB declared as String.

ArrayList<SampleParameterClass> sampleParams = new ArrayList<SampleParameterClass>();

sampleParams.add(new SampleParameterClass(stringValueForParamA, stringValueForParamB);

Let's assume that sampleParams is populated by hundreds of elements.

try
    {
        conn = ConnectionPool.getConnection();

        con.setAutoCommit(false);

        CallableStatement myProcedure = connection.prepareCall("{ ? = call my_proc_name(?, ?) }");

        for(int paramCounter = 0; paramCounter < sampleParams.size(); paramCounter ++){

            myProcedure.registerOutParameter(1, Types.OTHER);
            myProcedure.setObject(2, sampleParams.get(paramCounter).getParamA);
            myProcedure.setObject(3, sampleParams.get(paramCounter).getParamB);
            myProcedure.execute();

            ResultSet rs = (ResultSet) myProcedure.getObject(1);

            while (rs.next()) {

                String resultA = rs.getString("param_a");
                String resultB = rs.getString("param_b");
                String resultC = rs.getString("param_c");

                myArrayListOfResult.add(new Result(resultA, resultB, resultC);

            }
        }

    }
    catch (SQLException e) {
        myProcedure.close();
        conn.close();
    }
}

Basically, what the code above does is to loop throughout all the parameters in the ArrayList and call the stored procedure for every parameter. If I have a thousands of data to be processed, it would be slow as a snail isn't it?

My question would be:
1) Is it possible to call the procedure just once even though I have thousands of parameters in queue? Something similar to passing the whole ArrayList of parameters.
2) If above question cannot be answered, are there any method to minimize the process?

Note:
-Same question for a procedure with an INSERT statement.
-I haven't found a similar question to this, if I missed something. Please do post a URL to that question and I'll mark it as an answer to this question.
-I haven't found a reading material something like passing a list in a procedure.

Thanks, any answer is much appreciated.

4
  • 1
    In what you've written, i don't see any reason for this to be a stored procedure. You could use a normal PreparedStatement, and probably make your life a bit easier. Commented May 14, 2013 at 8:42
  • Thanks Tom. The stored proc written above is for the sake of my question. Currently, the procedure actually includes at least 8 tables. On the other hand, about your suggestion, wouldn't that be the same as if it were a procedure? The query will also be called based on the number of elements in my sampleParams? Commented May 14, 2013 at 8:48
  • You're right that using a PreparedStatement doesn't fix that problem. But user2310289's answer gives a solution which works for both CallableStatement and PreparedStatement. Commented May 14, 2013 at 17:30
  • Thank you Tom! I'm currently working on it. As soon as I'm done, I'll mark his answer. Commented May 15, 2013 at 1:09

1 Answer 1

2

Why not pass the parameters as a delimtered string and then use string_to_array(text, text [, text]) on the postgresql side to convert the string into an array.

See http://www.postgresql.org/docs/9.1/static/functions-array.html

and

Postgres integer arrays as parameters?

Sign up to request clarification or add additional context in comments.

3 Comments

You don't even need to encode the array as a string - the PostgreSQL JDBC driver will accept them as instances of java.sql.Array. You can create those with Connection#createArrayOf and use them with PreparedStatement#setArray.
There's a section on Using Array Objects in the JDBC tutorial.
Marked as answered. Thank you user2310289. I would like to thank you by your name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.