Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Cobol v5.0
  HXTT DBF v7.1
  HXTT Excel v6.1
  HXTT Json v1.0
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.0
Offshore Outsourcing
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
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT ACCESS
Performance problems
Timo
2006-09-25 07:25:21
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
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
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Refund | Sitemap