7 #include <sys/utsname.h>
17 #define MAX_QUERIES 33
19 /* variables used for timing */
21 int verboseOut
= 0; /* verboseOut flag */
23 int verify
= 0; /* Flag set to 1 if "-v" is specified, verify means
24 count all the tuples and print out a message if they
25 don't match the expected value
28 int modify
= 0; /* Flag to indicate whether or not to run the modification
29 queries. If set, tables are deleted on exit
32 int build
= 0; /* Flag set to 1 if "-b" is
33 specified, build means to populate
34 the tables with the data and run
35 the modification tests.
38 int readOnly
= 0; /* Flag to indicate whether or not we are set for read-only
42 long numSmallTblTups
= 1000; /* # of rows for the small table */
44 long numBigTblTups
= 10000; /* # of rows for the big tables */
46 extern unsigned int querySelection
; /* Query specification, 1 bit per query */
49 #define SMALLTBLNAME "small"
50 #define BIG1TBLNAME "big1"
51 #define BIG2TBLNAME "big2"
52 #define BPRIMETBLNAME "bprime"
56 static char *tableNames
[NUMTABLES
] = {
57 SMALLTBLNAME
, BIG1TBLNAME
, BIG2TBLNAME
, BPRIMETBLNAME
,
65 // Get base name of program
66 progname
= strrchr(prog
, '/');
73 /* Print the usage message */
76 "Usage:\t%s [-?] [-c <string>] [-v] [-verify] [-b] [-m] [-q <query_spec>]\n"
78 " -v Verbose trace messages\n"
79 " -q <query_spec> Specifies a comma-separated list of query numbers or\n"
80 " ranges (e.g., -q 1,3,5-7,10-14,25). If not specified,\n"
81 " ALL 32 queries are selected.\n"
82 " -b Build data store.\n"
83 " -m Run modify transactions (deletes data when done).\n"
84 " -verify Enables verification.\n"
85 " -readonly Set Queries to use readonly transactions.\n",
90 int populateDatabase (class DB
*db
, int empty
)
98 for (i
=0; i
<NUMTABLES
; i
++)
99 db
->emptyTable (tableNames
[i
]);
101 rc
+= db
->populateTable(SMALLTBLNAME
, numSmallTblTups
, numSmallTblTups
);
103 rc
+= db
->populateTable(BPRIMETBLNAME
, numBigTblTups
/10, numBigTblTups
);
105 rc
+= db
->populateTable(BIG1TBLNAME
, numBigTblTups
, numBigTblTups
);
107 rc
+= db
->populateTable(BIG2TBLNAME
, numBigTblTups
, numBigTblTups
);
113 get_args(int argc
, char **argv
)
119 if (!strcmp(argv
[i
], "-?")) {
123 else if ( !strcmp("-verify", argv
[i
]) ) {
126 printf ("Verification enabled.\n");
129 else if (!strcmp("-q", argv
[i
])) {
134 if (setQueries(argv
[i
+1])) {
140 else if ( !strcmp("-b", argv
[i
]) ) {
143 printf ("Data store build enabled.\n");
146 else if (!strcmp("-v", argv
[i
])) {
148 printf ("Verbose Tracing Enabled.\n");
151 else if (!strcmp("-readonly", argv
[i
])) {
154 printf ("Readonly transaction set for Queries.\n");
157 else if (!strcmp("-m", argv
[i
])) {
160 printf ("Modification queries enabled.\n");
171 main(int argc
, char *argv
[])
174 char *connect_string
= 0;
177 class wiscTimer
*timer
[MAX_QUERIES
];
181 /* parse the command line arguments */
183 get_args(argc
, argv
);
185 for (i
=0; i
<MAX_QUERIES
; i
++)
186 timer
[i
] = new wiscTimer();
188 class odbcDB
*odbcdb
= new odbcDB
;
189 db
= (class DB
*) odbcdb
;
192 fprintf (stderr
, "Runtime error - Test not specified at compile time.\n");
196 rc
= db
->connect (connect_string
);
199 printf ("ERROR in connection\n");
203 //printf ("build = %d\n", build);
206 /* Delete and recreate the tables */
208 for (i
=0; i
<NUMTABLES
; i
++) {
209 db
->dropTable (tableNames
[i
]);
210 rc
= db
->createTable (tableNames
[i
]);
213 printf("Create Tables failed"); exit(1);
217 populateDatabase (db
, 0);
220 printf ("Database Population Complete.\n");
224 /* Drop indexes so Non-Indexed Queries */
225 db
->dropIndex ("idx1", "small");
226 db
->dropIndex ("idx2", "small");
227 db
->dropIndex ("idx3", "big1");
228 db
->dropIndex ("idx4", "big1");
229 db
->dropIndex ("idx5", "big2");
230 db
->dropIndex ("idx6", "big2");
233 /* execute the non-indexed queries */
236 printf ("Running Non-Indexed Queries...\n");
238 /* query 1 - 1% selection on large table, no index */
240 rc
= db
->selectionQuery(1, timer
[1],
241 "select * from big1 where unique2 between %d and %d",
242 "select * from big2 where unique2 between %d and %d",
243 numBigTblTups
, numBigTblTups
/100, 0, verify
, 2, 2);
245 printf ("Query 1 failed\n");
247 /* query 2 - 10% selection on large table, no index */
249 rc
= db
->selectionQuery(2, timer
[2],
250 "select * from big1 where unique2 between %d and %d",
251 "select * from big2 where unique2 between %d and %d",
252 numBigTblTups
, numBigTblTups
/10, 0, verify
, 5, 2);
254 printf ("Query 2 failed\n");
256 /* query 9 - one join with range-selected input, no index */
259 "select * from big1, big2 where big1.unique2 = big2.unique2 and big2.unique2 < %d",
262 "select * from big1, big2 where big1.unique2 = big2.unique2 and big1.unique2 < %d",
265 rc
= db
->selectionQuery(9, timer
[9], queryBuf1
, queryBuf2
,
266 numBigTblTups
, numBigTblTups
/10, 0, verify
, 2, 0);
268 printf ("Query 9 failed\n");
270 /* query 10 - single join, no index */
272 rc
= db
->selectionQuery(10, timer
[10],
273 "select * from big1, bprime where big1.unique2 = bprime.unique2",
274 "select * from big2, bprime where big2.unique2 = bprime.unique2",
275 numBigTblTups
, numBigTblTups
/10, 0, verify
, 2, 0);
277 printf ("Query 10 failed\n");
279 // query 11 - two joins with filtered input, no index
281 sprintf(queryBuf1, "select * from small, big1, big2 "
282 "where big1.unique2 = big2.unique2 and "
283 "small.unique2 = big1.unique2 and "
284 "big1.unique2 < %d", numBigTblTups/10);
286 sprintf(queryBuf2, "select * from small, big2, big1 "
287 "where big2.unique2 = big1.unique2 and "
288 "small.unique2 = big2.unique2 and "
289 "big2.unique2 < %d", numBigTblTups/10);
291 rc = db->selectionQuery(11, timer[11], queryBuf1, queryBuf2,
292 numBigTblTups, numBigTblTups/10, 0, verify, 2, 0);
294 printf ("Query 11 failed\n");
296 /* query 18 - projection, 1% */
298 rc
= db
->selectionQuery (18, timer
[18],
299 "select distinct two, four, ten, twenty, onepercent, string4 from big1",
300 "select distinct two, four, ten, twenty, onepercent, string4 from big2",
301 numBigTblTups
, 400, 0, verify
, 2, 0);
304 printf ("Query 18 failed\n");
306 /* query 19 - projection, 100% */
308 rc
= db
->selectionQuery (19, timer
[19],
309 "select distinct two, four, ten, twenty, onepercent, tenpercent, twentypercent, fiftypercent, unique3, "
310 "evenonepercent, oddonepercent, stringu1, stringu2, string4 from big1",
311 "select distinct two, four, ten, twenty, onepercent, tenpercent, twentypercent, fiftypercent, unique3, "
312 "evenonepercent, oddonepercent, stringu1, stringu2, string4 from big2",
313 numBigTblTups
, numBigTblTups
, 0, verify
, 2, 0);
316 printf ("Query 19 failed");
318 // query 20 - min. agg., no grouping
320 rc
= db
->selectionQuery (20, timer
[20],
321 "select min(unique2) from big1",
322 "select min(unique2) from big2",
323 numBigTblTups
, 1, 0, verify
, 2, 0);
326 printf ("Query 20 failed\n");
328 // query 21 - min. agg., 100 groups
330 rc = db->selectionQuery (21, timer[21],
331 "select min(unique2) from big1 group by onepercent",
332 "select min(unique2) from big2 group by onepercent",
333 numBigTblTups, 100, 0, verify, 2, 0);
336 printf ("Query 21 failed\n");
338 // query 22 - sum agg., 100 groups
340 rc = db->selectionQuery (22, timer[22],
341 "select sum(unique2) from big1 group by onepercent",
342 "select sum(unique2) from big2 group by onepercent",
343 numBigTblTups, 100, 0, verify, 2, 0);
346 printf ("Query 22 failed\n");
349 // query 26 - insert one row, no index
351 rc
= db
->runInsert(26, timer
[26], numBigTblTups
, BIG1TBLNAME
, BIG2TBLNAME
);
354 printf ("Query 26, Insert failed\n");
356 // query 27 - delete one row, no index
358 rc
= db
->runDelete(27, timer
[27], numBigTblTups
, BIG1TBLNAME
, BIG2TBLNAME
);
360 printf ("Query 27, Delete failed\n");
362 // query 28 - update key attribute, no index
363 rc
= db
->runUpdate(28, timer
[28], "unique2", numBigTblTups
,
364 BIG1TBLNAME
, BIG2TBLNAME
);
366 printf ("Query 28, Update failed\n");
368 // Now destroy and recreate database.
370 if (populateDatabase (db
, 1))
371 printf ("repopulation of database failed.\n");
374 // Use statement handle allocated to create tables * as handle for creating indices
380 printf ("Creating indices...\n");
382 db
->createIndex ("idx1", "small", "unique1");
383 db
->createIndex ("idx2", "small", "unique2");
384 db
->createIndex ("idx3", "big1", "unique1");
385 db
->createIndex ("idx4", "big1", "unique2");
386 db
->createIndex ("idx5", "big2", "unique1");
387 db
->createIndex ("idx6", "big2", "unique2");
390 printf ("Index Creation Completed\n");
392 /* indexed queries */
395 printf ("Executing Indexed Queries...\n");
397 /* query 3 - 1% selection on big table, index */
399 rc
= db
->selectionQuery (3, timer
[3],
400 "select * from big1 where unique2 between %d and %d",
401 "select * from big2 where unique2 between %d and %d",
402 numBigTblTups
, numBigTblTups
/100, 0, verify
, 5, 2);
404 printf ("Query 3 failed\n");
406 /* query 4 - 10% selection on big table, index */
408 rc
= db
->selectionQuery (4, timer
[4],
409 "select * from big1 where unique2 between %d and %d",
410 "select * from big2 where unique2 between %d and %d",
411 numBigTblTups
, numBigTblTups
/10, 0, verify
, 5, 2);
413 printf ("Query 4 failed\n");
415 /* query 5 - 1% selection on big table, index */
417 rc
= db
->selectionQuery (5, timer
[5],
418 "select * from big1 where unique1 between %d and %d",
419 "select * from big2 where unique1 between %d and %d",
420 numBigTblTups
, numBigTblTups
/100, 0, verify
, 5, 2);
422 printf ("Query 5 failed\n");
424 /* query 6 - 10% selection on big table, index */
426 rc
= db
->selectionQuery (6, timer
[6],
427 "select * from big1 where unique1 between %d and %d",
428 "select * from big2 where unique1 between %d and %d",
429 numBigTblTups
, numBigTblTups
/10, 0, verify
, 5, 2);
431 printf ("Query 6 failed\n");
433 /* query 7 - 1 row selection on big table, index */
435 rc
= db
->selectionQuery (7, timer
[7],
436 "select * from big1 where unique2 = %d",
437 "select * from big2 where unique2 = %d",
438 numBigTblTups
, 1, 1, verify
, 5, 1);
440 printf ("Query 7 failed\n");
442 /* query 8 - 1% selection on big table, index */
444 rc
= db
->selectionQuery (8, timer
[8],
445 "select * from big1 where unique2 between %d and %d",
446 "select * from big2 where unique2 between %d and %d",
447 numBigTblTups
, numBigTblTups
/100, 0, verify
, 5, 2);
449 printf ("Query 8 failed\n");
451 /* query 12 - one join with filtered input, with index */
454 "select * from big1, big2 where big1.unique2 = big2.unique2 and big2.unique2 < %d",
458 "select * from big2, big1 where big2.unique2 = big1.unique2 and big1.unique2 < %d",
461 rc
= db
->selectionQuery (12, timer
[12], queryBuf1
, queryBuf2
, numBigTblTups
,
462 numBigTblTups
/10, 0, verify
, 2, 0);
464 printf ("Query 12 failed\n");
466 /* query 13 - one join, index */
468 rc
= db
->selectionQuery (13, timer
[13],
469 "select * from big1, bprime where big1.unique2 = bprime.unique2",
470 "select * from big2, bprime where big2.unique2 = bprime.unique2",
471 numBigTblTups
, numBigTblTups
/10, 0, verify
, 2, 0);
473 printf ("Query 13 failed\n");
475 // query 14 - two joins with filtered input, index
478 "select * from small, big1, big2 where big1.unique2 = big2.unique2 and "
479 "small.unique2 = big1.unique2 and big1.unique2 < %d",
483 "select * from small, big2, big1 where big2.unique2 = big1.unique2 and "
484 "small.unique2 = big2.unique2 and big2.unique2 < %d",
487 rc = db->selectionQuery (14, timer[14], queryBuf1, queryBuf2, numBigTblTups,
488 numBigTblTups/10, 0, verify, 2, 0);
490 printf ("Query 14 failed\n");
493 // query 15 - one join with filtered input, no index
496 "select * from big1, big2 where big1.unique1 = big2.unique1 and big2.unique1 < %d",
500 "select * from big2, big1 where big2.unique1 = big1.unique1 and big1.unique1 < %d",
503 rc
= db
->selectionQuery (15, timer
[15], queryBuf1
, queryBuf2
, numBigTblTups
,
504 numBigTblTups
/10, 0, verify
, 2, 0);
506 printf ("Query 15 failed\n");
508 /* query 16 - one join, index */
510 rc
= db
->selectionQuery (16, timer
[16],
511 "select * from big1, bprime where big1.unique1 = bprime.unique1",
512 "select * from big2, bprime where big2.unique1 = bprime.unique1",
513 numBigTblTups
, numBigTblTups
/10, 0, verify
, 2, 0);
515 printf ("Query 16 failed\n");
517 // query 17 - two joins with filtered input, index
520 "select * from small, big1, big2 where big1.unique1 = big2.unique1 and "
521 "small.unique1 = big1.unique1 and big1.unique1 < %d",
525 "select * from small, big2, big1 where big2.unique1 = big1.unique1 and "
526 "small.unique1 = big2.unique1 and big2.unique1 < %d",
529 rc = db->selectionQuery (17, timer[17], queryBuf1, queryBuf2, numBigTblTups,
530 numBigTblTups/10, 0, verify, 2, 0);
532 printf ("Query 17 failed\n");
535 // query 23 - min. agg., no grouping
537 rc
= db
->selectionQuery (23, timer
[23],
538 "select min(unique2) from big1",
539 "select min(unique2) from big2",
540 numBigTblTups
, 1, 0, verify
, 2, 0);
543 printf ("Query 23 failed\n");
545 // query 24 - min. agg., 100 groups
546 rc = db->selectionQuery (24, timer[24],
547 "select min(unique2) from big1 group by onepercent",
548 "select min(unique2) from big2 group by onepercent",
549 numBigTblTups, 100, 0, verify, 2, 0);
551 printf ("Query 24 failed\n");
553 // query 25 - sum agg., 100 groups
555 rc = db->selectionQuery (25, timer[25],
556 "select avg(unique2) from big1",
557 "select avg(unique2) from big2",
558 numBigTblTups, 100, 0, verify, 2, 0);
560 printf ("Query 25 failed\n");
563 // query 29 - insert one row, index
564 rc
= db
->runInsert (29, timer
[29], numBigTblTups
,
565 BIG1TBLNAME
, BIG2TBLNAME
);
567 printf ("Query 29 failed\n");
569 // query 30 - delete one row
571 rc
= db
->runDelete(30, timer
[30], numBigTblTups
,
572 BIG1TBLNAME
, BIG2TBLNAME
);
574 printf ("Query 30 failed\n");
576 // query 31 - update key attribute
578 rc
= db
->runUpdate(31, timer
[31], "unique2", numBigTblTups
,
579 BIG1TBLNAME
, BIG2TBLNAME
);
581 printf ("Query 31 failed\n");
583 // query 32 - update non-clustered key attribute
585 rc
= db
->runUpdate(32, timer
[32], "unique1", numBigTblTups
,
586 BIG1TBLNAME
, BIG2TBLNAME
);
588 printf ("Query 32 failed\n");
590 for (i
=0; i
<NUMTABLES
; i
++) {
591 db
->dropTable (tableNames
[i
]);
598 printf("\nQuery Type No Index Index1 Index2 Query Numbers\n");
599 printf("1%% Sel %12.6lf %12.6lf %12.6lf 1,3,5\n",
600 timer
[1]->avgTime(wiscWallTime
),
601 timer
[3]->avgTime(wiscWallTime
),
602 timer
[5]->avgTime(wiscWallTime
));
604 printf("10%% Sel %12.6lf %12.6lf %12.6lf 2,4,6\n",
605 timer
[2]->avgTime(wiscWallTime
),
606 timer
[4]->avgTime(wiscWallTime
),
607 timer
[6]->avgTime(wiscWallTime
));
609 printf("1 Tup Sel - %12.6lf - 7\n",
610 timer
[7]->avgTime(wiscWallTime
));
612 printf("1%% Sel - %12.6lf - 8\n",
613 timer
[8]->avgTime(wiscWallTime
));
615 printf("JoinAselB %12.6lf %12.6lf %12.6lf 9,12,15\n",
616 timer
[9]->avgTime(wiscWallTime
),
617 timer
[12]->avgTime(wiscWallTime
),
618 timer
[15]->avgTime(wiscWallTime
));
620 printf("JoinABprime %12.6lf %12.6lf %12.6lf 10,13,16\n",
621 timer
[10]->avgTime(wiscWallTime
),
622 timer
[13]->avgTime(wiscWallTime
),
623 timer
[16]->avgTime(wiscWallTime
));
625 printf("JoinCselAselB %12.6lf %12.6lf %12.6lf 11,14,17\n",
626 timer
[11]->avgTime(wiscWallTime
),
627 timer
[14]->avgTime(wiscWallTime
),
628 timer
[17]->avgTime(wiscWallTime
));
630 printf("MinAgg No Grps %12.6lf %12.6lf - 20,23\n",
631 timer
[20]->avgTime(wiscWallTime
),
632 timer
[23]->avgTime(wiscWallTime
));
634 printf("MinAgg 100 Grps %12.6lf %12.6lf - 21,24\n",
635 timer
[21]->avgTime(wiscWallTime
),
636 timer
[24]->avgTime(wiscWallTime
));
638 printf("SumAgg 100 Grps %12.6lf %12.6lf - 22,25\n",
639 timer
[22]->avgTime(wiscWallTime
),
640 timer
[25]->avgTime(wiscWallTime
));
642 printf("Delete 1 Tup %12.6lf - %12.6lf 27,30\n",
643 timer
[27]->avgTime(wiscWallTime
),
644 timer
[30]->avgTime(wiscWallTime
));
646 printf("Update 1 Tup %12.6lf %12.6lf %12.6lf 28,31,32\n",
647 timer
[28]->avgTime(wiscWallTime
),
648 timer
[31]->avgTime(wiscWallTime
),
649 timer
[32]->avgTime(wiscWallTime
));
651 printf("Insert 1 Tup %12.6lf - %12.6lf 26,29\n",
652 timer
[26]->avgTime(wiscWallTime
),
653 timer
[29]->avgTime(wiscWallTime
));
655 printf("1%% Prj No Dup %12.6lf - - 18\n",
656 timer
[18]->avgTime(wiscWallTime
));
658 printf("100%% Prj No Dup %12.6lf - - 19\n",
659 timer
[19]->avgTime(wiscWallTime
));
661 printf("\nBenchmark Results by Query - (times are in millisecs):\n");
663 printf ("\nQuery Wall-clk Resource User System\n");
666 for (i
=0; i
<MAX_QUERIES
; i
++) {
667 if (timer
[i
]->numIters() != 0) {
668 printf ("%2d %14f %14f %14f %14f\n", i
,
669 timer
[i
]->avgTime (wiscWallTime
),
670 timer
[i
]->avgTime (wiscResTime
),
671 timer
[i
]->avgTime (wiscUserTime
),
672 timer
[i
]->avgTime (wiscSysTime
));