7

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.
Improve this question
5
  • 10
    SQL Injection Hooooorrrrraaay! Sidenote: Using parameterized queries would also give you a better performance. Commented Jul 5, 2011 at 15:13
  • 2
    You've given a reason for the * - any reason you're not using a parameterized query? Commented Jul 5, 2011 at 15:14
  • Reducing the total number of queries executed by retreiving multiple sheetID/m pairs in each query (WHERE pair1 or pair2 or pair3 etc...) would reduce some of the overhead. Commented Jul 5, 2011 at 15:16
  • @Bobby: Since what I'm building is a Java program which doesn't require any input from the user, thus SQL injection problem will not bother me. Commented Jul 6, 2011 at 1:10
  • @Jon Skeet: I already tested the program by implementing parameterized query. It seems like the query speed is more or less the same. Commented Jul 6, 2011 at 1:12

8 Answers 8

Reset to default
6

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.

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

Comments

4

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. ....
Improve this answer

7 Comments

Hmmm... Do you think so? He said "The size of the List can grow up to 10000+"
I would still go for this way! The overhead for running each query is what is slowing him down currently. I presume going for an IN statement would also work: building SQL to say WHERE sheetID IN (1,2,3,4...) That is actually prettier to read and a lot shorter.
I think by using OR, you avoid the 1000 item limit for an IN clause in Oracle, which is good. But I'd definitely use a StringBuilder for all that String concatenation.
What is the effect of (1=1)? Doesn't that mean you get all rows, rather than those matching the sheetIds?
@sudocode my initial WHERE with OR was to avoid the 1000 limit for IN statements in Oracle. You have a valid point that some other method of building the string will look nicer.
|
3

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

Improve this answer

1 Comment

Anyway I'm trying to solve it by splitting them with 'OR' and "UNION'.
2

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.

Improve this answer

1 Comment

With a global temporary table, Oracle handles concurrency for you (each session having its own empty table to start with).
1

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.

Improve this answer

Comments

1

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.