Optimize speed when executing a "select" query in a large loop

Question

I am executing a simple select query in a Java loop, as shown below. List size can grow up to 10000+. How to increase request speed? Any example or advice is appreciated. Thank you

Note that I need to get all the data in each column of this table, so use an asterisk (*).

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 several ways to speed up this request. I use the second method as it prevents the ORA-04031 error in the future.

• Use the parameterized query "SELECT" with the clause "IN".
• Create a nested table and listing the array / list of elements that come from JDBC to the created Nested table.
• Create a temporary table and insert a list of items. Then do the JOIN in the main table (1 query) and get the results.

Answer 1

When trying to speed it up, there are two things to consider:

• Run this request only once for all
• Make sure you run the same query every time without encoding the values. Since these values ​​may change, each request will look like a previous request, but with only a few different values. This prevents Oracle from reusing the previous query and results in incompatible SQL in the shared pool. This will populate the shared pool. Do this long enough and you will receive ORA-04031 error messages.

The way to use 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 sheets:

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

Create an SQL Type:

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

In your code, populate an instance of the SQL type with the values ​​in your "valuelist" and use the TABLE statement to convert 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 only one SQL in your shared pool and only one execution of this query, not thousands.

Answer 2

Time is mainly spent on preparing and fulfilling requests.

If instead you run a single query that returns all your results, this will speed up the execution significantly.

t

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

Answer 3

You have to prepare a sql query with the status IN, and then execute the query only once ...

Answer 4

Here are a few other ideas.

• Create a temporary table and insert list items (10k). Then connect to your main table (1 query) and get your results.

• Create a stored procedure to take a list of elements (through a nested table) as the result of the input and return specified by the out parameter.

I would choose option 1. because it is simpler for me and probably faster. But you have to be careful in parallel sessions, etc. Not sure how you want to handle multiple sessions (will they provide this data, will they have separate lists of data?).

Something to consider anyway.

Answer 5

Oracle can accept up to 1000 parameters in IN . Therefore, if you use a prepared statement, you will reduce the number of iterations 1000 times. Just split the list into pieces of 1000 items.

Answer 6

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

Answer 7

I don't know if this will be an improvement, but you can try selecting all entries and checking your Java code if sheetId . This is what you need to find out which is better.

Answer 8

This is a minor thing, but if you are going to dynamically build a query (without using binding variables), you should use createStatement, not prepareStatement. There is a small amount of overhead with prepareStatement that you do not need.