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 wrond 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,10000,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 unique1=10001 WHERE unique1=10010;";
 192                 stmtStr = buf;
 193         }
 194         else
 195         {
 196                 String buf = "DELETE FROM big1 WHERE unique1=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 * FROM big1,big2 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                                 recordCount++;
 283                         }
 284                         rs.close();
 285                         con.commit();
 286                         end=System.nanoTime();
 287                         if(recordCount!=1000)
 288                         {
 289                                 System.out.println("No. of records returned is around for Q2"+recordCount);
 290                                 return 0;
 291                         }
 292                         curr = end - start;
 293                         tot = tot + curr;
 294                         count++;
 295                 }
 296                 stmt.close();
 297                 return (int)tot/100/1000;
 298 }*/
 299
 300 // projection
 301 /*public static  int projection(Connection con, boolean flag)throws Exception
 302 {
 303         PreparedStatement  stmt = null;
 304
 305         String stmtStr;
 306         if(flag)
 307         {
 308            String buf = "SELECT DISTINCT two,four,ten,twenty,onepercent,string4 from big1;";
 309            stmtStr = buf;
 310         }
 311         else
 312         {
 313             String buf = "SELECT DISTINCT two,four,ten,twenty,onepercent,tenpercent,twentypercent,fiftypercent,unique3,evenonepercent,oddonepercent,stringu1,stringu2,string4 FROM big1;";
 314             stmtStr = buf;
 315         }
 316                 stmt = con.prepareStatement(stmtStr);
 317                 int count = 0, recordCount;
 318                 int ret = 0;
 319                 long start = 0, end = 0, curr = 0;
 320                 long tot =0;
 321                 ResultSet rs;
 322                 for(int i=0 ; i<=100 ; i++)
 323                 {
 324                         start = System.nanoTime();
 325                         rs = stmt.executeQuery();
 326                         recordCount = 0;
 327                         while(rs.next())
 328                         {
 329                                 recordCount++;
 330                         }
 331
 332                         rs.close();
 333                         con.commit();
 334                         end = System.nanoTime();
 335
 336                         if((flag && recordCount !=1000) || (!flag && recordCount !=10000))
 337                         {
 338                            System.out.println("No. of records returned is around for Q2" + recordCount);
 339
 340                           return 0;
 341                         }
 342
 343                         curr = end - start;
 344                         tot = tot + curr;
 345                         count++;
 346                 }
 347
 348                 stmt.close();
 349                 return (int)tot/100/1000;
 350
 351 }*/
 352 // second query
 353 public static  int tenPerSel(Connection con, boolean flag)throws Exception
 354 {
 355         PreparedStatement  stmt = null;
 356         int searchVal = getRandom(9999 - 1000);
 357         int searchValEnd = searchVal + 999;
 358
 359         String stmtStr;
 360         if(flag)
 361         {
 362            String buf = "SELECT * from big1 where unique2 between "+ searchVal + " and "+ searchValEnd + ";";
 363            stmtStr = buf;
 364         }
 365         else
 366         {
 367             String buf = "SELECT * from big1 where unique1 between "+searchVal +" and "+ searchValEnd + ";";
 368                 stmtStr = buf;
 369         }
 370                 stmt = con.prepareStatement(stmtStr);
 371                 int count = 0, recordCount;
 372                 int ret = 0;
 373                 long start = 0, end = 0, curr = 0;
 374                 long tot =0;
 375                 ResultSet rs;
 376                 for(int i=0 ; i<=100 ; i++)
 377                 {
 378                         start = System.nanoTime();
 379                         rs = stmt.executeQuery();
 380                         recordCount = 0;
 381                         while(rs.next())
 382                         {
 383                                 recordCount++;
 384                         }
 385
 386                         rs.close();
 387                         con.commit();
 388                         end = System.nanoTime();
 389
 390
 391                         if(recordCount != 1000)
 392                         {
 393                            System.out.println("No. of records returned is around for Q2" + recordCount);
 394
 395                           return 0;
 396                         }
 397
 398                         curr = end - start;
 399                         tot = tot + curr;
 400                         count++;
 401                 }
 402
 403                 stmt.close();
 404                 return (int)tot/100/1000;
 405   }
 406
 407
 408 //*****************************************************
 409 public static void main(String[] args)
 410
 411    {
 412        try
 413        {
 414           Class.forName("csql.jdbc.JdbcSqlDriver");
 415           Connection con = DriverManager.getConnection("jdbc:csql", "root", "manager");
 416           Statement cStmt = con.createStatement();
 417
 418           System.out.println("\n\n");
 419           System.out.println("**********************************************************\n");
 420           System.out.println("NOTE :  Idx1-------Non cluster Unique Index(uniquue1).\n\tIdx2-------Cluster Unique Index(unique2).\n\tWall_Clk---");
 421           System.out.println("\n");
 422           System.out.println("Q.No.\tQuery Type\tNoIdx\tIdx1\tIdx2\tWall_Clk");
 423           System.out.println("-----\t----------\t-----\t----\t----\t--------");
 424
 425           try {
 426           cStmt.execute("DROP INDEX idx3");
 427           cStmt.close();
 428
 429           cStmt.execute("DROP INDEX idx4");// for q1 and q2
 430           cStmt.close();
 431           } catch(Exception e)
 432           {
 433               System.out.println("Indexes are already dropped\n");
 434           }
 435
 436           int q1 = onePerSel(con, true);
 437           System.out.println("1\t1% Sel\t\tY\t-\t-\t"+q1);
 438
 439           int q2 = tenPerSel(con,true);
 440           System.out.println("2\t10% Sel\t\tY\t-\t-\t"+q2);
 441
 442
 443           //create index
 444           cStmt.execute("CREATE INDEX idx4 ON big1(unique2)UNIQUE");
 445           cStmt.close();
 446
 447           int q3 = onePerSel(con,true);
 448           System.out.println("3\t1% Sel\t\t-\t-\tY\t"+q3);
 449
 450
 451           int q4 = tenPerSel(con,true);
 452           System.out.println("4\t10% Sel\t\t-\t-\tY\t"+q4);
 453
 454
 455           // drop index idx4
 456           cStmt.execute("DROP INDEX idx4");
 457           cStmt.close();
 458
 459           cStmt.execute("CREATE INDEX idx3 ON big1(unique1)UNIQUE");
 460           cStmt.close();
 461           int q5 = onePerSel(con,false);
 462           System.out.println("5\t1% Sel\t\t-\tY\t-\t"+q5);
 463
 464
 465           int q6 = tenPerSel(con,false);
 466           System.out.println("6\t10% Sel\t\t-\tY\t-\t"+q6);
 467
 468           cStmt.execute("DROP INDEX idx3");
 469           cStmt.close();
 470
 471           cStmt.execute("CREATE INDEX idx4 ON big1(unique2)UNIQUE");
 472           cStmt.close();
 473
 474           int q7 = singleTuple(con,true);
 475           System.out.println("7\t1 Tup Sel\t-\t-\tY\t"+q7);
 476
 477
 478           //commented query 8 as it is duplicate of query 3
 479           int q8 = onePerSel(con,true);
 480           System.out.println("8\t1% Sel\t\t-\t-\tY\t"+q1);
 481
 482           // joining
 483         /*  cStmt.execute("DROP INDEX idx4");
 484           cStmt.close();
 485
 486           cStmt.execute("DROP INDEX idx1");
 487           cStmt.close();
 488
 489           cStmt.execute("DROP INDEX idx2");
 490           cStmt.close();
 491
 492           cStmt.execute("DROP INDEX idx5");
 493           cStmt.close();
 494
 495           cStmt.execute("DROP INDEX idx6");
 496           cStmt.close();
 497
 498
 499           int Q9=1,Q10=2,Q11=3,Q15=4,Q16=5,Q17=6;
 500
 501           int q9 = joining(con, Q9);
 502           System.out.println("9\tJoin AselB\tY\t-\t-\t"+q9);
 503
 504           int q10 = joining(con,Q10);
 505           System.out.println("10\tJoin ABPrime\tY\t-\t-\t"+q10);
 506
 507           int q11 = joining(con,Q11);
 508           System.out.println("11\tJoin CselAselB\tY\t-\t-\t"+q11);
 509
 510           // create non-cluster index
 511           cStmt.execute("CREATE INDEX idx1 ON small(unique1)UNIQUE");
 512           cStmt.close();
 513
 514           cStmt.execute("CREATE INDEX idx3 ON big1(unique1)UNIQUE");
 515           cStmt.close();
 516
 517           cStmt.execute("CREATE INDEX idx5 ON big2(unique1)UNIQUE");
 518           cStmt.close();
 519
 520           int q15 = joining(con,Q15);
 521           System.out.println("15\tJoin AselB\t-\tY\t-\t"+q15);
 522
 523           int q16 = joining(con,Q16);
 524           System.out.println("16\tJoinABprime\t-\tY\t-\t"+q16);
 525
 526           int q17 = joining(con,Q17);
 527           System.out.println("17\tJoin CselAselB\t-\tY\t-\t"+q17);
 528
 529           // create index for projection
 530           cStmt.execute("CREATE INDEX idx2 ON small(unique2)UNIQUE");
 531           cStmt.close();
 532
 533           cStmt.execute("CREATE INDEX idx4 ON big1(unique2)UNIQUE");
 534           cStmt.close();
 535
 536           cStmt.execute("CREATE INDEX idx6 ON big2(unique2)UNIQUE");
 537           cStmt.close();
 538
 539           int q18 = projection(con,true);
 540           System.out.println("18\t1%Proj Sel\t-\tY\tY\t"+q18);
 541
 542           int q19 = projection(con,false);
 543           System.out.println("19\t100%Proj Sel\t-\tY\tY\t"+q19);*/
 544
 545
 546           // aggregate function
 547           //cStmt.execute("DROP INDEX idx3");
 548           //cStmt.close();
 549
 550           cStmt.execute("DROP INDEX idx4");
 551           cStmt.close();
 552
 553
 554           int min=1, ming=2,sum=3;// function parameter
 555
 556           int q20 = aggregate(con,min);
 557           System.out.println("20\tMinAgg No Grps\tY\t-\t-\t"+q20);
 558
 559           int q21 = aggregate(con,ming);
 560           System.out.println("21\tMinAgg Grps\tY\t-\t-\t"+q21);
 561
 562
 563           int q22 = aggregate(con,sum);
 564           System.out.println("22\tSumAgg Grps\tY\t-\t-\t"+q22);
 565
 566           //cStmt.execute("CREATE INDEX idx3 ON big1(unique1)UNIQUE");
 567           //cStmt.close();
 568
 569           cStmt.execute("CREATE INDEX idx4 ON big1(unique2)UNIQUE"); // create index agaig
 570           cStmt.close();
 571
 572           int q23 = aggregate(con,min);
 573           System.out.println("23\tMinAgg No Grps\t-\t-\tY\t"+q23);
 574
 575
 576           int q24 = aggregate(con,ming);
 577           System.out.println("24\tMinAgg Grps\t-\t-\tY\t"+q24);
 578
 579
 580           int q25 = aggregate(con,sum);
 581           System.out.println("25\tSumAgg Grps\t-\t-\tY\t"+q25);
 582
 583
 584           // drop index
 585           //cStmt.execute("DROP INDEX idx3");
 586           //cStmt.close();
 587
 588           cStmt.execute("DROP INDEX idx4");
 589           cStmt.close();
 590
 591
 592           int in=1,upd=2,del=3;// function parameter
 593
 594           int q26 = dmlstatement(con,in);
 595           System.out.println("26\tInsert 1 Tup\tY\t-\t-\t"+q26);
 596
 597
 598           int q27 = dmlstatement(con,upd);
 599           System.out.println("27\tupdate 1 Tup\tY\t-\t-\t"+q27);
 600
 601
 602           int q28 = dmlstatement(con,del);
 603           System.out.println("28\tdelete 1 Tup\tY\t-\t-\t"+q28);
 604
 605
 606           // create index again for dml statement
 607           cStmt.execute("CREATE INDEX idx3 ON big1(unique1)UNIQUE;");
 608           cStmt.close();
 609
 610           cStmt.execute("CREATE INDEX idx4 ON big1(unique2)UNIQUE;");
 611           cStmt.close();
 612
 613
 614           int q29 = dmlstatement(con,in);
 615           System.out.println("29\tinsert 1 Tup\t-\tY\tY\t"+q29);
 616
 617
 618           int q30 = dmlstatement(con,upd);
 619           System.out.println("30\tupdate 1 Tup\t-\tY\tY\t"+q30);
 620
 621
 622           int q31 = dmlstatement(con,del);
 623           System.out.println("31\tdelete 1 Tup\t-\tY\tY\t"+q31);
 624
 625           System.out.println("\n");
 626
 627           System.out.println("**********************************************************\n");
 628
 629
 630
 631
 632           con.close();
 633         }
 634         catch(Exception e) {
 635             System.out.println("Exception in Test: "+e);
 636             e.printStackTrace();
 637         }
 638
 639     }
 640 }
 641