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
HXTT Access performance
Kevin Shih
2007-02-24 21:36:21.0
I have a query that is selecting data from 2 tables using a join. The primary table has 311k records. The child table has 681k records. The query using HXTT does not return even after 60+ minutes of running. I run the same query in Access and it returns in seconds. Is there something I am missing or is there some parameters I can pass into the driver to increase performance?

The query I am using is:
SELECT "Connect".CallTableID, "Connect".PortID, "Connect".PortName, "Connect".GroupID, "Connect".GroupName, "Call".Extension, "Call".CallType, "Connect".PartyID, "Connect".CtrlPartyID, "Call".DialedNumber, "Call".CallerID, "Connect".Duration, "Connect".ConnectTime FROM "Call" INNER JOIN "Connect" ON "Call".ID = "Connect".CallTableID WHERE "Call".CallType in (2,3,4) AND "Connect".PartyType=2 and "Call".ID > ?;
Re:HXTT Access performance
HXTT Support
2007-02-24 21:39:33.0
Create index on "Call".ID. You can create index for "Call".CallType, and "Connect".PartyType too.
Re:Re:HXTT Access performance
Kevin Shih
2007-02-24 21:42:12.0
Thanks for the fast response.

"Call".ID is the primary key of the parent table, so it is already indexed. As for "Connect".CallTableID, it is not indexed, nor can I create an index as the database does not belong to us. It is a database being provided by another system that we wish to query.

Any other suggestions?
Re:Re:Re:HXTT Access performance
HXTT Support
2007-02-24 22:28:25.0
Whether Call has only one primary index for ID column, and hasn't other index? Whether Connect hasn't any index?
Re:Re:Re:Re:HXTT Access performance
Kevin Shih
2007-02-24 23:04:33.0
Both Call and Connect only have the indices of the PK fields, Call.ID and Connect.ID. No other indices exist for either table.
Re:Re:Re:Re:Re:HXTT Access performance
HXTT Support
2007-02-25 01:26:38.0
We're writing code to recur your issue. Thanks for your patience.
Re:Re:Re:Re:Re:Re:HXTT Access performance
HXTT Support
2007-02-25 06:20:26.0
Tested. The main reason is that you haven't an index on CallTableID, but HXTT SQL engine decided a wrong optimization weight calculation on inner join. Please download the latest package, which should be quicker now, after two hours.

BTW, it seems that you like to use two SQL reserved word( call and connect), and we have supported it in our engine. Now you can use:
SELECT Connect.CallTableID, Connect.PortID, Connect.PortName, Connect.GroupID, Connect.GroupName, Call.Extension, Call.CallType, Connect.PartyID, Connect.CtrlPartyID, Call.DialedNumber, Call.CallerID, Connect.Duration, Connect.ConnectTime FROM Call INNER JOIN Connect ON Call.ID = Connect.CallTableID WHERE Call.CallType in (2,3,4) AND Connect.PartyType=2 and Call.ID = ?;


I'm enclosing our test code below since you needn't it in fact:
import java.sql.*;
import java.util.Properties;

public class testAccessSpeed8 {
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 testjoin");

astat.executeUpdate(" create table testjoin.Call (ID int, Extension varchar(12),CallType int,DialedNumber varchar(16), CallerID int,"
+ "PartyType int,primary key (ID));");

astat.executeUpdate(" create table testjoin.Connect (ID int, CallTableID int, PortID int,PortName varchar(12),GroupID int,GroupName varchar(16), PartyID int, PartyType int, "
+ "CtrlPartyID int,Duration int, ConnectTime timestamp, primary key (ID));");

astat.close();

con.setCatalog("testjoin.mdb");

PreparedStatement ps = con.prepareStatement( "insert into Call (ID, CallType ,DialedNumber) values(?,?,?);");

for (int i = 1; i <= 311000; i++) {
ps.setInt(1, i); //ID
ps.setInt(2, i%8); //CallType
ps.setInt(3, i); //DialedNumber
ps.executeUpdate();
}
ps.close();

ps = con.prepareStatement( "insert into Connect (ID, CallTableID ,PartyType,PortName) values(?,?,?,?);");

for (int i = 1; i <= 681000; i++) {
ps.setInt(1, i); //ID
ps.setInt(2, i%311000); //CallTableID
ps.setInt(3, i%4); //PartyType
ps.setInt(4, i); //PortName
ps.executeUpdate();
}
ps.close();

}

con.setCatalog("testjoin.mdb");

long oldtime=System.currentTimeMillis();
// String sql="SELECT Connect.CallTableID, Connect.PortID, Connect.PortName, Connect.GroupID, Connect.GroupName, Call.Extension, Call.CallType, Connect.PartyID, Connect.CtrlPartyID, Call.DialedNumber, Call.CallerID, Connect.Duration, Connect.ConnectTime FROM Call INNER JOIN Connect ON Call.ID = Connect.CallTableID WHERE Call.CallType in (2,3,4) AND Connect.PartyType=2 and Call.ID > ?; ";
String sql="SELECT Connect.CallTableID, Connect.PortID, Connect.PortName, Connect.GroupID, Connect.GroupName, Call.Extension, Call.CallType, Connect.PartyID, Connect.CtrlPartyID, Call.DialedNumber, Call.CallerID, Connect.Duration, Connect.ConnectTime FROM Call INNER JOIN Connect ON Call.ID = Connect.CallTableID WHERE Call.CallType in (2,3,4) AND Connect.PartyType=2 and Call.ID = ?; ";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1,3333);
ResultSet rs=ps.executeQuery();
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;

ps.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();
}
}
}


/*
SELECT Connect.CallTableID, Connect.PortID, Connect.PortName, Connect.GroupID, Connect.GroupName, Call.Extension, Call.CallType, Connect.PartyID, Connect.CtrlPartyID, Call.DialedNumber, Call.CallerID, Connect.Duration, Connect.ConnectTime FROM Call INNER JOIN Connect ON Call.ID = Connect.CallTableID WHERE Call.CallType in (2,3,4) AND Connect.PartyType=2 and Call.ID = ?; :14125/0
*/
Re:Re:Re:Re:Re:Re:Re:HXTT Access performance
HXTT Support
2007-02-25 06:35:51.0
Now you can download the latest package. Good luck:)
Re:HXTT Access performance
Kevin Shih
2007-02-25 23:13:15.0
Definite improvement in performance! And it's a lot "cleaner" looking not having to have the table in names in quotes. Thanks!

Still takes some time to complete the query and CPU usage is still quite high. I will be experimenting with adding the index on Connect.CallTableID on a copy of the database to see if that improves either the speed or the CPU usage.

As always, I appreciate your speedy support!

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