Optimize the speed on performing "select" query in a big loop

I'm performing a simple 'select' query in a Java loop as what is shown below. The size of the List can grow up to 10000+. How can I improve the query speed? Any example or advice is appreciated. Thanks.

Do note that I need to retrieve all data in every column of that table, so that's why the asterisk (*) is used.

List<String> valueList = ....
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    conn = DriverManager.getConnection(dbURL, dbUsername, dbPassword);
    for (int m = 0; m < valueList.size() ; m++) {    
         String sql = "SELECT * FROM WORKSHEET WHERE " + sheetId + " = '" +    
                      valueList.get(m) + "'";
         ps = conn.prepareStatement(sql);
         rs = ps.executeQuery();
         // retreive data....           
    }
}

Edit: At the end, there are a few ways to speed this query up. I'm using the second way as it prevent ORA-04031 error in future.

1. Use parameterized 'SELECT' query with 'IN' clause.
2. Create a Nested table and cast array/list of items that comes from JDBC to the created Nested table.
3. Create a temporary table and insert the list of item开发者_开发知识库s. Then perform a JOIN to the main table (1 query) and get the results.

There are two things to consider when trying to speed this up:

1. Execute this query only once for all sheetid's
2. Make sure you are executing the same query each time, by not hardcoding the values in. Since these values possibly change, each query will look like the previous query, but with only a few different values. This won't allow Oracle to re-use the previous query and leads to non sharable SQL in the shared pool. This will fill up the shared pool. Do this long enough and you'll receive ORA-04031 error messages.

The way to go is to use SQL types. Here is an example in PL/SQL. You can use the same principle in Java.

First create a table with ten thousand sheetId's:

SQL> create table worksheet (sheetid)
  2  as
  3   select level
  4     from dual
  5  connect by level <= 10000
  6  /

Table created.

Create a SQL type:

SQL> create type mynumbers is table of number;
  2  /

Type created.

In your code, fill an instance of the SQL type with the values in your "valuelist" and use the TABLE operator to transform the type to table values:

SQL> declare
  2    valuelist mynumbers := mynumbers(23,124,987,6123,8923,1,7139);
  3  begin
  4    for r in
  5    ( select ws.sheetid
  6        from worksheet ws
  7           , table(valuelist) vl
  8       where ws.sheetid = vl.column_value
  9    )
 10    loop
 11      dbms_output.put_line(r.sheetid);
 12    end loop;
 13  end;
 14  /
1
23
124
987
6123
7139
8923

PL/SQL procedure successfully completed.

Now you have just one SQL in your shared pool and just one execution of this query, instead of thousands.

The time is mainly spent preparing and executing the queries.

If instead you run a single query that returns all your results, that will make things a lot faster.

ie:

String where = "(1=0) "
// first build the where string
for (int m = 0; m < valueList.size() ; m++ ) {
  where = where + " OR (" + sheetId + " = '" + valueList.get(m) + "'";
  }
// then run a single query
sql = "SELECT * FROM WORKSHEET WHERE " + where;
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
// retrieve and handle data. ....

You must prepare sql query with IN statment and then execute query just once...

Here's a few other ideas.

1. Create a temp table and insert your (10k) list items. Then perform a join to your main table (1 query) and get your results.

2. Create a stored procedure to take a list of items (via nested table) as input and return result set via out parameter.

I'd opt for option 1. because its more straightforward to me and probably faster. But you need to be careful about concurrent sessions, etc. Not sure how you want to handle multiple sessions (will they share this data, will they have separate data lists?).

Something to consider anyway.

Oracle can take up to 1000 parameters in IN clause. So if you use prepared statement you will reduce number of iterations 1000 fold.Just split the list in pieces of 1000 elements.

You can try something with clause: sheetId IN ('1', '2', '3', '4') And please make sure that the column sheetId has an index key.

I don't know if it would be an improvement, but you could try selecting all records and checking with your Java code if the sheetId matches. This is something you should time to know what is better.

It's a minor thing, but if you are going to construct the query dynamically (without using bind variables), you should use createStatement, not prepareStatement. There is a small amount of overhead with prepareStatement that you don't need.