Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  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 Text(CSV)
Sketch Join Performance
Martin Ross
2006-05-11 14:09:56.0
Query on two CSV tables (each under two megs in size)

select * from encounters this_
left outer join ClinicalLogicDoctors clinicallo2_
on this_.Doctor=clinicallo2_.uniqueDoctorID
inner join patients clinicallo3_
on this_.patientID=clinicallo3_.patientID
where this_.visitdate>?

This performs VERY VERY poorly... as in 10 rows/sec poorly.

This is urgent as usual :)

Thanks,

Martin
Re:Sketch Join Performance
HXTT Support
2006-05-11 22:11:21.0
Please download the latest package. Thanks for your requirement.


/* A simple test for JOIN on three tables(encounters 200000 rows(11524KB), ClinicalLogicDoctors 50000 rows(2833KB), patients 50000 rows (2833KB))
Tested with AMD2500 on 2006-05-12

select * from encounters this_ left outer join ClinicalLogicDoctors clinicallo2_ on this_.Doctor=clinicallo2_.uniqueDoctorID inner join patients clinicallo3_ on this_.patientID=clinicallo3_.patientID where this_.visitdate>?
Spent time/row count:76531ms/84120
Spent time/row count:60500ms/145460
Spent time/row count:35532ms/191800
Spent time/row count:5062ms/32280
Spent time/row count:11000ms/73760
Spent time/row count:12344ms/84120
Spent time/row count:22125ms/145460
Spent time/row count:29984ms/191800
Spent time/row count:5031ms/32280
Spent time/row count:10875ms/73760

sql:select * from encounters this_ left outer join ClinicalLogicDoctors clinicallo2_ on this_.Doctor=clinicallo2_.uniqueDoctorID inner join patients clinicallo3_ on this_.patientID=clinicallo3_.patientID where this_.visitdate>?
Spent time/row count:90750ms/84120
Spent time/row count:71672ms/145460
Spent time/row count:32875ms/191800
Spent time/row count:4937ms/32280
Spent time/row count:10984ms/73760
Spent time/row count:12469ms/84120
Spent time/row count:22156ms/145460
Spent time/row count:28860ms/191800
Spent time/row count:4890ms/32280
Spent time/row count:10985ms/73760

*/
import java.sql.*;
import java.util.Properties;

public class testCSV5{
public static void main(String argv[]){
try{
Class.forName("com.hxtt.sql.text.TextDriver");


String url="jdbc:csv:////d:/test";

Properties properties=new Properties();

properties.setProperty("_CSV_Header", "true");

Connection con = DriverManager.getConnection(url,properties);

boolean initDataBaseFlag=false;
if(initDataBaseFlag){
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
String sql="create table encounters (Doctor char(10),patientID char(10),visitdate date, notsure varchar (7), lastName varchar (20), firstName varchar (11), gender varchar (1), dob varchar (8), HealthCardNum varchar (12), healthCardversionNum varchar (2), number varchar (8), comments varchar (20), streetAdressLine1 varchar (30), "
+"city varchar (15), province varchar (3), postalCode varchar (6), homePhone varchar (10), workPhone varchar (10), secondAddress varchar (81), dunno varchar (49), dunno1 varchar (4), due varchar (8), due2 varchar (8), patKey2 varchar (3), morepadding varchar (16), flag varchar (1), eor varchar (35),"
+"tempPK char (14)); "
+"create table ClinicalLogicDoctors (uniqueDoctorID char(10),name char(20),notsure varchar (7), lastName varchar (20), firstName varchar (11), gender varchar (1), dob varchar (8), HealthCardNum varchar (12), healthCardversionNum varchar (2), number varchar (8), comments varchar (20), streetAdressLine1 varchar (30), "
+"city varchar (15), province varchar (3), postalCode varchar (6), homePhone varchar (10), workPhone varchar (10), secondAddress varchar (81), dunno varchar (49), dunno1 varchar (4), due varchar (8), due2 varchar (8), patKey2 varchar (3), morepadding varchar (16), flag varchar (1), eor varchar (35),"
+"tempPK char (14));"
+"create table patients (patientID char(10),name char(20), notsure varchar (7), lastName varchar (20), firstName varchar (11), gender varchar (1), dob varchar (8), HealthCardNum varchar (12), healthCardversionNum varchar (2), number varchar (8), comments varchar (20), streetAdressLine1 varchar (30), "
+"city varchar (15), province varchar (3), postalCode varchar (6), homePhone varchar (10), workPhone varchar (10), secondAddress varchar (81), dunno varchar (49), dunno1 varchar (4), due varchar (8), due2 varchar (8), patKey2 varchar (3), morepadding varchar (16), flag varchar (1), eor varchar (35),"
+"tempPK char (14));"
;
stmt.executeUpdate(sql);
stmt.close();

sql="insert into encounters (Doctor,patientID,visitdate) values(?,?,?);";
PreparedStatement pstmt = con.prepareStatement(sql);
for(int i=0;i<200000;i++){
pstmt.setInt(1,i/2);
pstmt.setInt(2,i/10);
pstmt.setDate(3,new Date(System.currentTimeMillis()-1000000*(i%10000)));
pstmt.executeUpdate();
}
pstmt.close();

sql="insert into ClinicalLogicDoctors (uniqueDoctorID,name) values(?,?);";
pstmt = con.prepareStatement(sql);
for(int i=0;i<50000;i++){
pstmt.setInt(1,i);
pstmt.setString(2,"doc"+i);
pstmt.executeUpdate();
}
pstmt.close();

sql="insert into patients (patientID,name) values(?,?);";
pstmt = con.prepareStatement(sql);
for(int i=0;i<50000;i++){
pstmt.setInt(1,i);
pstmt.setString(2,"patient"+i);
pstmt.executeUpdate();
}
pstmt.close();
}


String sql="select * from encounters this_ left outer join ClinicalLogicDoctors clinicallo2_ on this_.Doctor=clinicallo2_.uniqueDoctorID inner join patients clinicallo3_ on this_.patientID=clinicallo3_.patientID where this_.visitdate>?";
PreparedStatement pstmt = con.prepareStatement(sql);
System.out.println("sql:"+sql);
for(int i=0;i<10000;i+=1000){
pstmt.setDate(1,new Date(System.currentTimeMillis()-1000000*(i%5000)));
long currentTime=System.currentTimeMillis();
ResultSet rs = pstmt.executeQuery();
rs.beforeFirst();
int rowCount=0;
while (rs.next()) {
rs.getObject(1);
rowCount++;
}
currentTime=System.currentTimeMillis()-currentTime;

rs.close();
System.out.println("Spent time/row count:" +currentTime+"ms/"+rowCount );
}

pstmt.close();

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

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