[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

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.
0
PearlJamFanatic
Asked:
PearlJamFanatic
1 Solution
 
objectsCommented:
sounds like an issue with the jdbc driver. which one are you using?
try tweaking the fetch size for the result set
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
mrjoltcolaCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
PearlJamFanaticAuthor Commented:
How do i increase fetch size. Code in JAVA please.
0
 
PearlJamFanaticAuthor Commented:
I am using jconn2.jar
0
 
PearlJamFanaticAuthor Commented:
and ojdbc6.jar. One is for sybase and other for oracle. I don't remeber which one is for which
0
 
objectsCommented:
rs.setFetchSize(fetchSize);
0
 
PearlJamFanaticAuthor Commented:
stmt.setFetchSize(10000); did the trick.
Now dmping activity takes the same time as sybase. Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now