Increase ResultSet iteration performance

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"));
 }
sapientconceptionsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mick BarryJava DeveloperCommented:
try adjusting the fetch size of the statement.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sapientconceptionsAuthor Commented:
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...
Mick BarryJava DeveloperCommented:
rs.close();

u should close result sets after you are done with them
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Mayank SAssociate Director - Product EngineeringCommented:
Mick BarryJava DeveloperCommented:
Wouldn't that still have the same problem that the rows still need to be retrieved?
Mayank SAssociate Director - Product EngineeringCommented:
But I guess the performance is better because its disconnected?
Mick BarryJava DeveloperCommented:
It still has to get the result set from somewhere originally.
Mayank SAssociate Director - Product EngineeringCommented:
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.
Mayank SAssociate Director - Product EngineeringCommented:
Yes, it has to get it originally, but once your get it, you can iterate faster, can't you?
Mick BarryJava DeveloperCommented:
correct.
Mayank SAssociate Director - Product EngineeringCommented:
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.
Mayank SAssociate Director - Product EngineeringCommented:
CachedRowSet download can also be found on the link that I posted above.
grim_toasterCommented:
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)."

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.