Win a copy of Modern JavaScript for the Impatient this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

low performance of resultSet.next()

 
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.

 
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 ?
 
Saloon Keeper
Posts: 22480
151
Android Eclipse IDE Tomcat Server Redhat Java 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.
 
    Bookmark Topic Watch Topic
  • New Topic