asked on

Time delay on dumping oracle resultset in csv using open csv

private Connection con;
private Statement stmt;
con =   riverManager.getConnection(dbUrl, user, password);
stmt = con.createStatement();
ResultSet table = stmt.executeQuery("select * from tableA order by column1, column2,
column3");
StringWriter sw = new StringWriter();
CSVWriter csvw = new CSVWriter(sw);
csvw.writeAll(table, true);

Open in new window

Running this code on a oracle takes 10 times more time at the following line

csvw.writeAll(table, true);

Open in new window

than running it on sybase . The tables are identical in oracle and sybase. CSVWriter is class
used from the opencsv library. Please help.

Mick Barry

sounds like an issue with the jdbc driver. which one are you using?
try tweaking the fetch size for the result set

slightwv (䄆 Netminder)

Identical tables doesn't mean much. Tuning is a career and people make careers out of doing it.

Compare hardware, network speed, memory, disk speeds, caching, etc...

Check with the dba's for help figuring out if performance can be gained from normal tuning.

I assume opencsv creates a csv? There might be better ways with Oracle.

mrjoltcola

I'd look at the "order by" clause or remove the ordering clause completely to see if it affects performance. It is likely that the tables are ordered differently. On Oracle they are typically a heap table, with secondary indexes, unless you create an IOT with "organization index" which will more closely mimic a clustered index table on MS SQL or Sybase.

At least remove the ordering temporarily to determine if it is relevant.

PearlJamFanatic

ASKER

How do i increase fetch size. Code in JAVA please.

PearlJamFanatic

ASKER

I am using jconn2.jar

PearlJamFanatic

ASKER

and ojdbc6.jar. One is for sybase and other for oracle. I don't remeber which one is for which

ASKER CERTIFIED SOLUTION

Mick Barry

membership