CODEWITHSUNDEEP
javaoracle-database
Richie
Richie

Reputation: 5199

JDBC Performance - passing large resultset from database to Java

I have a performance related question. I need to retrieve about 500 rows from the database for the purpose of using the Apache POI to export the results into a Microsoft Excel spreadsheet.

Up until now for all my database queries I have been populating a PL/SQL object in the database layer and then returning that PL/SQL object to the Java and looping through the results.

But now that I need to return such a large result set to the Java from the db layer I've been asked a question about whether or not I think it might be better performance wise to return the 500 rows into the Java via an XML Clob.

This is a bit of an open question but I was hoping to get peoples opinion on this please.

thanks

Upvotes: 2

Views: 10752

Answers (2)

Scary Wombat
Scary Wombat

Reputation: 44834

As per http://docs.oracle.com/cd/E11882_01/java.112/e16548/resltset.htm#JJDBC28621

By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. This is the default Oracle row fetch size value. You can change the number of rows retrieved with each trip to the database cursor by changing the row fetch size value.

The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size:

void setFetchSize(int rows) throws SQLException
int getFetchSize() throws SQLException

Upvotes: 5

Sualeh Fatehi
Sualeh Fatehi

Reputation: 4784

Use a Java ResultSet. This will fetch only some rows at a time, as you need them. Here is an example of how to use it: http://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html Basically, every time you ask for a new row, as in rs.next(), the JDBC system decides if the data is available on the client, or needs to be fetched from the server. This way, you are not fetching all of the data at once.

Upvotes: 1

Related Questions