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 > ?;
|
|
Create index on "Call".ID. You can create index for "Call".CallType, and "Connect".PartyType too.
|
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?
|
|
Whether Call has only one primary index for ID column, and hasn't other index? Whether Connect hasn't any index?
|
|
Both Call and Connect only have the indices of the PK fields, Call.ID and Connect.ID. No other indices exist for either table.
|
|
We're writing code to recur your issue. Thanks for your patience.
|
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
*/
|
|
Now you can download the latest package. Good luck:)
|
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!
|
Main
webmaster@hxtt.com