JDBC ResultSet.getArray

[niting]

Hi All,
I am working on a performance critical application .I need to retrive Ids of thousands of employees and store them in String[] and some times in int[] .
On testing my application using JProfiler I found that much of the processing time is being spend iterating over resultset.

I am using the following kind of code

List empList=new ArrayList();
while(rs.next())
{
empList.add(rs.getString("emp_id"));
}
String[]empIds=(String[])empList.toArray(new String[0]);

I found that getArray() method of resultset is useful but I am not finding a suitable driver for this.Also my application needs to support multiple databases like SQLServer ,Oracle and Postgresql
Can any one help me in this .
Thanks
Regards Nitin

[mnuttall]

Having just dealt with this issue - your problem is more likely with your cursor type. You might want to change the fetch size. Oracle drivers default to 10. I found that 500 to 1000 is better. At least for the situation I was in.

If your application needs to support multiple dbs - check out Hibernate (http://hibernate.org/ ) or a JDO vendor. I like Hibernate.

[niting]

Hi ,
I am not getting performance even after uisng fetch size.
Can u sugget me drivers that support the getArray() method of resultset for SQLServer and Oracle
Thanks

[mnuttall]

I don't know of any that do. Mostly becuase I have not researched it because it was not needed.

I doubt the slow down is in the resultset.get___ . I've used Oracle to return large numbers of objects. The slowness was in pulling the data. Not building the objects.

[niting]

Hi,
Yes u are absolutely right.
setFetchSize actually improved the performance my problem was mainly in retrieving data which was solved by optimizing the query
Thanks for suggestion

[mnuttall]

Excellent. Good call on the query too.

Remember that the driver and cursor type and network all can affect how it performs.