Solved

Increase ResultSet iteration performance

Posted on 2003-12-04
15
761 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
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JList custom Cell Renderer refresh 15 56
servlet  URL Rewriting 1 36
jsp login check 12 30
Adding multiple JVM environments to RedHat 6 7 20
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
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 …
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

776 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