Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

low performance of resultSet.next()

 
vivekanand Binod jha
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How does the same statement behave when invoked from SQLPlus (or whatever raw SQL client) instead of JDBC ?
 
Tim Holloway
Saloon Keeper
Pie
Posts: 18152
52
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic