Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Increase ResultSet iteration performance

Posted on 2003-12-04
15
Medium Priority
?
902 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
13 Comments
 
LVL 92

Accepted Solution

by:
objects earned 200 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This video teaches viewers about errors in exception handling.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

824 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