Solved

Increase ResultSet iteration performance

Posted on 2003-12-04
15
801 Views
Last Modified: 2008-01-16
I have a resultset that I iterate through and apply the results to an object.  Each row returned represents one object and can range anywhere from 4000 rows to 50000 rows (fyi, this is just for one user).  However, I'm noticing a huge performace slow down, which I wasn't really expecting and I'm thinking there may be a better way of doing it.

I've timed it with the creation of the objects with the data (approx. 12 columns per row) and I've timed it with no object creation, just looping the resultset...Although the latter is faster, it only beats it by a slight margin, i.e. a few fractions of a second.  For instance an iteration of 3500 records with object creation takes 19.5 secs same resultset without object creation takes 18.7 secs.  

Any suggestions as to how to optimize this -- or can it be?

ResultSet rs = ps.executeQuery ();

while (rs.next()) {

          Object obj = new Object (r.getObject ("column1"),
                                   r.getObject ("column2"),
                                   r.getObject("column3"),
                                   r.getObject ("column4"),
                                   r.getObject ("column5"),
                                   r.getObject ("column6"),
                                   r.getObject ("column8"),
                                   r.getObject ("column9"),
                                   r.getObject ("column10"),
                                   r.getObject("column11"),
                                   r.getObject ("column12"));
 }
0
Comment
Question by:sapientconceptions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 92

Accepted Solution

by:
objects earned 50 total points
ID: 9878643
try adjusting the fetch size of the statement.

0
 

Author Comment

by:sapientconceptions
ID: 9878843
Thanks for that.  That defin. sped things up.  

Now, is it possible to force a release of the ResultSet from memory once it's done?  WIth the increase of fetchSize there is a tremendous increase in memory usage...
0
 
LVL 92

Expert Comment

by:objects
ID: 9878888
rs.close();

u should close result sets after you are done with them
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:Mayank S
ID: 9879964
0
 
LVL 92

Expert Comment

by:objects
ID: 9879996
Wouldn't that still have the same problem that the rows still need to be retrieved?
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 9880279
But I guess the performance is better because its disconnected?
0
 
LVL 92

Expert Comment

by:objects
ID: 9880302
It still has to get the result set from somewhere originally.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 9880320
Generally, I prefer using CachedRowSet because you can use it after closing your connection with the database. Also helps in multi-user environments because there are no locks.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 9880323
Yes, it has to get it originally, but once your get it, you can iterate faster, can't you?
0
 
LVL 92

Expert Comment

by:objects
ID: 9880337
correct.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 9880404
sapientconceptions,

CachedRowSet example:

// get your connection
// make a statement
// get the resultSet, say rsData

CachedRowSet crData = new CachedRowSet () ;
crData.populate ( rsData ) ;

// you can close your connection after this and use the CachedRowSet methods for accessing data
// most of its method signatures are same as ResultSet - getString (), getInt (), next (), etc.

Mayank.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 9880424
CachedRowSet download can also be found on the link that I posted above.
0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 9880787
Isn't it more efficient to use getObject(int) than getObject(String) (see link below)?  If you don't know what they are to be, you could acquire the meta data upfront and convert the column names to indexes there.  Also, have you tuned your Query that is run as much as possible?

http://www.datadirect-technologies.com/products/jdbc/docs/jdbcdesign2.asp - The quote:
"For example, suppose you have a result set that has 15 columns and 100 rows, and the column names are not included in the result set. You are interested in three columns, EMPLOYEENAME (a string), EMPLOYEENUMBER (a long integer), and SALARY (an integer). If you specify getString(“EmployeeName”), getLong(“EmployeeNumber”), and getInt(“Salary”), each column name must be converted to uppercase, and lookups would increase considerably. Performance would improve significantly if you specify getString(1), getLong(2), and getInt(15)."

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
hibernate example using maven 12 113
Running JavaFX on JDeveloper 12C 1 118
add projects t working set in maven 2 72
how to debug htl and js pages 8 80
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question