2 import java
.util
.Random
;
5 public static Random generator
= new Random();
6 public static int getRandom(int end
)
8 double val
= generator
.nextDouble();
14 public static int singleTuple(Connection con
, boolean flag
)throws Exception
16 PreparedStatement stmt
= null ;
20 String buf
= " SELECT * from big1 where unique2=?; ";
25 String buf
= "SELECT * from big1 where unique2=? ;";
29 stmt
= con
.prepareStatement(stmtStr
);
30 int count
=0, recordCount
=0;
32 long start
=0, end
=0, curr
=0;
37 for(int i
=0; i
<=100; i
++) {
40 start
= System
.nanoTime();
41 rs
= stmt
.executeQuery();
50 end
= System
.nanoTime();
53 System
.out
.println("records in Q7= "+ recordCount
);
63 return (int)tot
/100/1000;
66 public static int onePerSel(Connection con
, boolean flag
) throws Exception
68 PreparedStatement stmt
= null;
69 int searchVal
= getRandom(9999 - 100);
70 int searchValEnd
= searchVal
+ 99;
75 String buf
= "SELECT * from big1 where unique2 between "+
76 searchVal
+ " and "+ searchValEnd
+ ";";
79 String buf
= "SELECT * from big1 where unique1 between "+
80 searchVal
+ " and "+ searchValEnd
+ ";";
83 stmt
= con
.prepareStatement(stmtStr
);
84 int count
=0, recordCount
=0;
86 long start
=0, end
=0, curr
=0;
89 for (int i
=0 ; i
<= 100 ; i
++) {
90 start
= System
.nanoTime();
92 rs
= stmt
.executeQuery();
101 end
= System
.nanoTime();
104 if (recordCount
!= 100)
106 System
.out
.println("No. of records returned is wrong for Q1 "+recordCount
);
116 return (int)tot
/100/1000;
120 public static int aggregate(Connection con
, int val
)throws Exception
122 PreparedStatement stmt
= null;
126 String buf
= "SELECT MIN(unique2) from big1;";
131 String buf
= "SELECT MIN(unique2) FROM big1 GROUP BY onepercent;";
136 String buf
= "SELECT SUM(unique2) FROM big1 GROUP BY onepercent;";
140 stmt
= con
.prepareStatement(stmtStr
);
141 int count
= 0, recordCount
;
143 long start
= 0,end
= 0,curr
= 0;
147 //for(int i=0;i<100;i++){
148 start
= System
.nanoTime();
149 rs
= stmt
.executeQuery();
158 end
= System
.nanoTime();
160 if((val
==1) && recordCount
!=1)
162 System
.out
.println("No. of records returned is around for Q2" + recordCount
);
165 if((val
==2 || val
==3) && recordCount
!=100)
167 System
.out
.println("No. of records returned is around for Q2" + recordCount
);
175 return (int)tot
/1000;
180 public static int dmlstatement(Connection con
, int val
)throws Exception
182 PreparedStatement stmt
= null;
186 String buf
= "insert into big1 values(10010,10010,0,2,0,10,50,688,1950,4950,9950,1,100,'MXXXXXXXXXXXXXXXXXXXXXXXXXGXXXXXXXXXXXXXXXXXXXXXXXXC','GXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXA','OXXXXXXXXXXXXXXXXXXXXXXXXXOXXXXXXXXXXXXXXXXXXXXXXXXO');";
191 String buf
= "UPDATE big1 SET unique2=10001 WHERE unique2=10010;";
196 String buf
= "DELETE FROM big1 WHERE unique2=10001;";
200 stmt
= con
.prepareStatement(stmtStr
);
201 int count
= 0, recordCount
=0;
203 long start
= 0,end
= 0,curr
= 0;
208 //for(int i=0;i<100;i++){
209 start
= System
.nanoTime();
210 ret
= stmt
.executeUpdate();
215 end
= System
.nanoTime();
217 /*if((val==1) && recordCount !=1)
219 System.out.println("No. of records returned is around for Q2" + recordCount);
222 if((val==2 || val==3) && recordCount !=1)
224 System.out.println("No. of records returned is around for Q2" + recordCount);
232 return (int)tot
/1000;
236 public static int joining(Connection con
,int val
)throws Exception
238 PreparedStatement stmt
= null;
242 String buf
= "SELECT big1.unique2, big2.unique2 FROM big2,big1 WHERE big1.unique2=big2.unique2 AND big2.unique2<1000;";
247 String buf
= "SELECT * FROM big1,bprime WHERE big1.unique2=bprime.unique2;";
252 String buf
= "SELECT * FROM small,big1 WHERE small.unique2=big1.unique2 AND big1.unique2=big2.unique2 AND big1.unique2<1000;";
257 String buf
= "SELECT * FROM big1,big2 WHERE (big1.unique1=big2.unique1) AND (big1.unique2<1000);";
262 String buf
= "SELECT * FROM big1,bprime WHERE big1.unique1=bprime.unique1;";
267 String buf
= "SELECT * FROM small,big1 WHERE (small.unique1=big1.unique1) AND (big1.unique1=big2.unique1) AND (big1.unique1 < 1000);";
271 stmt
= con
.prepareStatement(stmtStr
);
272 int count
=0,recordCount
=0,ret
=0;
273 long start
=0,end
=0,curr
=0;
276 for(int i
=0;i
<=100;i
++)
278 start
= System
.nanoTime();
279 rs
= stmt
.executeQuery();
282 System
.out
.println("PRABA: "+ recordCount
);
285 System
.out
.println("PRABA: "+ recordCount
);
288 end
=System
.nanoTime();
289 if(recordCount
!=1000)
291 System
.out
.println("No. of records wrong "+recordCount
);
299 return (int)tot
/100/1000;
303 /*public static int projection(Connection con, boolean flag)throws Exception
305 PreparedStatement stmt = null;
310 String buf = "SELECT DISTINCT two,four,ten,twenty,onepercent,string4 from big1;";
315 String buf = "SELECT DISTINCT two,four,ten,twenty,onepercent,tenpercent,twentypercent,fiftypercent,unique3,evenonepercent,oddonepercent,stringu1,stringu2,string4 FROM big1;";
318 stmt = con.prepareStatement(stmtStr);
319 int count = 0, recordCount;
321 long start = 0, end = 0, curr = 0;
324 for(int i=0 ; i<=100 ; i++)
326 start = System.nanoTime();
327 rs = stmt.executeQuery();
336 end = System.nanoTime();
338 if((flag && recordCount !=1000) || (!flag && recordCount !=10000))
340 System.out.println("No. of records returned is around for Q2" + recordCount);
351 return (int)tot/100/1000;
355 public static int tenPerSel(Connection con
, boolean flag
)throws Exception
357 PreparedStatement stmt
= null;
358 int searchVal
= getRandom(9999 - 1000);
359 int searchValEnd
= searchVal
+ 999;
364 String buf
= "SELECT * from big1 where unique2 between "+ searchVal
+ " and "+ searchValEnd
+ ";";
369 String buf
= "SELECT * from big1 where unique1 between "+searchVal
+" and "+ searchValEnd
+ ";";
372 stmt
= con
.prepareStatement(stmtStr
);
373 int count
= 0, recordCount
;
375 long start
= 0, end
= 0, curr
= 0;
378 for(int i
=0 ; i
<=100 ; i
++)
380 start
= System
.nanoTime();
381 rs
= stmt
.executeQuery();
390 end
= System
.nanoTime();
393 if(recordCount
!= 1000)
395 System
.out
.println("No. of records returned is wrong for Q2 " + recordCount
);
406 return (int)tot
/100/1000;
410 //*****************************************************
411 public static void main(String
[] args
)
416 Class
.forName("csql.jdbc.JdbcSqlDriver");
417 Connection con
= DriverManager
.getConnection("jdbc:csql", "root", "manager");
418 Statement cStmt
= con
.createStatement();
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--------");
428 //cStmt.execute("DROP INDEX idx3");
431 cStmt
.execute("DROP INDEX idx4 on big1");// for q1 and q2
435 System
.out
.println("Indexes are already dropped\n");
438 int q1
= onePerSel(con
, true);
439 System
.out
.println("1\t1% Sel\t\tY\t-\t-\t"+q1
);
441 int q2
= tenPerSel(con
,true);
442 System
.out
.println("2\t10% Sel\t\tY\t-\t-\t"+q2
);
447 cStmt
.execute("CREATE INDEX idx4 ON big1(unique2) UNIQUE");
449 }catch(Exception e
){}
451 int q3
= onePerSel(con
,true);
452 System
.out
.println("3\t1% Sel\t\t-\t-\tY\t"+q3
);
455 int q4
= tenPerSel(con
,true);
456 System
.out
.println("4\t10% Sel\t\t-\t-\tY\t"+q4
);
461 cStmt
.execute("DROP INDEX idx4 on big1");
463 }catch(Exception e
){}
465 int q5
= onePerSel(con
,false);
466 System
.out
.println("5\t1% Sel\t\t-\tY\t-\t"+q5
);
469 int q6
= tenPerSel(con
,false);
470 System
.out
.println("6\t10% Sel\t\t-\tY\t-\t"+q6
);
473 cStmt
.execute("CREATE INDEX idx4 ON big1(unique2)UNIQUE");
475 }catch(Exception e
){}
477 int q7
= singleTuple(con
,true);
478 System
.out
.println("7\t1 Tup Sel\t-\t-\tY\t"+q7
);
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);
487 cStmt
.execute("DROP INDEX idx2");
489 cStmt
.execute("DROP INDEX idx4");
491 cStmt
.execute("DROP INDEX idx6");
495 System
.out
.println("Drop indexes failed");
497 System
.out
.println("All indexes dropped\n");
500 int Q9
=1,Q10
=2,Q11
=3,Q15
=4,Q16
=5,Q17
=6;
502 //int q9 = joining(con, Q9);
503 //System.out.println("9\tJoin AselB\tY\t-\t-\t"+q9);
506 //int q10 = joining(con,Q10);
507 //System.out.println("10\tJoin ABPrime\tY\t-\t-\t"+q10);
509 //int q11 = joining(con,Q11);
510 //System.out.println("11\tJoin CselAselB\tY\t-\t-\t"+q11);
513 // create non-cluster index
514 cStmt.execute("CREATE INDEX idx1 ON small(unique1)UNIQUE");
517 cStmt.execute("CREATE INDEX idx3 ON big1(unique1)UNIQUE");
520 cStmt.execute("CREATE INDEX idx5 ON big2(unique1)UNIQUE");
523 int q15 = joining(con,Q15);
524 System.out.println("15\tJoin AselB\t-\tY\t-\t"+q15);
526 int q16 = joining(con,Q16);
527 System.out.println("16\tJoinABprime\t-\tY\t-\t"+q16);
529 int q17 = joining(con,Q17);
530 System.out.println("17\tJoin CselAselB\t-\tY\t-\t"+q17);
532 // create index for projection
533 cStmt.execute("CREATE INDEX idx2 ON small(unique2)UNIQUE");
536 cStmt.execute("CREATE INDEX idx4 ON big1(unique2)UNIQUE");
539 cStmt.execute("CREATE INDEX idx6 ON big2(unique2)UNIQUE");
542 int q18 = projection(con,true);
543 System.out.println("18\t1%Proj Sel\t-\tY\tY\t"+q18);
545 int q19 = projection(con,false);
546 System.out.println("19\t100%Proj Sel\t-\tY\tY\t"+q19);*/
549 // aggregate function
550 //cStmt.execute("DROP INDEX idx3");
553 //cStmt.execute("DROP INDEX idx4 ");
557 int min
=1, ming
=2,sum
=3;// function parameter
559 int q20
= aggregate(con
,min
);
560 System
.out
.println("20\tMinAgg No Grps\tY\t-\t-\t"+q20
);
562 int q21
= aggregate(con
,ming
);
563 System
.out
.println("21\tMinAgg Grps\tY\t-\t-\t"+q21
);
566 int q22
= aggregate(con
,sum
);
567 System
.out
.println("22\tSumAgg Grps\tY\t-\t-\t"+q22
);
569 //cStmt.execute("CREATE INDEX idx3 ON big1(unique1)UNIQUE");
572 cStmt
.execute("CREATE INDEX idx4 ON big1(unique2)");
574 }catch(Exception e
){}
576 int q23
= aggregate(con
,min
);
577 System
.out
.println("23\tMinAgg No Grps\t-\t-\tY\t"+q23
);
580 int q24
= aggregate(con
,ming
);
581 System
.out
.println("24\tMinAgg Grps\t-\t-\tY\t"+q24
);
584 int q25
= aggregate(con
,sum
);
585 System
.out
.println("25\tSumAgg Grps\t-\t-\tY\t"+q25
);
589 //cStmt.execute("DROP INDEX idx3");
592 //cStmt.execute("DROP INDEX idx4");
595 int in
=1,upd
=2,del
=3;// function parameter
597 int q26 = dmlstatement(con,in);
598 System.out.println("26\tInsert 1 Tup\tY\t-\t-\t"+q26);
601 int q27 = dmlstatement(con,upd);
602 System.out.println("27\tupdate 1 Tup\tY\t-\t-\t"+q27);
605 int q28 = dmlstatement(con,del);
606 System.out.println("28\tdelete 1 Tup\tY\t-\t-\t"+q28);
610 cStmt
.execute("CREATE INDEX idx4 ON big1(unique2)UNIQUE;");
612 }catch(Exception e
){}
615 int q29 = dmlstatement(con,in);
616 System.out.println("29\tinsert 1 Tup\t-\tY\tY\t"+q29);
619 int q30 = dmlstatement(con,upd);
620 System.out.println("30\tupdate 1 Tup\t-\tY\tY\t"+q30);
623 int q31 = dmlstatement(con,del);
624 System.out.println("31\tdelete 1 Tup\t-\tY\tY\t"+q31);
626 System
.out
.println("\n");
628 System
.out
.println("**********************************************************\n");
636 System
.out
.println("Exception in Test: "+e
);