/* 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();

        }

    }

}