Hi, everyone,
I have been stuck here for a week. Could someone please help me out?
I am using Oracle JDBC thin driver to connect to an Oracle DB, and
since the data I am pulling out of the DB is really large amount, I am
using CachedRowSet to get better performance. It works fine for some
query statements, like I got results in about 2 seconds. But for some
other query statements, it seems like the program just sit there for
several minutes, while at the mean time, the CPU usage and memory usage
are 0. Sometime it stuck at the Statement.executeQuery method, other
times it stuck at the CachedRowSet.populate method. I tried my best,
still it's not getting any better. I am posting my program and some
running results here. Please anyone, if you have any idea, please help
me! I am really appreciate it.
Note: For debugging purpose, I am displaying results in a JTable, on
the DOS window, and saving them into a file named "temp". Please ignore
those parts. And those date time stuffs are for debugging too. I was
trying to see how long each part takes.
Here is the code:
import java.io.*;
import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.math.*;
import sun.jdbc.rowset.CachedRowSet;
public class cachedRowSetTest2 {
public static void main (String[] args) {
getConnection();
}
public static void getConnection () {
Vector data = new Vector();
Vector columnName = new Vector();
Connection con = null;
Statement rs_stmt = null;
ResultSet rs = null;
CachedRowSet crs = null;
String userid = "...";
String passwd = "...";
String url = "jdbc
racle:thin:@...:...:...";
String query = "select PAT_TEST_HISTV.PI, PAT_TEST_HISTV.TEST_ID, PAT_TEST_HISTV.TEST_ABBR, PAT_TEST_HISTV.RSLT_VAL, PAT_TEST_HISTV.RSLT_DT_TM from RESULT.PAT_TEST_HISTV where ((PAT_TEST_HISTV.TEST_ABBR = 'ALKP') or (PAT_TEST_HISTV.TEST_ABBR = 'GGTP') or (PAT_TEST_HISTV.TEST_ABBR='ALT') or (PAT_TEST_HISTV.TEST_ABBR='AST') or (PAT_TEST_HISTV.TEST_ABBR='CK'))and (PAT_TEST_HISTV.PI between 31631 and 46766)";
BufferedWriter resultFile = null;
java.util.Date date1, date2, date3, date4;
int s1=0, s2=0, s3=0, s4=0, m1=0, m2=0, m3=0, m4=0, h1=0, h2=0, h3=0, h4=0;
int numberOfColumns = 0;
int counter=0;
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
DriverManager.setLogWriter(new PrintWriter(System.out) );
con = DriverManager.getConnection (url, userid, passwd);
System.out.println("before createStatement");
rs_stmt = con.createStatement();
System.out.println("after createStatement");
date1 = new java.util.Date();
s1 = date1.getSeconds();
m1 = date1.getMinutes();
h1 = date1.getHours();
System.out.println("the query statement is: " + query);
rs = rs_stmt.executeQuery(query);
date2 = new java.util.Date();
s2 = date2.getSeconds();
m2 = date2.getMinutes();
h2 = date2.getHours();
System.out.println("after executeQuery");
System.out.println("before create the cachedRowSet");
crs = new CachedRowSet();
System.out.println("after create cachedRowSet");
crs.populate(rs);
date3 = new java.util.Date();
s3 = date3.getSeconds();
m3 = date3.getMinutes();
h3 = date3.getHours();
System.out.println("after populate the cachedRowSet");
}catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (rs != null) rs.close();
if (rs_stmt != null) rs_stmt.close();
if (con != null) con.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
try {
resultFile = new BufferedWriter (new FileWriter("temp"));
}catch (IOException e) {
e.printStackTrace();
}
try {
ResultSetMetaData rsmd = crs.getMetaData();
numberOfColumns = rsmd.getColumnCount();
for (int i=1; i<=numberOfColumns; i++) {
columnName.addElement(rsmd.getColumnName(i));
}
while (crs.next()) {
counter++;
System.out.print(counter + ": " );
Vector record = new Vector();
try {
for (int i=1; i<=numberOfColumns; i++) {
System.out.print (crs.getString(i) + ", ");
String tempStr = crs.getString(i);
if (tempStr == null) {
resultFile.write(" ");
}
else {
resultFile.write(tempStr);
}
resultFile.write("\t");
record.addElement(crs.getString(i));
}
resultFile.newLine();
resultFile.flush();
}
catch (IOException e) {
e.printStackTrace();
}
System.out.println();
data.addElement(record);
}
}
catch (SQLException e) {
e.printStackTrace();
}
try {
resultFile.close();
}catch (IOException e) {
e.printStackTrace();
}
date4 = new java.util.Date();
s4 = date4.getSeconds();
m4 = date4.getMinutes();
h4 = date4.getHours();
System.out.println("time before the executeQuery: " + h1 + ":" + m1 + ":" + s1);
System.out.println("time after the executeQuery: " + h2 + ":" + m2 + ":" + s2);
System.out.println("time after populate the crs: " + h3 + ":" + m3 + ":" + s3);
System.out.println("time after printing out the crs contents: " + h4 + ":" + m4 + ":" + s4);
JTable resultTable = new JTable(data, columnName);
resultTable.setPreferredScrollableViewportSize (new Dimension (600, 600));
JScrollPane scrollPane = new JScrollPane (resultTable);
JPanel tablePanel = new JPanel();
tablePanel.setLayout (new BoxLayout(tablePanel, BoxLayout.Y_AXIS));
tablePanel.setBackground(Color.white);
tablePanel.add(scrollPane);
JFrame resultWindow = new JFrame ("Query Results");
resultWindow.addWindowListener(new CloseQuit());
resultWindow.getContentPane().add(scrollPane);
resultWindow.pack();
resultWindow.setVisible(true);
}
private static class CloseQuit extends WindowAdapter {
public void windowClosing (WindowEvent e ) {
System.exit(0);
}
}
}
And some sample running results are:
(1). ...
total amount of record is: 1449
connection, statement, and resultset are all closed
time before the executeQuery: 15:39:33
time after the executeQuery: 15:51:12
time after populate the crs: 15:51:12
time after printing out the crs contents: 15:51:13
...
(2). ...
total amount of record is: 1449
connection, statement, and resultset are all closed
time before the executeQuery: 16:15:02
time after the executeQuery: 16:15:09
time after populate the crs: 16:26:40
time after printing out the crs contents: 16:26:41
...
The only difference between the query statements for these two is the
first one has "order by" part. And it seems to me that the first one is
stuck at the executeQuery, and the second one stuck at the crs.populate.
It doesn't make any sense to me. Please help me out with this!
Thanks a lot.
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]
[ November 12, 2002: Message edited by: Sally ZX ]