2 import java
.util
.Random
;
5 public static Random generator
= new Random();
6 public static int getRandom(int end
)
8 double val
= generator
.nextDouble();
13 public static int singleTuple(Connection con
, boolean flag
)throws Exception
15 PreparedStatement stmt
= null ;
18 String buf
= "SELECT unique1, unique2, stringu1, stringu2 from big1 where unique1=?;";
21 String buf
= "SELECT unique1, unique2, stringu1, stringu2 from big1 where stringu1=?;";
25 stmt
= con
.prepareStatement(stmtStr
);
26 int count
=0, recordCount
=0;
28 long start
=0, end
=0, curr
=0;
31 int searchVal
[] = {1, 10, 100, 500, 1000, 5000 ,7500, 8000, 9000, 9999};
33 for(int i
=0; i
<10; i
++) {
34 start
= System
.nanoTime();
36 stmt
.setInt(1, searchVal
[i
]);
38 stmt
.setString(1, "Value" +searchVal
[i
]);
39 rs
= stmt
.executeQuery();
47 end
= System
.nanoTime();
50 System
.out
.println("No of records for Q1: "+ recordCount
);
60 return (int)tot
/10/1000;
63 public static int onePerSel(Connection con
) throws Exception
65 PreparedStatement stmt
= null;
68 String buf
= "SELECT unique1, unique2, stringu1 from big1 where unique1 between ? and ?;";
70 stmt
= con
.prepareStatement(stmtStr
);
71 int count
=0, recordCount
=0;
73 long start
=0, end
=0, curr
=0;
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();
90 end
= System
.nanoTime();
93 if (recordCount
!= 100)
95 System
.out
.println("No. of records for Q2: "+recordCount
);
104 return (int)tot
/10/1000;
108 public static int aggregate(Connection con
, int val
)throws Exception
110 PreparedStatement stmt
= null;
114 String buf
= "SELECT MIN(unique1) from big1;";
119 String buf
= "SELECT SUM(unique1) from big1;";
122 stmt
= con
.prepareStatement(stmtStr
);
123 int count
= 0, recordCount
;
125 long start
= 0,end
= 0,curr
= 0;
129 for(int i
=0;i
<10;i
++){
130 start
= System
.nanoTime();
131 rs
= stmt
.executeQuery();
140 end
= System
.nanoTime();
144 System
.out
.println("No. of records for Q4-7(" + val
+" ): " + recordCount
);
152 return (int)tot
/10/1000;
157 public static int dmlstatement(Connection con
, int val
)throws Exception
159 PreparedStatement stmt
= null;
163 String buf
= "insert into big1 values(?,?,0,2,0,10,50,688,1950,4950,9950,1,100,'MXXXXXXXXXXXXXXXXXXXXXXXXXGXXXXXXXXXXXXXXXXXXXXXXXXC','GXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXA','OXXXXXXXXXXXXXXXXXXXXXXXXXOXXXXXXXXXXXXXXXXXXXXXXXXO');";
168 String buf
= "UPDATE big1 SET two=1 WHERE unique1=?;";
173 String buf
= "DELETE FROM big1 WHERE unique1=?;";
177 stmt
= con
.prepareStatement(stmtStr
);
178 int count
= 0, recordCount
=0;
180 long start
= 0,end
= 0,curr
= 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();
190 end
= System
.nanoTime();
196 return (int)tot
/100/1000;
200 public static int joining(Connection con
,int val
)throws Exception
202 PreparedStatement stmt
= null;
203 String stmtStr
= null;
206 String buf
= "SELECT big1.unique1, big1.unique2, small.unique1, small.stringu1 FROM small,big1 WHERE big1.unique1=small.unique1 AND small.unique1=?;";
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 = ?;";
215 stmt
= con
.prepareStatement(stmtStr
);
216 int count
=0,recordCount
=0,ret
=0;
217 long start
=0,end
=0,curr
=0;
220 int searchVal
[] = {1, 5, 10, 50, 100, 250, 500, 750, 900, 999};
221 for(int i
=0;i
<10;i
++)
224 start
= System
.nanoTime();
225 stmt
.setInt(1, searchVal
[i
]);
226 rs
= stmt
.executeQuery();
229 //System.out.println("Record:"+ rs.getInt(1)+ " "+ rs.getInt(2)+" " +rs.getInt(3)+ " "+ rs.getString(4));
234 end
=System
.nanoTime();
237 System
.out
.println("No. of records Join: "+recordCount
);
245 return (int)tot
/10/1000;
247 //*****************************************************
248 public static void main(String
[] args
)
252 if (args
.length
== 1)
254 if (args
[0].equals("csql")) flag
=1;
255 else if (args
[0].equals("mysql")) flag
=2;
257 System
.out
.println("Valid values: csql or mysql");
263 Connection con
= null;
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", "");
271 con
.setAutoCommit(false);
272 con
.setTransactionIsolation(Connection
.TRANSACTION_READ_COMMITTED
);
273 Statement cStmt
= con
.createStatement();
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
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");
295 cStmt
.execute("CREATE INDEX idx1 ON big1(unique1) HASH");
297 cStmt
.execute("CREATE INDEX idx2 ON big2(unique1) HASH");
299 cStmt
.execute("CREATE INDEX idx3 ON small(unique1) HASH");
301 cStmt
.execute("CREATE INDEX idx4 ON big1(stringu1) HASH");
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)");
311 }catch(Exception e
){e
.printStackTrace(); System
.out
.println("Error creating index");}
312 timeTaken
[0][1] = singleTuple(con
, true);
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");
327 cStmt
.execute("DROP INDEX idx1");
329 cStmt
.execute("DROP INDEX idx2");
331 cStmt
.execute("DROP INDEX idx3");
333 cStmt
.execute("DROP INDEX idx4");
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");
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)");
351 }catch(Exception e
){e
.printStackTrace(); System
.out
.println("Error creating index");}
353 timeTaken
[0][2] = singleTuple(con
, true);
354 timeTaken
[1][2] = onePerSel(con
);
355 timeTaken
[2][2] = aggregate(con
,min
);
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");
369 cStmt
.execute("DROP INDEX idx5;");
371 cStmt
.execute("DROP INDEX idx6;");
373 cStmt
.execute("DROP INDEX idx7;");
375 cStmt
.execute("DROP INDEX idx8;");
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");
385 }catch(Exception e
){System
.out
.println("Error dropping indexes");}
387 System
.out
.println("Wisconsin Benchmark Report:");
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]);
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("**********************************************************");
408 System
.out
.println("Exception in Test: "+e
);