How to improve the select query performance in Java ? Is there a way to do select query using multi threading?

Question

I'm need to do a select query from a queue I have created in data base , The view has about 30,00,000 rows and is taking about 2 minutes to read this data and storing in local memory using JTDS JDBC driver . The order in which I read data does not matter . Right now I'm simply using a prepared statement and reading from a result set . Is there a better way to read from data base ? I'm reading from MS SQL server . The way I'm reading right now is

public ResultSet getData(String view_name) throws SQLException { 
    String SQL="select * from "+view_name; 
    PreparedStatement stmt=conn.prepareStatement(SQL); 
    resultSet= stmt.executeQuery(); 
    resultSet.setFetchSize(8000); 
    return resultSet;
}

1st question as always: do you really need all that data in memory? — Sirko, Jul 17 '15 at 09:04
Is it possible to move data processing part onto database and then just fetch processed outcome? — Rajesh, Jul 17 '15 at 09:11
I need to calculate some variables using the entities in the DB and store that result into a file — marvel308, Jul 17 '15 at 09:17
If I store the query into a file in my system would it take less time to create a file ? I could then read values from the file and process — marvel308, Jul 17 '15 at 09:20

score 0 · Accepted Answer · answered Jul 17 '15 at 09:42

As you already know the performance of the application will degrade as soon as the size of available memory starts decreasing which will return in more frequent GC cycles.

Is there a better way to read from data base ?

Did you try streaming of ResultSet and the use of Adaptive Buffering? What is adaptive response buffering and why should I use it?

Adaptive buffering is designed to retrieve any kind of large-value data without the overhead of server cursors. The application can execute a SELECT statement that produces more rows than the application can store in memory. Adaptive buffering provides the ability to do a forward-only read-only pass of an arbitrarily large result set without requiring a server cursor.

When large values are read once by using the get<Type>Stream methods, and the ResultSet columns and the CallableStatement OUT parameters are accessed in the order returned by the SQL Server, adaptive buffering minimizes the application memory usage when processing the results.

You can have a look at this MSDN library post for more info on Using Adaptive Buffering.

Not sure about MS SQL, but in MySQL streaming of resultset can be enable as below.

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                    java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

Hope this helps you.

Is adaptive buffering supported in JTDS driver ? – marvel308 Jul 17 '15 at 10:04 — marvel308, Jul 17 '15 at 10:04

How to improve the select query performance in Java ? Is there a way to do select query using multi threading?

1 Answers1