CODEWITHSUNDEEP
javaoracle-databasejdbc
Wilts C
Wilts C

Reputation: 1750

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 items. Then perform a JOIN to the main table (1 query) and get the results.

Upvotes: 7

Views: 5381

Answers (8)

user591593
user591593

Reputation:

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

Upvotes: 1

Rob van Wijk
Rob van Wijk

Reputation: 17705

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.

Upvotes: 6

Carl
Carl

Reputation: 11

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.

Upvotes: 0

tbone
tbone

Reputation: 15493

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.

Upvotes: 2

Eljakim
Eljakim

Reputation: 6937

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

Upvotes: 4

Alex Gitelman
Alex Gitelman

Reputation: 24732

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.

Upvotes: 1

Martijn Courteaux
Martijn Courteaux

Reputation: 68907

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.

Upvotes: 0

Ovais Khatri
Ovais Khatri

Reputation: 3211

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

Upvotes: 3

Related Questions