test/performance/wisc/JDBCBench.java

   1 import java.sql.*;
   2 import java.util.Random;
   3 public class JDBCBench
   4 {
   5    public static Random generator = new Random();
   6    public static int getRandom(int end)
   7    {
   8        double val = generator.nextDouble();
   9        val *=end;
  10        return (int)(val);
  11    }
  12
  13    public static int singleTuple(Connection con, boolean flag)throws Exception
  14    {
  15         PreparedStatement stmt = null ;
  16         String stmtStr;
  17         if (flag) {
  18             String buf = "SELECT unique1, unique2, stringu1, stringu2 from big1 where unique1=?;";
  19             stmtStr = buf;
  20         }else {
  21             String buf = "SELECT unique1, unique2, stringu1, stringu2 from big1 where stringu1=?;";
  22             stmtStr = buf;
  23         }
  24
  25         stmt = con.prepareStatement(stmtStr);
  26         int count=0, recordCount=0;
  27         int ret =0;
  28         long start=0, end=0, curr=0;
  29         long tot=0;
  30         ResultSet  rs;
  31         int searchVal[] = {1, 10, 100, 500, 1000, 5000 ,7500, 8000, 9000, 9999};
  32
  33         for(int i=0; i<10; i++) {
  34                 start = System.nanoTime();
  35                 if (flag)
  36                     stmt.setInt(1, searchVal[i]);
  37                 else
  38                     stmt.setString(1, "Value" +searchVal[i]);
  39                 rs = stmt.executeQuery();
  40                 recordCount=0;
  41                 if(rs.next())
  42                 {
  43                    recordCount++;
  44                 }
  45                 rs.close();
  46                 con.commit();
  47                 end = System.nanoTime();
  48                 if(recordCount != 1)
  49                 {
  50                    System.out.println("No of records for Q1: "+ recordCount );
  51                    return 0;
  52                 }
  53
  54                 curr = end - start;
  55                 tot = tot + curr;
  56                 count++;
  57         }
  58
  59         stmt.close();
  60         return (int)tot/10/1000;
  61    }
  62
  63    public static int onePerSel(Connection con) throws Exception
  64    {
  65           PreparedStatement stmt = null;
  66
  67           String stmtStr;
  68           String buf= "SELECT unique1, unique2, stringu1 from big1 where unique1 between ? and ?;";
  69           stmtStr= buf;
  70           stmt = con.prepareStatement(stmtStr);
  71           int count =0, recordCount=0;
  72           int ret =0;
  73           long start =0, end =0, curr =0;
  74           long tot =0;
  75           ResultSet rs;
  76           int searchVal[] = {1, 5, 10, 50, 100, 500, 1000, 5000 ,7500, 9500};
  77           for (int i =0 ; i< 10 ; i++) {
  78              start = System.nanoTime();
  79              stmt.setInt(1, searchVal[i]);
  80              stmt.setInt(2, searchVal[i] +99);
  81              rs = stmt.executeQuery();
  82              recordCount=0;
  83              while (rs.next())
  84              {
  85                  recordCount++;
  86              }
  87
  88              rs.close();
  89              con.commit();
  90              end = System.nanoTime();
  91
  92
  93              if (recordCount!= 100)
  94              {
  95                  System.out.println("No. of records for Q2: "+recordCount);
  96                  return 0;
  97              }
  98              curr = end-start;
  99              tot = tot + curr;
 100              count++;
 101
 102          }
 103          stmt.close();
 104          return (int)tot/10/1000;
 105    }
 106
 107 // aggregate
 108 public static int aggregate(Connection con, int val)throws Exception
 109 {
 110         PreparedStatement stmt = null;
 111         String stmtStr="";
 112         if(val==1)
 113         {
 114             String buf = "SELECT MIN(unique1) from big1;";
 115             stmtStr = buf;
 116         }
 117         else if (val ==2)
 118         {
 119             String buf = "SELECT SUM(unique1) from big1;";
 120             stmtStr = buf;
 121         }
 122         stmt = con.prepareStatement(stmtStr);
 123         int count = 0, recordCount;
 124         int ret = 0;
 125         long start = 0,end = 0,curr = 0;
 126         long tot = 0;
 127         ResultSet rs;
 128
 129         for(int i=0;i<10;i++){
 130                 start = System.nanoTime();
 131                 rs = stmt.executeQuery();
 132                 recordCount = 0;
 133                 if(rs.next())
 134                 {
 135                     recordCount++;
 136                 }
 137
 138                 rs.close();
 139                 con.commit();
 140                 end = System.nanoTime();
 141
 142                 if(recordCount !=1)
 143                 {
 144                         System.out.println("No. of records for Q4-7(" + val +" ): " + recordCount);
 145                         return 0;
 146                 }
 147                 curr = end - start;
 148                 tot = tot + curr;
 149                 count++;
 150         }
 151         stmt.close();
 152         return (int)tot/10/1000;
 153    }
 154
 155
 156   // DML statement
 157   public static int dmlstatement(Connection con, int val)throws Exception
 158   {
 159         PreparedStatement stmt = null;
 160         String stmtStr="";
 161         if(val==1)
 162         {
 163                 String buf = "insert into big1 values(?,?,0,2,0,10,50,688,1950,4950,9950,1,100,'MXXXXXXXXXXXXXXXXXXXXXXXXXGXXXXXXXXXXXXXXXXXXXXXXXXC','GXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXA','OXXXXXXXXXXXXXXXXXXXXXXXXXOXXXXXXXXXXXXXXXXXXXXXXXXO');";
 164                 stmtStr = buf;
 165         }
 166         else if(val==2)
 167         {
 168             String buf = "UPDATE big1 SET two=1 WHERE unique1=?;";
 169             stmtStr = buf;
 170         }
 171         else
 172         {
 173             String buf = "DELETE FROM big1 WHERE unique1=?;";
 174             stmtStr = buf;
 175         }
 176
 177         stmt = con.prepareStatement(stmtStr);
 178         int count = 0, recordCount=0;
 179         int ret = 0;
 180         long start = 0,end = 0,curr = 0;
 181         long tot = 0;
 182         for(int i=0; i<100; i++){
 183                 stmt.setInt(1, 10000+i);
 184                 if (val ==1) stmt.setInt(2, 10000+i);
 185                 start = System.nanoTime();
 186                 ret = stmt.executeUpdate();
 187                 if(ret!=1) return 1;
 188
 189                 con.commit();
 190                 end = System.nanoTime();
 191                 curr = end - start;
 192                 tot = tot + curr;
 193                 count++;
 194         }
 195         stmt.close();
 196         return (int)tot/100/1000;
 197   }
 198
 199 // Joining
 200 public static int joining(Connection con,int val)throws Exception
 201 {
 202         PreparedStatement stmt = null;
 203         String stmtStr= null;
 204         if(val==1)
 205         {
 206             String buf = "SELECT big1.unique1, big1.unique2, small.unique1, small.stringu1 FROM small,big1 WHERE big1.unique1=small.unique1 AND small.unique1=?;";
 207             stmtStr = buf;
 208         }
 209         else if(val==2)
 210         {
 211             String buf = "SELECT small.unique1, big1.unique1, big2.unique1, small.stringu1, big1.unique2, big2.unique2 FROM big1, big2, small WHERE small.unique1=big1.unique1 AND big1.unique1=big2.unique1 AND big1.unique1 = ?;";
 212             stmtStr=buf;
 213         }
 214
 215         stmt  = con.prepareStatement(stmtStr);
 216         int count=0,recordCount=0,ret=0;
 217         long start=0,end=0,curr=0;
 218         long tot=0;
 219         ResultSet rs;
 220         int searchVal[] = {1, 5, 10, 50, 100, 250, 500, 750, 900, 999};
 221         for(int i=0;i<10;i++)
 222         {
 223                 recordCount =0;
 224                 start = System.nanoTime();
 225                 stmt.setInt(1, searchVal[i]);
 226                 rs = stmt.executeQuery();
 227                 if(rs.next())
 228                 {
 229                     //System.out.println("Record:"+ rs.getInt(1)+ " "+ rs.getInt(2)+" " +rs.getInt(3)+ " "+ rs.getString(4));
 230                     recordCount++;
 231                 }
 232                 rs.close();
 233                 con.commit();
 234                 end=System.nanoTime();
 235                 if(recordCount!=1)
 236                 {
 237                    System.out.println("No. of records Join: "+recordCount);
 238                    return 0;
 239                 }
 240                 curr = end - start;
 241                 tot = tot + curr;
 242                 count++;
 243         }
 244         stmt.close();
 245         return (int)tot/10/1000;
 246 }
 247 //*****************************************************
 248 public static void main(String[] args)
 249
 250    {
 251      int flag=1;
 252      if (args.length == 1)
 253      {
 254          if (args[0].equals("csql")) flag=1;
 255          else if (args[0].equals("mysql")) flag=2;
 256          else {
 257              System.out.println("Valid values: csql or mysql");
 258              return;
 259          }
 260      }
 261      try
 262      {
 263           Connection con = null;
 264           if (flag == 1) {
 265               Class.forName("csql.jdbc.JdbcSqlDriver");
 266               con = DriverManager.getConnection("jdbc:csql", "root", "manager");
 267           }else if (flag == 2) {
 268               Class.forName("com.mysql.jdbc.Driver");
 269               con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");
 270           }
 271           con.setAutoCommit(false);
 272           con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
 273           Statement cStmt = con.createStatement();
 274
 275           int timeTaken[][] = new int[15][3];
 276           int ins=1,upd=2,del=3;// function parameter
 277           int min=1, sum=2;// function parameter
 278
 279
 280           timeTaken[0][0] = singleTuple(con, true);
 281           timeTaken[1][0] = onePerSel(con);
 282           timeTaken[2][0] = aggregate(con,min);
 283           timeTaken[3][0] = aggregate(con,sum);
 284           timeTaken[4][0] = dmlstatement(con,ins);
 285           timeTaken[5][0] = dmlstatement(con,upd);
 286           timeTaken[6][0] = dmlstatement(con,del);
 287           timeTaken[7][0] = joining(con,1);
 288           timeTaken[8][0] = joining(con,2);
 289           timeTaken[9][0] = singleTuple(con, false);
 290           System.out.println("Finished no index queries");
 291
 292           //create index
 293           try{
 294             if (flag ==1) {
 295               cStmt.execute("CREATE INDEX idx1 ON big1(unique1) HASH");
 296               cStmt.close();
 297               cStmt.execute("CREATE INDEX idx2 ON big2(unique1) HASH");
 298               cStmt.close();
 299               cStmt.execute("CREATE INDEX idx3 ON small(unique1) HASH");
 300               cStmt.close();
 301               cStmt.execute("CREATE INDEX idx4 ON big1(stringu1) HASH");
 302               cStmt.close();
 303               con.commit();
 304             }else if (flag ==2) {
 305               cStmt.execute("CREATE INDEX mysqld1 USING HASH ON big1(unique1)");
 306               cStmt.execute("CREATE INDEX mysqld2 USING HASH ON big2(unique1)");
 307               cStmt.execute("CREATE INDEX mysqld3 USING HASH ON small(unique1)");
 308               cStmt.execute("CREATE INDEX mysqld4 USING HASH ON big1(stringu1)");
 309               con.commit();
 310             }
 311           }catch(Exception e ){e.printStackTrace(); System.out.println("Error creating index");}
 312           timeTaken[0][1] = singleTuple(con, true);
 313           timeTaken[1][1] = 0;
 314           timeTaken[2][1] = 0;
 315           timeTaken[3][1] = 0;
 316           timeTaken[4][1] = dmlstatement(con,ins);
 317           timeTaken[5][1] = dmlstatement(con,upd);
 318           timeTaken[6][1] = dmlstatement(con,del);
 319           timeTaken[7][1] = joining(con,1);
 320           timeTaken[8][1] = joining(con,2);
 321           timeTaken[9][1] = singleTuple(con, false);
 322           System.out.println("Finished hash index queries");
 323
 324
 325           try{
 326             if (flag ==1) {
 327               cStmt.execute("DROP INDEX idx1");
 328               cStmt.close();
 329               cStmt.execute("DROP INDEX idx2");
 330               cStmt.close();
 331               cStmt.execute("DROP INDEX idx3");
 332               cStmt.close();
 333               cStmt.execute("DROP INDEX idx4");
 334               cStmt.close();
 335               cStmt.execute("CREATE INDEX idx5 ON big1(unique1) TREE");
 336               cStmt.execute("CREATE INDEX idx6 ON big2(unique1) TREE");
 337               cStmt.execute("CREATE INDEX idx7 ON small(unique1) TREE");
 338               cStmt.execute("CREATE INDEX idx8 ON big1(stringu1) TREE");
 339               con.commit();
 340             }else if (flag ==2) {
 341               cStmt.execute("DROP INDEX mysqld1 on big1");
 342               cStmt.execute("DROP INDEX mysqld2 on big2");
 343               cStmt.execute("DROP INDEX mysqld3 on small");
 344               cStmt.execute("DROP INDEX mysqld4 on big1;");
 345               cStmt.execute("CREATE INDEX mysqld5 USING BTREE ON big1(unique1)");
 346               cStmt.execute("CREATE INDEX mysqld6 USING BTREE ON big2(unique1)");
 347               cStmt.execute("CREATE INDEX mysqld7 USING BTREE ON small(unique1)");
 348               cStmt.execute("CREATE INDEX mysqld8 USING BTREE ON big1(stringu1)");
 349               con.commit();
 350             }
 351           }catch(Exception e ){e.printStackTrace(); System.out.println("Error creating index");}
 352
 353           timeTaken[0][2] = singleTuple(con, true);
 354           timeTaken[1][2] = onePerSel(con);
 355           timeTaken[2][2] = aggregate(con,min);
 356           timeTaken[3][2] = 0;
 357           timeTaken[4][2] = dmlstatement(con,ins);
 358           timeTaken[5][2] = dmlstatement(con,upd);
 359           timeTaken[6][2] = dmlstatement(con,del);
 360           timeTaken[7][2] = joining(con,1);
 361           timeTaken[8][2] = joining(con,2);
 362           timeTaken[9][2] = singleTuple(con, false);
 363           System.out.println("Finished tree index queries");
 364
 365
 366
 367           try{
 368             if (flag ==1) {
 369               cStmt.execute("DROP INDEX idx5;");
 370               cStmt.close();
 371               cStmt.execute("DROP INDEX idx6;");
 372               cStmt.close();
 373               cStmt.execute("DROP INDEX idx7;");
 374               cStmt.close();
 375               cStmt.execute("DROP INDEX idx8;");
 376               cStmt.close();
 377               con.commit();
 378             } else if (flag ==2) {
 379               cStmt.execute("DROP INDEX mysqld5 on big1");
 380               cStmt.execute("DROP INDEX mysqld6 on big2");
 381               cStmt.execute("DROP INDEX mysqld7 on small");
 382               cStmt.execute("DROP INDEX mysqld8 on big1");
 383               con.commit();
 384             }
 385           }catch(Exception e ){System.out.println("Error dropping indexes");}
 386
 387           System.out.println("Wisconsin Benchmark Report:");
 388
 389           System.out.println("**********************************************************");
 390           System.out.println(" Statement       \t NoIndex\tHash \tTree");
 391           System.out.println("**********************************************************");
 392           System.out.println(" SelectInt 1     \t "+timeTaken[0][0] +" \t \t"+ timeTaken[0][1]+ "   \t"+ timeTaken[0][2]);
 393           System.out.println(" SelectStr 1     \t "+timeTaken[9][0] +" \t \t"+ timeTaken[9][1]+ "   \t"+ timeTaken[9][2]);
 394           System.out.println(" 1% Sel          \t "+timeTaken[1][0] +" \t \t-   \t"+ timeTaken[1][2]);
 395
 396           System.out.println(" Min All         \t "+timeTaken[2][0] +" \t \t-   \t"+ timeTaken[2][2]);
 397           System.out.println(" Sum All         \t "+timeTaken[3][0] +" \t \t-   \t-");
 398           System.out.println(" Insert 1        \t "+timeTaken[4][0] +" \t \t"+ timeTaken[4][1]+ "   \t"+ timeTaken[4][2]);
 399           System.out.println(" Update 1       \t "+timeTaken[5][0] +" \t \t"+ timeTaken[5][1]+ "   \t"+ timeTaken[5][2]);
 400           System.out.println(" Delete 1        \t "+timeTaken[6][0] +" \t \t"+ timeTaken[6][1]+ "   \t"+ timeTaken[6][2]);
 401           System.out.println(" Join(10K*1K)1  \t "+timeTaken[7][0] +" \t \t"+ timeTaken[7][1]+ "\t"+ timeTaken[7][2]);
 402           System.out.println(" Join(10K*10K*1K)1\t "+timeTaken[8][0] +"\t  \t"+ timeTaken[8][1]+ "\t"+ timeTaken[8][2]);
 403           System.out.println("**********************************************************");
 404
 405           con.close();
 406         }
 407         catch(Exception e) {
 408             System.out.println("Exception in Test: "+e);
 409             e.printStackTrace();
 410         }
 411
 412     }
 413 }