Solved

Increase ResultSet iteration performance

Posted on 2003-12-04
15
791 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

Independent Software Vendors: 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
eclipse console opening separately 2 37
Problem to Alipay 10 70
collection output issue 9 62
Pass multiple values or string arrays in java as a parameter 3 41
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
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…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

749 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