This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes CallableStatement and ResultSet - Performance Issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "CallableStatement and ResultSet - Performance Issue" Watch "CallableStatement and ResultSet - Performance Issue" New topic
Author

CallableStatement and ResultSet - Performance Issue

Ponery Nishad
Greenhorn

Joined: May 08, 2008
Posts: 5
Hi All,

I have a result set object which contains more than 3000 records and 10 columns returned through a callable statement. And I am making an arrayList which contains string[] out of the result set.

The structure is like ...



But it is taking more than 2 mins for the iteration through the java code.

Please find the the code block...



Please help me to fine tune this code so that I can iterate the records fastly.

Thanks in advance...

regards,
nponery
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30136
    
150

Ponery,
The first step is to find the bottleneck. Common places are:
1) The stored proc itself - how long does the query take?
2) Bandwidth - are you using all the data that gets returned


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Ponery Nishad
Greenhorn

Joined: May 08, 2008
Posts: 5
Jeanne,

Thank you very much for the reply.

Please see the details for your questions.

1) The stored proc itself - how long does the query take?
The procedure excecution is taking hardly 6-7 seconds. (since db contains huge data volume).
2) Bandwidth - are you using all the data that gets returned
Yes. But I felt, its not a problem with bandwidth.

I am using BEA WL 8 in Unix Box with 8GB RAM and 3GHz Processor Speed (also using DataSource and weblogic connection pool) and Oracle DB is located in Remote Machine.

I found the problem is in the above code snippet I mentioned.

Please find the code with the execution time it has taken




This code block took 2 mins and 53 seconds to complete the iteration.

I would like to know, when we call rs.next()/rs.getString() "Will it hit DB for each call to retrieve the info on resultset object??? "

If yes, please suggest me some other good approach. If no, how to fine tune this code?

Thanks again...

regards,
nponery
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

The stored proceure is taking 6-7 seconds to prepare the cursor, however you are processing the data in your java code which includes the execution time.
I am also interested to know if you are executing procedure on server itself on executing it from any sql client.

1) Can you remove inner for loop and extract the data for all 10 columns and provide us timing
2)provide the timing when you extract one column only.

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
The loop doesn't look like it would take that long. You could test its performance without the query by simply assigning the same string to all values in the data structure. Also you could use jamon for timing your sql and jdbc methods.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Ponery Nishad
Greenhorn

Joined: May 08, 2008
Posts: 5
Hi,

Problem is with the resultset object. Since it is a CONNECTED one, if I call rs.next() or rs.getString(), it has an overhead to reach db and come.

(As I told earlier, the resultset fetches 2042 rows and 10 columns = 20420)

Now I am trying with another approach using CachedRowSet, which uses DISCONNECTED concepts.

I am using PL/SQL for querying.

In my PL/SQL procedure I have 5 in IN parameters and 2 OUT parameters(which are cursors).



where con is my connection object.


Since I am not able to set the out params, it is giving the below exceptions.



This exception means, code is able to call the PL/SQL.

We have callableStatement.registerOutParameter(<index>,<SQLType>) method in CallableStatement.

What is the similar method using CachedRowSet or how to set the OUT param values using CachedRowSet concept?

Please throw some light on this issue...

regards,
Nishad P
Ponery Nishad
Greenhorn

Joined: May 08, 2008
Posts: 5
OR is there any other way/approach to use the DISCONNECTED concept to call the PL/SQL with OUT parameter(s) ?
 
Don't get me started about those stupid light bulbs.
 
subject: CallableStatement and ResultSet - Performance Issue
 
Similar Threads
PreparedStatement (displaying records)
SoDuko puzzle
Sudoku solver help (not brute force)
Displaying records through result set meta data
Whats wrong with this code