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 public static void main(String
[] args
)
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();
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
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");
277 cStmt
.execute("CREATE INDEX idx1 ON big1(unique1) HASH");
279 cStmt
.execute("CREATE INDEX idx2 ON big2(unique1) HASH");
281 cStmt
.execute("CREATE INDEX idx3 ON small(unique1) HASH");
283 cStmt
.execute("CREATE INDEX idx4 ON big1(stringu1) HASH");
285 cStmt
.execute("FLUSH CACHE");
288 }catch(Exception e
){e
.printStackTrace(); System
.out
.println("Error creating index");}
289 timeTaken
[0][1] = singleTuple(con
, true);
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");
302 cStmt
.execute("DROP INDEX idx1");
304 cStmt
.execute("DROP INDEX idx2");
306 cStmt
.execute("DROP INDEX idx3");
308 cStmt
.execute("DROP INDEX idx4");
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");
317 }catch(Exception e
){e
.printStackTrace(); System
.out
.println("Error creating index");}
319 timeTaken
[0][2] = singleTuple(con
, true);
320 timeTaken
[1][2] = onePerSel(con
);
321 timeTaken
[2][2] = aggregate(con
,min
);
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");
334 cStmt
.execute("DROP INDEX idx5;");
336 cStmt
.execute("DROP INDEX idx6;");
338 cStmt
.execute("DROP INDEX idx7;");
340 cStmt
.execute("DROP INDEX idx8;");
342 cStmt
.execute("FLUSH CACHE");
345 }catch(Exception e
){System
.out
.println("Error dropping indexes");}
347 System
.out
.println("Wisconsin Benchmark Report:");
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]);
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("**********************************************************");
368 System
.out
.println("Exception in Test: "+e
);