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         {
  19                 String buf = " SELECT * from big1 where unique2=?;";
  20                 stmtStr =  buf;
  21         }
  22         else
  23         {
  24                 String buf = "SELECT * from big1 where unique1=?;";
  25                 stmtStr = buf;
  26         }
  27
  28         stmt = con.prepareStatement(stmtStr);
  29         int count=0, recordCount=0;
  30         int ret =0;
  31         long start=0, end=0, curr=0;
  32         long tot=0;
  33         ResultSet  rs;
  34
  35
  36         for(int i=0; i<=100; i++) {
  37
  38                 stmt.setInt(1,i);
  39                 start = System.nanoTime();
  40                 rs = stmt.executeQuery();
  41                 recordCount=0;
  42                 while(rs.next())
  43                 {
  44                    recordCount++;
  45                 }
  46                 rs.close();
  47                 con.commit();
  48                 end = System.nanoTime();
  49                 if(recordCount != 1)
  50                 {
  51                    System.out.println("No of records for Q1: "+ recordCount );
  52                    return 0;
  53                 }
  54
  55                 curr = end - start;
  56                 tot = tot + curr;
  57                 count++;
  58         }
  59
  60         stmt.close();
  61         return (int)tot/100/1000;
  62    }
  63
  64    public static int onePerSel(Connection con, boolean flag) throws Exception
  65    {
  66           PreparedStatement stmt = null;
  67           int searchVal = getRandom(9999 - 100);
  68           int searchValEnd = searchVal+ 99;
  69
  70
  71           String stmtStr;
  72           if (flag) {
  73               String buf= "SELECT * from big1 where unique2 between "+
  74                           searchVal + " and "+ searchValEnd+ ";";
  75               stmtStr= buf;
  76           }else{
  77               String buf= "SELECT * from big1 where unique1 between "+
  78                           searchVal + " and "+ searchValEnd+ ";";
  79               stmtStr= buf;
  80           }
  81           stmt = con.prepareStatement(stmtStr);
  82           int count =0, recordCount=0;
  83           int ret =0;
  84           long start =0, end =0, curr =0;
  85           long tot =0;
  86           ResultSet rs;
  87           for (int i =0 ; i<= 100 ; i++) {
  88              start = System.nanoTime();
  89
  90              rs = stmt.executeQuery();
  91              recordCount=0;
  92              while (rs.next())
  93              {
  94                  recordCount++;
  95              }
  96
  97              rs.close();
  98              con.commit();
  99              end = System.nanoTime();
 100
 101
 102              if (recordCount!= 100)
 103              {
 104                  System.out.println("No. of records for Q2: "+recordCount);
 105                  return 0;
 106              }
 107              curr = end-start;
 108              tot = tot + curr;
 109              count++;
 110
 111          }
 112          stmt.close();
 113          return (int)tot/100/1000;
 114    }
 115
 116 // aggregate
 117 public static int aggregate(Connection con, int val, boolean flag)throws Exception
 118 {
 119         PreparedStatement stmt = null;
 120         String stmtStr="";
 121         if(val==1)
 122         {
 123             if (flag) {
 124                 String buf = "SELECT MIN(unique2) from big1;";
 125                 stmtStr = buf;
 126             }else {
 127                 String buf = "SELECT MIN(unique1) from big1;";
 128                 stmtStr = buf;
 129             }
 130         }
 131         else if (val ==2)
 132         {
 133             if (flag) {
 134                 String buf = "SELECT SUM(unique2) from big1;";
 135                 stmtStr = buf;
 136             }else {
 137                 String buf = "SELECT SUM(unique1) from big1;";
 138                 stmtStr = buf;
 139             }
 140         }
 141         else if(val==3)
 142         {
 143             if (flag) {
 144                 String buf = "SELECT MIN(unique2) FROM big1 GROUP BY onepercent;";
 145                 stmtStr = buf;
 146             }else{
 147                 String buf = "SELECT MIN(unique1) FROM big1 GROUP BY onepercent;";
 148                 stmtStr = buf;
 149             }
 150         }
 151         else
 152         {
 153             if (flag) {
 154                 String buf = "SELECT SUM(unique2) FROM big1 GROUP BY onepercent;";
 155                 stmtStr = buf;
 156             }else {
 157                 String buf = "SELECT SUM(unique1) FROM big1 GROUP BY onepercent;";
 158                 stmtStr = buf;
 159             }
 160         }
 161
 162         stmt = con.prepareStatement(stmtStr);
 163         int count = 0, recordCount;
 164         int ret = 0;
 165         long start = 0,end = 0,curr = 0;
 166         long tot = 0;
 167         ResultSet rs;
 168
 169         for(int i=0;i<10;i++){
 170                 start = System.nanoTime();
 171                 rs = stmt.executeQuery();
 172                 recordCount = 0;
 173                 while(rs.next())
 174                 {
 175                         recordCount++;
 176                 }
 177
 178                 rs.close();
 179                 con.commit();
 180                 end = System.nanoTime();
 181
 182                 if((val==1 || val ==2) && recordCount !=1)
 183                 {
 184                         System.out.println("No. of records for Q4-7(" + val +" ): " + recordCount);
 185                         return 0;
 186                 }
 187                 if((val==4 || val==3) && recordCount !=100)
 188                 {
 189                         System.out.println("No. of records for Q4-7("+ val + "): " + recordCount);
 190                         return 0;
 191                 }
 192                 curr = end - start;
 193                 tot = tot + curr;
 194                 count++;
 195         }
 196         stmt.close();
 197         return (int)tot/10/1000;
 198    }
 199
 200
 201   // DML statement
 202   public static int dmlstatement(Connection con, int val, boolean flag)throws Exception
 203   {
 204         PreparedStatement stmt = null;
 205         String stmtStr="";
 206         if(val==1)
 207         {
 208                 String buf = "insert into big1 values(?,?,0,2,0,10,50,688,1950,4950,9950,1,100,'MXXXXXXXXXXXXXXXXXXXXXXXXXGXXXXXXXXXXXXXXXXXXXXXXXXC','GXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXA','OXXXXXXXXXXXXXXXXXXXXXXXXXOXXXXXXXXXXXXXXXXXXXXXXXXO');";
 209                 stmtStr = buf;
 210         }
 211         else if(val==2)
 212         {
 213            if(flag) {
 214                 String buf = "UPDATE big1 SET two=1 WHERE unique2=?;";
 215                 stmtStr = buf;
 216            }else {
 217                 String buf = "UPDATE big1 SET two=1 WHERE unique1=?;";
 218                 stmtStr = buf;
 219            }
 220         }
 221         else
 222         {
 223            if (flag) {
 224                 String buf = "DELETE FROM big1 WHERE unique2=?;";
 225                 stmtStr = buf;
 226            }else {
 227                 String buf = "DELETE FROM big1 WHERE unique1=?;";
 228                 stmtStr = buf;
 229            }
 230         }
 231
 232         stmt = con.prepareStatement(stmtStr);
 233         int count = 0, recordCount=0;
 234         int ret = 0;
 235         long start = 0,end = 0,curr = 0;
 236         long tot = 0;
 237         for(int i=0; i<100; i++){
 238                 stmt.setInt(1, 10000+i);
 239                 if (val ==1) stmt.setInt(2, 10000+i);
 240                 start = System.nanoTime();
 241                 ret = stmt.executeUpdate();
 242                 if(ret!=1) return 1;
 243
 244                 con.commit();
 245                 end = System.nanoTime();
 246                 curr = end - start;
 247                 tot = tot + curr;
 248                 count++;
 249         }
 250         stmt.close();
 251         return (int)tot/100/1000;
 252   }
 253
 254 // Joining
 255 public static int joining(Connection con,int val, boolean flag)throws Exception
 256 {
 257         PreparedStatement stmt = null;
 258         String stmtStr;
 259         if(val==1)
 260         {
 261            if (flag) {
 262                 String buf = "SELECT big1.unique2, big2.unique2 FROM big2,big1 WHERE big1.unique2=big2.unique2 AND big2.unique2=1000;";
 263                 stmtStr = buf;
 264            }else{
 265                 String buf = "SELECT big1.unique1, big2.unique1 FROM big2,big1 WHERE big1.unique1=big2.unique1 AND big2.unique1=1000;";
 266                 stmtStr = buf;
 267            }
 268         }
 269         else if(val==2)
 270         {
 271            if (flag) {
 272                 String buf = "SELECT big1.unique2, big2.unique2 FROM big2,big1 WHERE big1.unique2=big2.unique2 AND big2.unique2>1000;";
 273                 stmtStr = buf;
 274            }else{
 275                 String buf = "SELECT big1.unique1, big2.unique1 FROM big2,big1 WHERE big1.unique1=big2.unique1 AND big2.unique1>1000;";
 276                 stmtStr = buf;
 277            }
 278         }
 279         else if(val==11)
 280         {
 281                 String buf = "SELECT * FROM small,big1 WHERE small.unique2=big1.unique2 AND big1.unique2=big2.unique2 AND big1.unique2<1000;";
 282                 stmtStr=buf;
 283         }
 284         else if(val==15)
 285         {
 286                 String buf = "SELECT * FROM big1,big2 WHERE (big1.unique1=big2.unique1) AND (big1.unique2<1000);";
 287                 stmtStr=buf;
 288         }
 289         else if(val==16)
 290         {
 291                 String buf = "SELECT * FROM big1,bprime WHERE big1.unique1=bprime.unique1;";
 292                 stmtStr=buf;
 293         }
 294         else
 295         {
 296                 String buf = "SELECT * FROM small,big1 WHERE (small.unique1=big1.unique1) AND (big1.unique1=big2.unique1) AND (big1.unique1 < 1000);";
 297                 stmtStr=buf;
 298         }
 299
 300         stmt  = con.prepareStatement(stmtStr);
 301         int count=0,recordCount=0,ret=0;
 302         long start=0,end=0,curr=0;
 303         long tot=0;
 304         ResultSet rs;
 305         //for(int i=0;i<=100;i++)
 306         {
 307                 start = System.nanoTime();
 308                 rs = stmt.executeQuery();
 309                 while(rs.next())
 310                 {
 311                     //System.out.println("Record: "+ rs.getInt(1)+ " "+ rs.getInt(2));
 312                     recordCount++;
 313                 }
 314                 rs.close();
 315                 con.commit();
 316                 end=System.nanoTime();
 317                 if(recordCount!=1)
 318                 {
 319                 System.out.println("No. of records Join: "+recordCount);
 320                 return 0;
 321                 }
 322                 curr = end - start;
 323                 tot = tot + curr;
 324                 count++;
 325         }
 326         stmt.close();
 327         return (int)tot/1000;
 328 }
 329
 330 // projection
 331 /*public static  int projection(Connection con, boolean flag)throws Exception
 332 {
 333         PreparedStatement  stmt = null;
 334
 335         String stmtStr;
 336         if(flag)
 337         {
 338            String buf = "SELECT DISTINCT two,four,ten,twenty,onepercent,string4 from big1;";
 339            stmtStr = buf;
 340         }
 341         else
 342         {
 343             String buf = "SELECT DISTINCT two,four,ten,twenty,onepercent,tenpercent,twentypercent,fiftypercent,unique3,evenonepercent,oddonepercent,stringu1,stringu2,string4 FROM big1;";
 344             stmtStr = buf;
 345         }
 346                 stmt = con.prepareStatement(stmtStr);
 347                 int count = 0, recordCount;
 348                 int ret = 0;
 349                 long start = 0, end = 0, curr = 0;
 350                 long tot =0;
 351                 ResultSet rs;
 352                 for(int i=0 ; i<=100 ; i++)
 353                 {
 354                         start = System.nanoTime();
 355                         rs = stmt.executeQuery();
 356                         recordCount = 0;
 357                         while(rs.next())
 358                         {
 359                                 recordCount++;
 360                         }
 361
 362                         rs.close();
 363                         con.commit();
 364                         end = System.nanoTime();
 365
 366                         if((flag && recordCount !=1000) || (!flag && recordCount !=10000))
 367                         {
 368                            System.out.println("No. of records returned is around for Q2" + recordCount);
 369
 370                           return 0;
 371                         }
 372
 373                         curr = end - start;
 374                         tot = tot + curr;
 375                         count++;
 376                 }
 377
 378                 stmt.close();
 379                 return (int)tot/100/1000;
 380
 381 }*/
 382 // second query
 383 public static  int tenPerSel(Connection con, boolean flag)throws Exception
 384 {
 385         PreparedStatement  stmt = null;
 386         int searchVal = getRandom(9999 - 1000);
 387         int searchValEnd = searchVal + 999;
 388
 389         String stmtStr;
 390         if(flag)
 391         {
 392            String buf = "SELECT * from big1 where unique2 between "+ searchVal + " and "+ searchValEnd + ";";
 393            stmtStr = buf;
 394         }
 395         else
 396         {
 397             String buf = "SELECT * from big1 where unique1 between "+searchVal +" and "+ searchValEnd + ";";
 398                 stmtStr = buf;
 399         }
 400                 stmt = con.prepareStatement(stmtStr);
 401                 int count = 0, recordCount;
 402                 int ret = 0;
 403                 long start = 0, end = 0, curr = 0;
 404                 long tot =0;
 405                 ResultSet rs;
 406                 for(int i=0 ; i<=100 ; i++)
 407                 {
 408                         start = System.nanoTime();
 409                         rs = stmt.executeQuery();
 410                         recordCount = 0;
 411                         while(rs.next())
 412                         {
 413                                 recordCount++;
 414                         }
 415
 416                         rs.close();
 417                         con.commit();
 418                         end = System.nanoTime();
 419
 420
 421                         if(recordCount != 1000)
 422                         {
 423                           System.out.println("No. of records for Q3: " + recordCount);
 424                           return 0;
 425                         }
 426
 427                         curr = end - start;
 428                         tot = tot + curr;
 429                         count++;
 430                 }
 431
 432                 stmt.close();
 433                 return (int)tot/100/1000;
 434   }
 435
 436
 437 //*****************************************************
 438 public static void main(String[] args)
 439
 440    {
 441      int flag=1;
 442      if (args.length == 1)
 443      {
 444          if (args[0].equals("csql")) flag=1;
 445          else if (args[0].equals("mysql")) flag=2;
 446          else {
 447              System.out.println("Valid values: csql or mysql");
 448              return;
 449          }
 450      }
 451      try
 452      {
 453           Connection con = null;
 454           if (flag == 1) {
 455               Class.forName("csql.jdbc.JdbcSqlDriver");
 456               con = DriverManager.getConnection("jdbc:csql", "root", "manager");
 457           }else if (flag == 2) {
 458               Class.forName("com.mysql.jdbc.Driver");
 459               con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");
 460           }
 461           con.setAutoCommit(false);
 462           con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
 463           Statement cStmt = con.createStatement();
 464
 465           int timeTaken[][] = new int[15][3];
 466           int ins=1,upd=2,del=3;// function parameter
 467           int min=1, sum=2, ming=3, sumg=4;// function parameter
 468
 469
 470           timeTaken[0][0] = singleTuple(con,true);
 471           timeTaken[1][0] = onePerSel(con, true);
 472           timeTaken[2][0] = tenPerSel(con,true);
 473           timeTaken[3][0] = aggregate(con,min, true);
 474           timeTaken[4][0] = aggregate(con,sum, true);
 475           timeTaken[5][0] = aggregate(con,ming, true);
 476           timeTaken[6][0] = aggregate(con,sumg, true);
 477
 478           timeTaken[7][0] = dmlstatement(con,ins, true);
 479           //timeTaken[8][0] = dmlstatement(con,upd, true);
 480           timeTaken[9][0] = dmlstatement(con,del, true);
 481
 482           timeTaken[10][0] = joining(con,1, true);
 483           //timeTaken[11][0] = joining(con,2, true);
 484           System.out.println("Finished no index queries");
 485           //create index
 486           try{
 487             if (flag ==1) {
 488               cStmt.execute("CREATE INDEX idx4 ON big1(unique2) HASH");
 489               cStmt.close();
 490               cStmt.execute("CREATE INDEX idx3 ON big1(unique1) TREE");
 491               cStmt.close();
 492               cStmt.execute("CREATE INDEX idx6 ON big2(unique2) HASH");
 493               cStmt.close();
 494               cStmt.execute("CREATE INDEX idx5 ON big2(unique1) TREE");
 495               cStmt.close();
 496               con.commit();
 497             }else if (flag ==2) {
 498               cStmt.execute("CREATE INDEX mysqlc1 USING HASH ON big1(unique2)");
 499               cStmt.execute("CREATE INDEX mysqlc2 USING BTREE ON big1(unique1)");
 500               cStmt.execute("CREATE INDEX mysqlc3 USING HASH ON big2(unique2)");
 501               cStmt.execute("CREATE INDEX mysqlc4 USING BTREE ON big2(unique1)");
 502               cStmt.close();
 503               con.commit();
 504             }
 505           }catch(Exception e ){e.printStackTrace(); System.out.println("Error creating index");}
 506
 507           timeTaken[0][1] = singleTuple(con,true);
 508           timeTaken[1][1] = onePerSel(con, true);
 509           timeTaken[2][1] = tenPerSel(con,true);
 510           timeTaken[3][1] = aggregate(con,min, true);
 511           timeTaken[4][1] = aggregate(con,sum, true);
 512           timeTaken[5][1] = aggregate(con,ming, true);
 513           timeTaken[6][1] = aggregate(con,sumg, true);
 514           timeTaken[7][1] = dmlstatement(con,ins, true);
 515           //timeTaken[8][1] = dmlstatement(con,upd, true);
 516           timeTaken[9][1] = dmlstatement(con,del, true);
 517           timeTaken[10][1] = joining(con,1, true);
 518           //timeTaken[11][1] = joining(con,2, true);
 519           System.out.println("Finished hash index queries");
 520
 521           timeTaken[0][2] = singleTuple(con,false);
 522           timeTaken[1][2] = onePerSel(con, false);
 523           timeTaken[2][2] = tenPerSel(con,false);
 524           timeTaken[3][2] = aggregate(con,min, false);
 525           timeTaken[4][2] = aggregate(con,sum, false);
 526           timeTaken[5][2] = aggregate(con,ming, false);
 527           timeTaken[6][2] = aggregate(con,sumg, false);
 528           timeTaken[7][2] = dmlstatement(con,ins, false);
 529           //timeTaken[8][2] = dmlstatement(con,upd, false);
 530           timeTaken[9][2] = dmlstatement(con,del, false);
 531           timeTaken[10][2] = joining(con,1, false);
 532           //timeTaken[11][2] = joining(con,2, false);
 533           System.out.println("Finished tree index queries");
 534
 535
 536           int join1=1;
 537           //int q18 = projection(con,true);
 538           //System.out.println("18\t1%Proj Sel\t-\tY\tY\t"+q18);
 539
 540           //int q19 = projection(con,false);
 541           //System.out.println("19\t100%Proj Sel\t-\tY\tY\t"+q19);*/
 542
 543           try{
 544             if (flag ==1) {
 545               cStmt.execute("DROP INDEX idx4;");
 546               cStmt.close();
 547               cStmt.execute("DROP INDEX idx3;");
 548               cStmt.close();
 549               cStmt.execute("DROP INDEX idx6;");
 550               cStmt.close();
 551               cStmt.execute("DROP INDEX idx5;");
 552               cStmt.close();
 553               con.commit();
 554             } else if (flag ==2) {
 555               cStmt.execute("DROP INDEX mysqlc1 on big1");
 556               cStmt.close();
 557               cStmt.execute("DROP INDEX mysqlc2 on big1");
 558               cStmt.close();
 559               cStmt.execute("DROP INDEX mysqlc3 on big2");
 560               cStmt.close();
 561               cStmt.execute("DROP INDEX mysqlc4 on big2");
 562               cStmt.close();
 563               con.commit();
 564             }
 565           }catch(Exception e ){System.out.println("Error dropping indexes");}
 566           System.out.println("Wisconsin Benchmark Report:");
 567
 568           System.out.println("**********************************************************");
 569           System.out.println(" Statement       \t NoIndex\tHash \tTree");
 570           System.out.println("**********************************************************");
 571           System.out.println(" 1 Tuple         \t "+timeTaken[0][0] +" \t \t"+ timeTaken[0][1]+ "   \t"+ timeTaken[0][2]);
 572           System.out.println(" 1% Sel          \t "+timeTaken[1][0] +" \t \t"+ timeTaken[1][1]+ "   \t"+ timeTaken[1][2]);
 573           System.out.println(" 10% Sel         \t "+timeTaken[2][0] +" \t \t"+ timeTaken[2][1]+ "   \t"+ timeTaken[2][2]);
 574
 575           System.out.println(" Min All         \t "+timeTaken[3][0] +" \t \t"+ timeTaken[3][1]+ "   \t"+ timeTaken[3][2]);
 576           System.out.println(" Sum All         \t "+timeTaken[4][0] +" \t \t"+ timeTaken[4][1]+ "   \t"+ timeTaken[4][2]);
 577           System.out.println(" Min Grp(100)    \t "+timeTaken[5][0] +" \t \t"+ timeTaken[5][1]+ "\t"+ timeTaken[5][2]);
 578           System.out.println(" Sum Grp(100)    \t "+timeTaken[6][0] +" \t \t"+ timeTaken[6][1]+ "\t"+ timeTaken[6][2]);
 579
 580           System.out.println(" Insert 1        \t "+timeTaken[7][0] +" \t \t"+ timeTaken[7][1]+ "   \t"+ timeTaken[7][2]);
 581           System.out.println(" Update 1       \t "+timeTaken[8][0] +" \t \t"+ timeTaken[8][1]+ "   \t"+ timeTaken[8][2]);
 582           System.out.println(" Delete 1        \t "+timeTaken[9][0] +" \t \t"+ timeTaken[9][1]+ "   \t"+ timeTaken[9][2]);
 583           System.out.println(" Join(10K*10K)1  \t "+timeTaken[10][0] +" \t \t"+ timeTaken[10][1]+ "\t"+ timeTaken[10][2]);
 584           System.out.println(" Join(10K*10K)1K \t "+timeTaken[11][0] +" \t \t"+ timeTaken[11][1]+ "\t"+ timeTaken[11][2]);
 585           System.out.println("**********************************************************");
 586
 587           con.close();
 588         }
 589         catch(Exception e) {
 590             System.out.println("Exception in Test: "+e);
 591             e.printStackTrace();
 592         }
 593
 594     }
 595 }