aspose file tools*
The moose likes Performance and the fly likes low performance of resultSet.next() Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "low performance of resultSet.next()" Watch "low performance of resultSet.next()" New topic
Author

low performance of resultSet.next()

vivekanand Binod jha
Greenhorn

Joined: Jan 07, 2010
Posts: 2
I am getting very low speed of resultSet.next() method in while loop. can any one suggest me what to do for better performance.

CallableStatement stmt=connObject.prepareCall(strSQL); where strSQL is string which calls a stored procedure in DB. it takes 9 IN parameters and 10th parameter as OUT.

stmt.registerOutParameter (10, oracle.jdbc.driver.OracleTypes.CURSOR);
stmt.execute();

i am getting 10TH parameter cursor as OUT in stored procedure.

ResultSet cursor = ((oracle.jdbc.driver.OracleCallableStatement) stmt).getCursor(10);

while(cursor .next())
{
fetching column values for each row.
}

while only executing the cursor.next() first time it takes 10 min. after that all the fetching are fast.

so, can any one suggest me solution to reduce this timing.
vivekanand Binod jha
Greenhorn

Joined: Jan 07, 2010
Posts: 2
vivekanand Binod jha wrote:I am getting very low speed of resultSet.next() method in while loop. can any one suggest me what to do for better performance.

CallableStatement stmt=connObject.prepareCall(strSQL); where strSQL is string which calls a stored procedure in DB. it takes 9 IN parameters and 10th parameter as OUT.

stmt.registerOutParameter (10, oracle.jdbc.driver.OracleTypes.CURSOR);
stmt.execute();

i am getting 10TH parameter cursor as OUT in stored procedure.

ResultSet cursor = ((oracle.jdbc.driver.OracleCallableStatement) stmt).getCursor(10);

while(cursor .next())
{
fetching column values for each row.
}

while only executing the cursor.next() first time it takes 10 minutes. after that all the fetching are fast.

so, can any one suggest me solution to reduce this timing.
Costi Ciudatu
Ranch Hand

Joined: Oct 24, 2006
Posts: 74
How does the same statement behave when invoked from SQLPlus (or whatever raw SQL client) instead of JDBC ?
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16145
    
  21

vivekanand Binod jha wrote:... which calls a stored procedure in DB...


Stored procedures are very powerful. They are also horribly misused, and in some case I've seen, misused to the point where not only was the application permanently wedded to a specific brand of database, but fully half the source code to the application wasn't in the source code archives, it was in the database.

It's very likely that this particular stored procedure is doing some very heavy processing inside the database server. JDBC requests are synchronous, so until ALL processing is complete, the fetch/execute operation for a given SQL statement will "hang" on the attempt. So before worrying about the Java, I'd first get the DBA to help tune the stored procedure.


Customer surveys are for companies who didn't pay proper attention to begin with.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: low performance of resultSet.next()