demo/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 public static void main(String[] args)
 248 {
 249      try
 250      {
 251           Connection con = null;
 252           Class.forName("csql.jdbc.JdbcSqlDriver");
 253           con = DriverManager.getConnection("jdbc:csql", "root", "manager");
 254           con.setAutoCommit(false);
 255           con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
 256           Statement cStmt = con.createStatement();
 257
 258           int timeTaken[][] = new int[15][3];
 259           int ins=1,upd=2,del=3;// function parameter
 260           int min=1, sum=2;// function parameter
 261
 262
 263           timeTaken[0][0] = singleTuple(con, true);
 264           timeTaken[1][0] = onePerSel(con);
 265           timeTaken[2][0] = aggregate(con,min);
 266           timeTaken[3][0] = aggregate(con,sum);
 267           timeTaken[4][0] = dmlstatement(con,ins);
 268           timeTaken[5][0] = dmlstatement(con,upd);
 269           timeTaken[6][0] = dmlstatement(con,del);
 270           timeTaken[7][0] = joining(con,1);
 271           timeTaken[8][0] = joining(con,2);
 272           timeTaken[9][0] = singleTuple(con, false);
 273           System.out.println("Finished no index queries");
 274
 275           //create index
 276           try{
 277               cStmt.execute("CREATE INDEX idx1 ON big1(unique1) HASH");
 278               cStmt.close();
 279               cStmt.execute("CREATE INDEX idx2 ON big2(unique1) HASH");
 280               cStmt.close();
 281               cStmt.execute("CREATE INDEX idx3 ON small(unique1) HASH");
 282               cStmt.close();
 283               cStmt.execute("CREATE INDEX idx4 ON big1(stringu1) HASH");
 284               cStmt.close();
 285               cStmt.execute("FLUSH CACHE");
 286               cStmt.close();
 287               con.commit();
 288           }catch(Exception e ){e.printStackTrace(); System.out.println("Error creating index");}
 289           timeTaken[0][1] = singleTuple(con, true);
 290           timeTaken[1][1] = 0;
 291           timeTaken[2][1] = 0;
 292           timeTaken[3][1] = 0;
 293           timeTaken[4][1] = dmlstatement(con,ins);
 294           timeTaken[5][1] = dmlstatement(con,upd);
 295           timeTaken[6][1] = dmlstatement(con,del);
 296           timeTaken[7][1] = joining(con,1);
 297           timeTaken[8][1] = joining(con,2);
 298           timeTaken[9][1] = singleTuple(con, false);
 299           System.out.println("Finished hash index queries");
 300
 301           try{
 302               cStmt.execute("DROP INDEX idx1");
 303               cStmt.close();
 304               cStmt.execute("DROP INDEX idx2");
 305               cStmt.close();
 306               cStmt.execute("DROP INDEX idx3");
 307               cStmt.close();
 308               cStmt.execute("DROP INDEX idx4");
 309               cStmt.close();
 310               cStmt.execute("CREATE INDEX idx5 ON big1(unique1) TREE");
 311               cStmt.execute("CREATE INDEX idx6 ON big2(unique1) TREE");
 312               cStmt.execute("CREATE INDEX idx7 ON small(unique1) TREE");
 313               cStmt.execute("CREATE INDEX idx8 ON big1(stringu1) TREE");
 314               cStmt.execute("FLUSH CACHE");
 315               cStmt.close();
 316               con.commit();
 317           }catch(Exception e ){e.printStackTrace(); System.out.println("Error creating index");}
 318
 319           timeTaken[0][2] = singleTuple(con, true);
 320           timeTaken[1][2] = onePerSel(con);
 321           timeTaken[2][2] = aggregate(con,min);
 322           timeTaken[3][2] = 0;
 323           timeTaken[4][2] = dmlstatement(con,ins);
 324           timeTaken[5][2] = dmlstatement(con,upd);
 325           timeTaken[6][2] = dmlstatement(con,del);
 326           timeTaken[7][2] = joining(con,1);
 327           timeTaken[8][2] = joining(con,2);
 328           timeTaken[9][2] = singleTuple(con, false);
 329           System.out.println("Finished tree index queries");
 330
 331
 332
 333           try{
 334               cStmt.execute("DROP INDEX idx5;");
 335               cStmt.close();
 336               cStmt.execute("DROP INDEX idx6;");
 337               cStmt.close();
 338               cStmt.execute("DROP INDEX idx7;");
 339               cStmt.close();
 340               cStmt.execute("DROP INDEX idx8;");
 341               cStmt.close();
 342               cStmt.execute("FLUSH CACHE");
 343               cStmt.close();
 344               con.commit();
 345           }catch(Exception e ){System.out.println("Error dropping indexes");}
 346
 347           System.out.println("Wisconsin Benchmark Report:");
 348
 349           System.out.println("**********************************************************");
 350           System.out.println(" Statement       \t NoIndex\tHash \tTree");
 351           System.out.println("**********************************************************");
 352           System.out.println(" SelectInt 1     \t "+timeTaken[0][0] +" \t \t"+ timeTaken[0][1]+ "   \t"+ timeTaken[0][2]);
 353           System.out.println(" SelectStr 1     \t "+timeTaken[9][0] +" \t \t"+ timeTaken[9][1]+ "   \t"+ timeTaken[9][2]);
 354           System.out.println(" 1% Sel          \t "+timeTaken[1][0] +" \t \t-   \t"+ timeTaken[1][2]);
 355
 356           System.out.println(" Min All         \t "+timeTaken[2][0] +" \t \t-   \t"+ timeTaken[2][2]);
 357           System.out.println(" Sum All         \t "+timeTaken[3][0] +" \t \t-   \t-");
 358           System.out.println(" Insert 1        \t "+timeTaken[4][0] +" \t \t"+ timeTaken[4][1]+ "   \t"+ timeTaken[4][2]);
 359           System.out.println(" Update 1       \t "+timeTaken[5][0] +" \t \t"+ timeTaken[5][1]+ "   \t"+ timeTaken[5][2]);
 360           System.out.println(" Delete 1        \t "+timeTaken[6][0] +" \t \t"+ timeTaken[6][1]+ "   \t"+ timeTaken[6][2]);
 361           System.out.println(" Join(10K*1K)1  \t "+timeTaken[7][0] +" \t \t"+ timeTaken[7][1]+ "\t"+ timeTaken[7][2]);
 362           System.out.println(" Join(10K*10K*1K)1\t "+timeTaken[8][0] +"\t  \t"+ timeTaken[8][1]+ "\t"+ timeTaken[8][2]);
 363           System.out.println("**********************************************************");
 364
 365           con.close();
 366         }
 367         catch(Exception e) {
 368             System.out.println("Exception in Test: "+e);
 369             e.printStackTrace();
 370         }
 371
 372     }
 373 }