Hello again,
we are doing some data analysis on differnt database systems. We use generated standard sql-92 statements to determine several information like AVG, SUM, MAX, MIN values.
On a "huge" table (~40.000 records, 10 columns) our statement (which is like SELECT COUNT(myCol) cnt, MIN(myCol), MAX(myCol), AVG(myCol) FROM myTable) has a horrible performance. I have read in the FAQ to use RECCOUNT instead of COUNT, but we want (or: have to) use the SQL92-standard and we cannot implement different selects for different JDBC/DBMS types.
Do you have any hints to speed up the Access JDBC driver? Are there any properties which i can pass when connecting to the MDB File?
Regards,
Timo
|
According to your description, I do one test for 50,000 row, 11 column on PIII600. For aggregating on int type column, the speed is 4~5s. It seems not very slower. What's your enviroment? Whether your test case has longvarchar or OLE column? What's your mdb file size? My test case is 5492KB.
import java.sql.*;
import java.util.Properties;
public class testAccessSpeed4 {
public static void main(String argv[]) {
try {
Class.forName("com.hxtt.sql.access.AccessDriver");
String url = "jdbc:Access:/d:/temp";
Connection con = DriverManager.getConnection(url, new Properties());
if (false) { //init database
Statement astat = con.createStatement();
astat.execute("create database testaggregate");
astat.executeUpdate(" create table testaggregate.ledger (Trans_no int, Trans_code smallint,Trans_amt Decimal(12,2),Trans_time smallint, checknum varchar(9),"
+ "user_name varchar(12),serv_prov varchar(4),carr varchar(4),servind varchar(2),"
+ "tranid int,custactnr varchar(16));");
astat.close();
con.setCatalog("testaggregate.mdb");
PreparedStatement ps = con.prepareStatement(
"insert into ledger (Trans_no, Trans_code,Trans_amt,Trans_time, checknum,"
+ "user_name,serv_prov,carr,servind,"
+ "tranid,custactnr) values(?,?,?,?,?,?,?,?,?,?,?);");
long oldestTime=System.currentTimeMillis();
long oldtime=oldestTime;
int i;
for (i = 1; i <= 50000; i++) {
ps.setInt(1, i); //Trans_no
ps.setInt(2, 12345); //Trans_code
ps.setDouble(3, 12345.67); //Trans_amt
ps.setInt(4, 12345); //Trans_time
ps.setString(5, "checknum" + i); //checknum
ps.setString(6, "user" + i); //user_name
ps.setString(7, "pv" + (i % 100)); //serv_prov
ps.setString(8, "cr" + (i % 100)); //carr
ps.setString(9, "" + (i % 100)); //servind
ps.setInt(10, 54321); //tranid
ps.setString(11, "custactnr" + i); //custactnr
ps.executeUpdate();
if (i % 1000 == 0) {
long newtime=System.currentTimeMillis();
if(newtime>oldtime){
System.out.println(i + ":" + (newtime - oldtime));
oldtime = newtime;
}
}
}
long newtime=System.currentTimeMillis();
if(newtime>oldestTime){
System.out.println((i-1) + " rows :" + (newtime - oldestTime));
}
ps.close();
}
con.setCatalog("testaggregate.mdb");
Statement stmt = con.createStatement();
long oldtime=System.currentTimeMillis();
String sql="SELECT COUNT(Trans_no) cnt, MIN(Trans_no), MAX(Trans_no), AVG(Trans_no) FROM ledger";
ResultSet rs=stmt.executeQuery(sql);
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int iNumCols = resultSetMetaData.getColumnCount();
for (int j = 1; j <= iNumCols; j++) {
System.out.println(resultSetMetaData.getColumnLabel(j)
+ " " +
resultSetMetaData.getColumnTypeName(j)
+ " " +
resultSetMetaData.getColumnDisplaySize(j)
+ " " + resultSetMetaData.getPrecision(j)
+ " " + resultSetMetaData.getScale(j)
);
}
Object colval;
rs.beforeFirst();
int count=0;
while (rs.next()) {
count++;
for (int j = 1; j <= iNumCols; j++) {
colval = rs.getObject(j);
System.out.print(colval+" ");
}
System.out.println();
}
rs.close();
rs=null;
stmt.close();
long newtime=System.currentTimeMillis();
System.out.println(sql+":" + (newtime - oldtime)+"/"+count);
con.close();
}
catch (SQLException sqle) {
do {
System.out.println(sqle.getMessage());
System.out.println("Error Code:" + sqle.getErrorCode());
System.out.println("SQL State:" + sqle.getSQLState());
sqle.printStackTrace();
}
while ( (sqle = sqle.getNextException()) != null);
}
catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
/*
query spped following insert speed: (50000 rows :16885 50000 rows :15703)
SELECT COUNT(Trans_no) cnt, MIN(Trans_no), MAX(Trans_no), AVG(Trans_no) FROM ledger:3165/1 (quicker because insert cached pages)
independent query speed:
SELECT COUNT(Trans_no) cnt, MIN(Trans_no), MAX(Trans_no), AVG(Trans_no) FROM ledger:5408/1 (slower bececame without cache)
SELECT COUNT(Trans_no) cnt, MIN(Trans_no), MAX(Trans_no), AVG(Trans_no) FROM ledger:4877/1
*/
|