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?
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.
Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
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.