?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
added more join tests
[csql.git] / test / performance / wisc / JDBCBench.java
blob3de80d05920354cc59dd19f3200bcf536c6b4ac0
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 {
19 String buf = " SELECT unique1, unique2, stringu1, stringu2 from big1 where unique2=?;";
20 stmtStr = buf;
21 }
22 else
23 {
24 String buf = "SELECT unique1, unique2, stringu1, stringu2 from big1 where unique1=?;";
25 stmtStr = buf;
26 }
27
28 stmt = con.prepareStatement(stmtStr);
29 int count=0, recordCount=0;
30 int ret =0;
31 long start=0, end=0, curr=0;
32 long tot=0;
33 ResultSet rs;
34
35
36 for(int i=0; i<=100; i++) {
37
38 stmt.setInt(1,i);
39 start = System.nanoTime();
40 rs = stmt.executeQuery();
41 recordCount=0;
42 while(rs.next())
43 {
44 recordCount++;
45 }
46 rs.close();
47 con.commit();
48 end = System.nanoTime();
49 if(recordCount != 1)
50 {
51 System.out.println("No of records for Q1: "+ recordCount );
52 return 0;
53 }
54
55 curr = end - start;
56 tot = tot + curr;
57 count++;
58 }
59
60 stmt.close();
61 return (int)tot/100/1000;
62 }
63
64 public static int onePerSel(Connection con, boolean flag) throws Exception
65 {
66 PreparedStatement stmt = null;
67 int searchVal = getRandom(9999 - 100);
68 int searchValEnd = searchVal+ 99;
69
70
71 String stmtStr;
72 if (flag) {
73 String buf= "SELECT * from big1 where unique2 between "+
74 searchVal + " and "+ searchValEnd+ ";";
75 stmtStr= buf;
76 }else{
77 String buf= "SELECT * from big1 where unique1 between "+
78 searchVal + " and "+ searchValEnd+ ";";
79 stmtStr= buf;
80 }
81 stmt = con.prepareStatement(stmtStr);
82 int count =0, recordCount=0;
83 int ret =0;
84 long start =0, end =0, curr =0;
85 long tot =0;
86 ResultSet rs;
87 for (int i =0 ; i<= 100 ; i++) {
88 start = System.nanoTime();
89
90 rs = stmt.executeQuery();
91 recordCount=0;
92 while (rs.next())
93 {
94 recordCount++;
95 }
96
97 rs.close();
98 con.commit();
99 end = System.nanoTime();
100
101
102 if (recordCount!= 100)
103 {
104 System.out.println("No. of records for Q2: "+recordCount);
105 return 0;
106 }
107 curr = end-start;
108 tot = tot + curr;
109 count++;
110
111 }
112 stmt.close();
113 return (int)tot/100/1000;
114 }
115
116 // aggregate
117 public static int aggregate(Connection con, int val, boolean flag)throws Exception
118 {
119 PreparedStatement stmt = null;
120 String stmtStr="";
121 if(val==1)
122 {
123 if (flag) {
124 String buf = "SELECT MIN(unique2) from big1;";
125 stmtStr = buf;
126 }else {
127 String buf = "SELECT MIN(unique1) from big1;";
128 stmtStr = buf;
129 }
130 }
131 else if (val ==2)
132 {
133 if (flag) {
134 String buf = "SELECT SUM(unique2) from big1;";
135 stmtStr = buf;
136 }else {
137 String buf = "SELECT SUM(unique1) from big1;";
138 stmtStr = buf;
139 }
140 }
141 else if(val==3)
142 {
143 if (flag) {
144 String buf = "SELECT MIN(unique2) FROM big1 GROUP BY onepercent;";
145 stmtStr = buf;
146 }else{
147 String buf = "SELECT MIN(unique1) FROM big1 GROUP BY onepercent;";
148 stmtStr = buf;
149 }
150 }
151 else
152 {
153 if (flag) {
154 String buf = "SELECT SUM(unique2) FROM big1 GROUP BY onepercent;";
155 stmtStr = buf;
156 }else {
157 String buf = "SELECT SUM(unique1) FROM big1 GROUP BY onepercent;";
158 stmtStr = buf;
159 }
160 }
161
162 stmt = con.prepareStatement(stmtStr);
163 int count = 0, recordCount;
164 int ret = 0;
165 long start = 0,end = 0,curr = 0;
166 long tot = 0;
167 ResultSet rs;
168
169 for(int i=0;i<10;i++){
170 start = System.nanoTime();
171 rs = stmt.executeQuery();
172 recordCount = 0;
173 while(rs.next())
174 {
175 recordCount++;
176 }
177
178 rs.close();
179 con.commit();
180 end = System.nanoTime();
181
182 if((val==1 || val ==2) && recordCount !=1)
183 {
184 System.out.println("No. of records for Q4-7(" + val +" ): " + recordCount);
185 return 0;
186 }
187 if((val==4 || val==3) && recordCount !=100)
188 {
189 System.out.println("No. of records for Q4-7("+ val + "): " + recordCount);
190 return 0;
191 }
192 curr = end - start;
193 tot = tot + curr;
194 count++;
195 }
196 stmt.close();
197 return (int)tot/10/1000;
198 }
199
200
201 // DML statement
202 public static int dmlstatement(Connection con, int val, boolean flag)throws Exception
203 {
204 PreparedStatement stmt = null;
205 String stmtStr="";
206 if(val==1)
207 {
208 String buf = "insert into big1 values(?,?,0,2,0,10,50,688,1950,4950,9950,1,100,'MXXXXXXXXXXXXXXXXXXXXXXXXXGXXXXXXXXXXXXXXXXXXXXXXXXC','GXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXA','OXXXXXXXXXXXXXXXXXXXXXXXXXOXXXXXXXXXXXXXXXXXXXXXXXXO');";
209 stmtStr = buf;
210 }
211 else if(val==2)
212 {
213 if(flag) {
214 String buf = "UPDATE big1 SET two=1 WHERE unique2=?;";
215 stmtStr = buf;
216 }else {
217 String buf = "UPDATE big1 SET two=1 WHERE unique1=?;";
218 stmtStr = buf;
219 }
220 }
221 else
222 {
223 if (flag) {
224 String buf = "DELETE FROM big1 WHERE unique2=?;";
225 stmtStr = buf;
226 }else {
227 String buf = "DELETE FROM big1 WHERE unique1=?;";
228 stmtStr = buf;
229 }
230 }
231
232 stmt = con.prepareStatement(stmtStr);
233 int count = 0, recordCount=0;
234 int ret = 0;
235 long start = 0,end = 0,curr = 0;
236 long tot = 0;
237 for(int i=0; i<100; i++){
238 stmt.setInt(1, 10000+i);
239 if (val ==1) stmt.setInt(2, 10000+i);
240 start = System.nanoTime();
241 ret = stmt.executeUpdate();
242 if(ret!=1) return 1;
243
244 con.commit();
245 end = System.nanoTime();
246 curr = end - start;
247 tot = tot + curr;
248 count++;
249 }
250 stmt.close();
251 return (int)tot/100/1000;
252 }
253
254 // Joining
255 public static int joining(Connection con,int val, boolean flag)throws Exception
256 {
257 PreparedStatement stmt = null;
258 String stmtStr= null;
259 if(val==1)
260 {
261 if (flag) {
262 String buf = "SELECT big1.unique1, big1.unique2, big2.unique2, big2.stringu1 FROM big2,big1 WHERE big1.unique2=big2.unique2 AND big2.unique2=1000;";
263 stmtStr = buf;
264 }else{
265 String buf = "SELECT big1.unique1, big1.unique2, big2.unique1, big2.stringu1 FROM big2,big1 WHERE big1.unique1=big2.unique1 AND big2.unique1=1000;";
266 stmtStr = buf;
267 }
268 }
269 else if(val==2)
270 {
271 if (flag) {
272 String buf = "SELECT big1.unique1, big1.unique2, big2.unique2, big2.stringu1 FROM big2,big1 WHERE big1.unique2=big2.unique2 AND big2.unique2<100;";
273 stmtStr = buf;
274 }else{
275 String buf = "SELECT big1.unique1, big1.unique2, big2.unique1, big2.stringu1 FROM big2,big1 WHERE big1.unique1=big2.unique1 AND big2.unique1<100;";
276 stmtStr = buf;
277 }
278 }
279 else if(val==3)
280 {
281 if (flag) {
282 String buf = "SELECT small.unique2, big1.unique2, big2.unique2, small.stringu1, big1.unique1, big2.unique1 FROM small,big1, big2 WHERE small.unique2=big1.unique2 AND big1.unique2=big2.unique2 AND big1.unique2 = 100;";
283 stmtStr=buf;
284 }else{
285 String buf = "SELECT small.unique1, big1.unique1, big2.unique1, small.stringu1, big1.unique2, big2.unique2 FROM small,big1, big2 WHERE small.unique1=big1.unique1 AND big1.unique1=big2.unique1 AND big1.unique1 = 100;";
286 stmtStr=buf;
287 }
288 }
289
290 stmt = con.prepareStatement(stmtStr);
291 int count=0,recordCount=0,ret=0;
292 long start=0,end=0,curr=0;
293 long tot=0;
294 ResultSet rs;
295 //for(int i=0;i<=100;i++)
296 {
297 start = System.nanoTime();
298 rs = stmt.executeQuery();
299 while(rs.next())
300 {
301 //System.out.println("Record:"+ rs.getInt(1)+ " "+ rs.getInt(2)+" " +rs.getInt(3)+ " "+ rs.getString(4));
302 recordCount++;
303 }
304 rs.close();
305 con.commit();
306 end=System.nanoTime();
307 if(val ==1 && recordCount!=1)
308 {
309 System.out.println("No. of records Join: "+recordCount);
310 return 0;
311 }
312 if(val ==2 && recordCount!=100)
313 {
314 System.out.println("Stmt: "+stmtStr);
315 System.out.println("No. of records Join: "+recordCount);
316 return 0;
317 }
318 curr = end - start;
319 tot = tot + curr;
320 count++;
321 }
322 stmt.close();
323 return (int)tot/1000;
324 }
325
326 // projection
327 /*public static int projection(Connection con, boolean flag)throws Exception
328 {
329 PreparedStatement stmt = null;
330
331 String stmtStr;
332 if(flag)
333 {
334 String buf = "SELECT DISTINCT two,four,ten,twenty,onepercent,string4 from big1;";
335 stmtStr = buf;
336 }
337 else
338 {
339 String buf = "SELECT DISTINCT two,four,ten,twenty,onepercent,tenpercent,twentypercent,fiftypercent,unique3,evenonepercent,oddonepercent,stringu1,stringu2,string4 FROM big1;";
340 stmtStr = buf;
341 }
342 stmt = con.prepareStatement(stmtStr);
343 int count = 0, recordCount;
344 int ret = 0;
345 long start = 0, end = 0, curr = 0;
346 long tot =0;
347 ResultSet rs;
348 for(int i=0 ; i<=100 ; i++)
349 {
350 start = System.nanoTime();
351 rs = stmt.executeQuery();
352 recordCount = 0;
353 while(rs.next())
354 {
355 recordCount++;
356 }
357
358 rs.close();
359 con.commit();
360 end = System.nanoTime();
361
362 if((flag && recordCount !=1000) || (!flag && recordCount !=10000))
363 {
364 System.out.println("No. of records returned is around for Q2" + recordCount);
365
366 return 0;
367 }
368
369 curr = end - start;
370 tot = tot + curr;
371 count++;
372 }
373
374 stmt.close();
375 return (int)tot/100/1000;
376
377 }*/
378 // second query
379 public static int tenPerSel(Connection con, boolean flag)throws Exception
380 {
381 PreparedStatement stmt = null;
382 int searchVal = getRandom(9999 - 1000);
383 int searchValEnd = searchVal + 999;
384
385 String stmtStr;
386 if(flag)
387 {
388 String buf = "SELECT * from big1 where unique2 between "+ searchVal + " and "+ searchValEnd + ";";
389 stmtStr = buf;
390 }
391 else
392 {
393 String buf = "SELECT * from big1 where unique1 between "+searchVal +" and "+ searchValEnd + ";";
394 stmtStr = buf;
395 }
396 stmt = con.prepareStatement(stmtStr);
397 int count = 0, recordCount;
398 int ret = 0;
399 long start = 0, end = 0, curr = 0;
400 long tot =0;
401 ResultSet rs;
402 for(int i=0 ; i<=100 ; i++)
403 {
404 start = System.nanoTime();
405 rs = stmt.executeQuery();
406 recordCount = 0;
407 while(rs.next())
408 {
409 recordCount++;
410 }
411
412 rs.close();
413 con.commit();
414 end = System.nanoTime();
415
416
417 if(recordCount != 1000)
418 {
419 System.out.println("No. of records for Q3: " + recordCount);
420 return 0;
421 }
422
423 curr = end - start;
424 tot = tot + curr;
425 count++;
426 }
427
428 stmt.close();
429 return (int)tot/100/1000;
430 }
431
432
433 //*****************************************************
434 public static void main(String[] args)
435
436 {
437 int flag=1;
438 if (args.length == 1)
439 {
440 if (args[0].equals("csql")) flag=1;
441 else if (args[0].equals("mysql")) flag=2;
442 else {
443 System.out.println("Valid values: csql or mysql");
444 return;
445 }
446 }
447 try
448 {
449 Connection con = null;
450 if (flag == 1) {
451 Class.forName("csql.jdbc.JdbcSqlDriver");
452 con = DriverManager.getConnection("jdbc:csql", "root", "manager");
453 }else if (flag == 2) {
454 Class.forName("com.mysql.jdbc.Driver");
455 con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");
456 }
457 con.setAutoCommit(false);
458 con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
459 Statement cStmt = con.createStatement();
460
461 int timeTaken[][] = new int[15][3];
462 int ins=1,upd=2,del=3;// function parameter
463 int min=1, sum=2, ming=3, sumg=4;// function parameter
464
465
466 timeTaken[0][0] = singleTuple(con,true);
467 timeTaken[1][0] = onePerSel(con, true);
468 timeTaken[2][0] = tenPerSel(con,true);
469 timeTaken[3][0] = aggregate(con,min, true);
470 timeTaken[4][0] = aggregate(con,sum, true);
471 timeTaken[5][0] = aggregate(con,ming, true);
472 timeTaken[6][0] = aggregate(con,sumg, true);
473
474 timeTaken[7][0] = dmlstatement(con,ins, true);
475 //timeTaken[8][0] = dmlstatement(con,upd, true);
476 timeTaken[9][0] = dmlstatement(con,del, true);
477
478 timeTaken[10][0] = joining(con,1, true);
479
480 timeTaken[11][0] = joining(con,2, true);
481 timeTaken[12][0] = joining(con,3, true);
482 System.out.println("Finished no index queries");
483
484 //create index
485 try{
486 if (flag ==1) {
487 cStmt.execute("CREATE INDEX idx1 ON big1(unique2) HASH");
488 cStmt.close();
489 cStmt.execute("CREATE INDEX idx2 ON big1(unique1) TREE");
490 cStmt.close();
491 cStmt.execute("CREATE INDEX idx3 ON big2(unique2) HASH");
492 cStmt.close();
493 cStmt.execute("CREATE INDEX idx4 ON big2(unique1) TREE");
494 cStmt.close();
495 cStmt.execute("CREATE INDEX idx5 ON small(unique2) HASH");
496 cStmt.close();
497 cStmt.execute("CREATE INDEX idx6 ON small(unique1) TREE");
498 cStmt.close();
499 con.commit();
500 }else if (flag ==2) {
501 cStmt.execute("CREATE INDEX mysqlc1 USING HASH ON big1(unique2)");
502 cStmt.execute("CREATE INDEX mysqlc2 USING BTREE ON big1(unique1)");
503 cStmt.execute("CREATE INDEX mysqlc3 USING HASH ON big2(unique2)");
504 cStmt.execute("CREATE INDEX mysqlc4 USING BTREE ON big2(unique1)");
505 cStmt.execute("CREATE INDEX mysqlc5 USING HASH ON small(unique2)");
506 cStmt.execute("CREATE INDEX mysqlc6 USING BTREE ON small(unique1)");
507 cStmt.close();
508 con.commit();
509 }
510 }catch(Exception e ){e.printStackTrace(); System.out.println("Error creating index");}
511
512 timeTaken[0][1] = singleTuple(con,true);
513 timeTaken[1][1] = onePerSel(con, true);
514 timeTaken[2][1] = tenPerSel(con,true);
515 timeTaken[3][1] = aggregate(con,min, true);
516 timeTaken[4][1] = aggregate(con,sum, true);
517 timeTaken[5][1] = aggregate(con,ming, true);
518 timeTaken[6][1] = aggregate(con,sumg, true);
519 timeTaken[7][1] = dmlstatement(con,ins, true);
520 //timeTaken[8][1] = dmlstatement(con,upd, true);
521 timeTaken[9][1] = dmlstatement(con,del, true);
522 timeTaken[10][1] = joining(con,1, true);
523 timeTaken[11][1] = joining(con,2, true);
524 timeTaken[12][1] = joining(con,3, true);
525 System.out.println("Finished hash index queries");
526
527 timeTaken[0][2] = singleTuple(con,false);
528 timeTaken[1][2] = onePerSel(con, false);
529 timeTaken[2][2] = tenPerSel(con,false);
530 timeTaken[3][2] = aggregate(con,min, false);
531 timeTaken[4][2] = aggregate(con,sum, false);
532 timeTaken[5][2] = aggregate(con,ming, false);
533 timeTaken[6][2] = aggregate(con,sumg, false);
534 timeTaken[7][2] = dmlstatement(con,ins, false);
535 //timeTaken[8][2] = dmlstatement(con,upd, false);
536 timeTaken[9][2] = dmlstatement(con,del, false);
537 timeTaken[10][2] = joining(con,1, false);
538 timeTaken[11][2] = joining(con,2, false);
539 timeTaken[12][2] = joining(con,3, false);
540 System.out.println("Finished tree index queries");
541
542
543
544 try{
545 if (flag ==1) {
546 cStmt.execute("DROP INDEX idx1;");
547 cStmt.close();
548 cStmt.execute("DROP INDEX idx2;");
549 cStmt.close();
550 cStmt.execute("DROP INDEX idx3;");
551 cStmt.close();
552 cStmt.execute("DROP INDEX idx4;");
553 cStmt.close();
554 cStmt.execute("DROP INDEX idx5;");
555 cStmt.close();
556 cStmt.execute("DROP INDEX idx6;");
557 cStmt.close();
558 con.commit();
559 } else if (flag ==2) {
560 cStmt.execute("DROP INDEX mysqlc1 on big1");
561 cStmt.close();
562 cStmt.execute("DROP INDEX mysqlc2 on big1");
563 cStmt.close();
564 cStmt.execute("DROP INDEX mysqlc3 on big2");
565 cStmt.close();
566 cStmt.execute("DROP INDEX mysqlc4 on big2");
567 cStmt.close();
568 cStmt.execute("DROP INDEX mysqlc5 on big2");
569 cStmt.close();
570 cStmt.execute("DROP INDEX mysqlc6 on big2");
571 cStmt.close();
572 con.commit();
573 }
574 }catch(Exception e ){System.out.println("Error dropping indexes");}
575
576 System.out.println("Wisconsin Benchmark Report:");
577
578 System.out.println("**********************************************************");
579 System.out.println(" Statement \t NoIndex\tHash \tTree");
580 System.out.println("**********************************************************");
581 System.out.println(" 1 Tuple \t "+timeTaken[0][0] +" \t \t"+ timeTaken[0][1]+ " \t"+ timeTaken[0][2]);
582 System.out.println(" 1% Sel \t "+timeTaken[1][0] +" \t \t"+ timeTaken[1][1]+ " \t"+ timeTaken[1][2]);
583 System.out.println(" 10% Sel \t "+timeTaken[2][0] +" \t \t"+ timeTaken[2][1]+ " \t"+ timeTaken[2][2]);
584
585 System.out.println(" Min All \t "+timeTaken[3][0] +" \t \t"+ timeTaken[3][1]+ " \t"+ timeTaken[3][2]);
586 System.out.println(" Sum All \t "+timeTaken[4][0] +" \t \t"+ timeTaken[4][1]+ " \t"+ timeTaken[4][2]);
587 System.out.println(" Min Grp(100) \t "+timeTaken[5][0] +" \t \t"+ timeTaken[5][1]+ "\t"+ timeTaken[5][2]);
588 System.out.println(" Sum Grp(100) \t "+timeTaken[6][0] +" \t \t"+ timeTaken[6][1]+ "\t"+ timeTaken[6][2]);
589
590 System.out.println(" Insert 1 \t "+timeTaken[7][0] +" \t \t"+ timeTaken[7][1]+ " \t"+ timeTaken[7][2]);
591 System.out.println(" Update 1 \t "+timeTaken[8][0] +" \t \t"+ timeTaken[8][1]+ " \t"+ timeTaken[8][2]);
592 System.out.println(" Delete 1 \t "+timeTaken[9][0] +" \t \t"+ timeTaken[9][1]+ " \t"+ timeTaken[9][2]);
593 System.out.println(" Join(10K*10K)1 \t "+timeTaken[10][0] +" \t \t"+ timeTaken[10][1]+ "\t"+ timeTaken[10][2]);
594 System.out.println(" Join(10K*10K)100 \t "+timeTaken[11][0] +" \t"+ timeTaken[11][1]+ "\t"+ timeTaken[11][2]);
595 System.out.println(" Join(10K*10K*1K)1\t "+timeTaken[12][0] +" \t"+ timeTaken[12][1]+ "\t"+ timeTaken[12][2]);
596 System.out.println("**********************************************************");
597
598 con.close();
599 }
600 catch(Exception e) {
601 System.out.println("Exception in Test: "+e);
602 e.printStackTrace();
603 }
604
605 }
606 }
Main memory database SQL engine (cvs import)