Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Cobol v2.1
  HXTT DBF v5.2
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT ACCESS
Performance problems
Timo
2006-09-25 07:25:21.0
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
Re:Performance problems
HXTT Support
2006-09-25 08:33:13.0
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
*/


Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap