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