13 long long int timevalues
[30]={0};
29 "The driver reported the following diagnostics whilst running "
35 ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
37 if (SQL_SUCCEEDED(ret))
38 printf("%s:%ld:%ld:%s\n", state, i, native, text);
40 while( ret == SQL_SUCCESS );
44 check_error (SQLSMALLINT handleType, SQLHANDLE handle, int rc, int line)
48 cout << "Error " << rc << " at line: " << line << endl;
53 while (SQLGetDiagRec (handleType, handle, i++, state, &native, mesg, 300,
57 cout << "state: " << state << endl;
58 cout << "native: " << native << endl;
59 cout << "mesg: " << mesg << endl;
66 inline void checkrc (int rc
, int line
)
70 printf("Error %d at line: %d\n", rc
, line
);
75 void readWriteTrans (SQLHDBC hdbc
)
77 int rc
= SQLSetConnectAttr (hdbc
, SQL_ATTR_ACCESS_MODE
,
78 (SQLPOINTER
) SQL_MODE_READ_WRITE
, 0);
79 checkrc (rc
, __LINE__
);
80 rc
= SQLEndTran (SQL_HANDLE_DBC
, hdbc
, SQL_COMMIT
);
81 checkrc (rc
, __LINE__
);
84 void readOnlyTrans (SQLHDBC hdbc
)
86 int rc
= SQLSetConnectAttr (hdbc
, SQL_ATTR_ACCESS_MODE
,
87 (SQLPOINTER
) SQL_MODE_READ_ONLY
, 0);
88 checkrc (rc
, __LINE__
);
89 rc
= SQLEndTran (SQL_HANDLE_DBC
, hdbc
, SQL_COMMIT
);
90 checkrc (rc
, __LINE__
);
93 void createHashIndex(SQLHANDLE hstmt
,bool flag
)
96 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "CREATE INDEX idx1 ON big1(unique1) HASH;", SQL_NTS
);
97 checkrc (rc
, __LINE__
);
98 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "CREATE INDEX idx2 ON big2(unique1) HASH;", SQL_NTS
);
99 checkrc (rc
, __LINE__
);
100 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "CREATE INDEX idx3 ON small(unique1) HASH", SQL_NTS
);
101 checkrc (rc
, __LINE__
);
102 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "CREATE INDEX idx4 ON big1(stringu1) HASH", SQL_NTS
);
103 checkrc (rc
, __LINE__
);
104 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "FLUSH CACHE", SQL_NTS
);
105 checkrc (rc
, __LINE__
);
109 void createTreeIndex(SQLHANDLE hstmt
,bool flag
)
112 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "CREATE INDEX idx5 ON big1(unique1) TREE;", SQL_NTS
);
113 checkrc (rc
, __LINE__
);
114 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "CREATE INDEX idx6 ON big2(unique1) TREE;", SQL_NTS
);
115 checkrc (rc
, __LINE__
);
116 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "CREATE INDEX idx7 ON small(unique1) TREE;", SQL_NTS
);
117 checkrc (rc
, __LINE__
);
118 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "CREATE INDEX idx8 ON big1(stringu1) TREE;", SQL_NTS
);
119 checkrc (rc
, __LINE__
);
120 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "FLUSH CACHE", SQL_NTS
);
121 checkrc (rc
, __LINE__
);
124 void dropHashIndex(SQLHANDLE hstmt
,bool flag
)
127 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "DROP INDEX idx1 ;", SQL_NTS
);
128 checkrc (rc
, __LINE__
);
129 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "DROP INDEX idx2 ;", SQL_NTS
);
130 checkrc (rc
, __LINE__
);
131 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "DROP INDEX idx3 ;", SQL_NTS
);
132 checkrc (rc
, __LINE__
);
133 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "DROP INDEX idx4 ;", SQL_NTS
);
134 checkrc (rc
, __LINE__
);
135 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "FLUSH CACHE", SQL_NTS
);
136 checkrc (rc
, __LINE__
);
139 void dropTreeIndex(SQLHANDLE hstmt
,bool flag
)
142 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "DROP INDEX idx5 ;", SQL_NTS
);
143 checkrc (rc
, __LINE__
);
144 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "DROP INDEX idx6 ;", SQL_NTS
);
145 checkrc (rc
, __LINE__
);
146 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "DROP INDEX idx7 ;", SQL_NTS
);
147 checkrc (rc
, __LINE__
);
148 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "DROP INDEX idx8 ;", SQL_NTS
);
149 checkrc (rc
, __LINE__
);
150 rc
= SQLExecDirect(hstmt
, (SQLCHAR
*) "FLUSH CACHE", SQL_NTS
);
151 checkrc (rc
, __LINE__
);
155 long long int onePerSel(SQLHANDLE henv
, SQLHANDLE hdbc
, SQLHANDLE hstmt
)
161 int val
[] = {1, 5, 10, 50, 100, 500, 1000, 5000 ,7500, 9900};;
164 char tempval
[124]="Value";
166 rc
= SQLPrepare (hstmt
, (unsigned char *) "SELECT unique1, unique2, stringu1 from big1 where unique1 between ? and ?;", SQL_NTS
);
167 checkrc (rc
, __LINE__
);
168 rc
= SQLBindParameter (hstmt
, 1, SQL_PARAM_INPUT
, SQL_C_LONG
, SQL_INTEGER
, 0, 0, &tempTermVar
, 0, NULL
);
169 checkrc (rc
, __LINE__
);
170 rc
= SQLBindParameter (hstmt
, 2, SQL_PARAM_INPUT
, SQL_C_LONG
, SQL_INTEGER
, 0, 0, &tempTermVar2
, 0, NULL
);
171 checkrc (rc
, __LINE__
);
173 rc
= SQLBindCol (hstmt
, 1, SQL_INTEGER
, &uni1
, 0, NULL
);
174 checkrc (rc
, __LINE__
);
175 rc
= SQLBindCol (hstmt
, 2, SQL_INTEGER
, &uni2
, 0, NULL
);
176 checkrc (rc
, __LINE__
);
177 rc
= SQLBindCol (hstmt
, 3, SQL_C_CHAR
, str1
, sizeof (str1
), NULL
);
178 checkrc (rc
, __LINE__
);
185 for (i
= 0; i
< 10; i
++)
188 strcpy(tempval
,"Value");
189 tempTermVar
= val
[i
] ;
190 tempTermVar2
= val
[i
]+99 ;
192 rc
= SQLExecute (hstmt
);
193 checkrc (rc
, __LINE__
);
194 while(SQL_SUCCEEDED(rc
= SQLFetch(hstmt
)))
198 // printf("Count=%d\n",Count);
199 rc
= SQLCloseCursor (hstmt
);
200 checkrc (rc
, __LINE__
);
201 rc
= SQLTransact (henv
, hdbc
, SQL_COMMIT
);
202 checkrc (rc
, __LINE__
);
205 return timer
.avg()/1000;
208 long long int runReadTest (SQLHANDLE henv
, SQLHANDLE hdbc
,SQLHANDLE hstmt
,bool flag
)
215 int val
[] = {1, 10, 100, 500, 1000, 5000 ,7500, 8000, 9000, 9999};
217 char tempval
[124]="Value";
221 rc
= SQLPrepare (hstmt
, (unsigned char *) "SELECT unique1, unique2, stringu1, stringu2 from big1 where unique1=?;", SQL_NTS
);
224 rc
= SQLPrepare (hstmt
, (unsigned char *) "SELECT unique1, unique2, stringu1, stringu2 from big1 where stringu1=?;", SQL_NTS
);
226 checkrc (rc
, __LINE__
);
229 rc
= SQLBindParameter (hstmt
, 1, SQL_PARAM_INPUT
, SQL_C_LONG
, SQL_INTEGER
, 0, 0, &tempTermVar
, 0, NULL
);
230 checkrc (rc
, __LINE__
);
233 rc
= SQLBindParameter (hstmt
, 1, SQL_PARAM_INPUT
, SQL_C_CHAR
, SQL_C_CHAR
, 196, 0, tempval
, 0, NULL
);
234 checkrc (rc
, __LINE__
);
237 rc
= SQLBindCol (hstmt
, 1, SQL_INTEGER
, &uni1
, 0, NULL
);
238 checkrc (rc
, __LINE__
);
239 rc
= SQLBindCol (hstmt
, 2, SQL_INTEGER
, &uni2
, 0, NULL
);
240 checkrc (rc
, __LINE__
);
241 rc
= SQLBindCol (hstmt
, 3, SQL_C_CHAR
, str1
, sizeof (str1
), NULL
);
242 checkrc (rc
, __LINE__
);
243 rc
= SQLBindCol (hstmt
, 4, SQL_C_CHAR
, str2
, sizeof (str2
), NULL
);
244 checkrc (rc
, __LINE__
);
251 for (i
= 0; i
< 10; i
++)
253 strcpy(tempval
,"Value");
255 tempTermVar
= val
[i
] ;}
258 sprintf(buf
,"%d",val
[i
]);
262 rc
= SQLExecute (hstmt
);
263 checkrc (rc
, __LINE__
);
264 rc
= SQLFetch (hstmt
);
265 checkrc (rc
, __LINE__
);
267 rc
= SQLCloseCursor (hstmt
);
268 checkrc (rc
, __LINE__
);
269 rc
= SQLTransact (henv
, hdbc
, SQL_COMMIT
);
270 checkrc (rc
, __LINE__
);
273 return timer
.avg()/1000;
278 long long int aggregate(SQLHANDLE henv
, SQLHANDLE hdbc
,SQLHANDLE hstmt
,bool flag
)
283 rc
= SQLPrepare (hstmt
, (unsigned char *) "SELECT MIN(unique1) from big1;", SQL_NTS
);
286 rc
= SQLPrepare (hstmt
, (unsigned char *) "SELECT SUM(unique1) from big1;", SQL_NTS
);
288 checkrc (rc
, __LINE__
);
289 rc
= SQLBindCol (hstmt
, 1, SQL_INTEGER
, &summin
, 0, NULL
);
290 checkrc (rc
, __LINE__
);
296 for (i
= 0; i
< 10; i
++)
299 rc
= SQLExecute (hstmt
);
300 checkrc (rc
, __LINE__
);
301 rc
= SQLFetch (hstmt
);
302 checkrc (rc
, __LINE__
);
304 // printf("value=%d",summin);
305 rc
= SQLCloseCursor (hstmt
);
306 checkrc (rc
, __LINE__
);
307 rc
= SQLTransact (henv
, hdbc
, SQL_COMMIT
);
308 checkrc (rc
, __LINE__
);
311 return timer
.avg()/1000;
315 long long int dmlstatement(SQLHANDLE henv
, SQLHANDLE hdbc
,SQLHANDLE hstmt
,int val
)
317 int tempTermVar
=10000,tempTermVar2
=10000;
320 rc
= SQLPrepare (hstmt
, (unsigned char *) "insert into big1 values(?,?,0,2,0,10,50,688,1950,4950,9950,1,100,'MXXXXXXXXXXXXXXXXXXXXXXXXXGXXXXXXXXXXXXXXXXXXXXXXXXC','GXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXA','OXXXXXXXXXXXXXXXXXXXXXXXXXOXXXXXXXXXXXXXXXXXXXXXXXXO');", SQL_NTS
);
323 rc
= SQLPrepare (hstmt
, (unsigned char *) "UPDATE big1 SET two=1 WHERE unique1=?;", SQL_NTS
);
326 rc
= SQLPrepare (hstmt
, (unsigned char *) "DELETE FROM big1 WHERE unique1=?;", SQL_NTS
);
328 checkrc (rc
, __LINE__
);
334 for (i
= 0; i
< 100; i
++)
336 rc
= SQLBindParameter (hstmt
, 1, SQL_PARAM_INPUT
, SQL_C_LONG
, SQL_INTEGER
, 0, 0, &tempTermVar
, 0, NULL
);
337 checkrc (rc
, __LINE__
);
341 rc
= SQLBindParameter (hstmt
, 1, SQL_PARAM_INPUT
, SQL_C_LONG
, SQL_INTEGER
, 0, 0, &tempTermVar2
, 0, NULL
);
342 checkrc (rc
, __LINE__
);
345 rc
= SQLExecute (hstmt
);
346 checkrc (rc
, __LINE__
);
347 rc
= SQLTransact (henv
, hdbc
, SQL_COMMIT
);
348 checkrc (rc
, __LINE__
);
350 tempTermVar
=10000;tempTermVar2
=10000;
352 return timer
.avg()/1000;
357 long long int joining(SQLHANDLE henv
, SQLHANDLE hdbc
, SQLHANDLE hstmt
,int flag
)
360 int uni2
,uni3
,uni4
,uni5
;
364 int val
[] = {1, 5, 10, 50, 100, 250, 500, 750, 900, 999};
366 char tempval
[124]="Value";
370 rc
= SQLPrepare (hstmt
, (unsigned char *) "SELECT big1.unique1, big1.unique2, small.unique1, small.stringu1 FROM small,big1 WHERE big1.unique1=small.unique1 AND small.unique1=?;", SQL_NTS
);
373 rc
= SQLPrepare (hstmt
, (unsigned char *) "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 = ?;", SQL_NTS
);
375 checkrc (rc
, __LINE__
);
377 rc
= SQLBindParameter (hstmt
, 1, SQL_PARAM_INPUT
, SQL_C_LONG
, SQL_INTEGER
, 0, 0, &tempTermVar
, 0, NULL
);
378 checkrc (rc
, __LINE__
);
379 rc
= SQLBindCol (hstmt
, 1, SQL_INTEGER
, &uni1
, 0, NULL
);
380 checkrc (rc
, __LINE__
);
381 rc
= SQLBindCol (hstmt
, 2, SQL_INTEGER
, &uni2
, 0, NULL
);
382 checkrc (rc
, __LINE__
);
383 rc
= SQLBindCol (hstmt
, 2, SQL_INTEGER
, &uni3
, 0, NULL
);
384 checkrc (rc
, __LINE__
);
385 rc
= SQLBindCol (hstmt
, 4, SQL_C_CHAR
, str1
, sizeof (str1
), NULL
);
386 checkrc (rc
, __LINE__
);
388 rc
= SQLBindCol (hstmt
, 5, SQL_INTEGER
, &uni4
, 0, NULL
);
389 checkrc (rc
, __LINE__
);
390 rc
= SQLBindCol (hstmt
, 6, SQL_INTEGER
, &uni5
, 0, NULL
);
391 checkrc (rc
, __LINE__
);
398 for (i
= 0; i
< 10; i
++)
400 tempTermVar
= val
[i
] ;
402 rc
= SQLExecute (hstmt
);
403 checkrc (rc
, __LINE__
);
404 rc
= SQLFetch(hstmt
);
406 // printf("%d,%d\n",uni1,uni3);
407 rc
= SQLCloseCursor (hstmt
);
408 checkrc (rc
, __LINE__
);
409 rc
= SQLTransact (henv
, hdbc
, SQL_COMMIT
);
410 checkrc (rc
, __LINE__
);
413 return timer
.avg()/1000;
419 printf("Wisconsin Benchmark Report:\n");
420 printf("*******************************************************\n");
421 printf(" Statement \t NoIndex Hash\t Tree\n");
422 printf("*******************************************************\n");
423 printf(" SelectInt 1 \t %lld\t %lld \t %lld\n",timevalues
[0],timevalues
[1],timevalues
[2]);
424 printf(" SelectStr 1 \t %lld\t %lld \t %lld\n",timevalues
[3],timevalues
[4],timevalues
[5]);
425 printf(" 1Per Sel \t %lld\t - \t %lld\n\n",timevalues
[6],timevalues
[8]);
426 printf(" Min All \t %lld\t - \t %lld\n",timevalues
[9],timevalues
[11]);
427 printf(" Sum All \t %lld\t - \t - \n\n",timevalues
[12]);
428 printf(" Insert 1 \t %lld\t %lld \t %lld\n",timevalues
[15],timevalues
[16],timevalues
[17]);
429 printf(" Update 1 \t %lld\t %lld \t %lld\n",timevalues
[18],timevalues
[19],timevalues
[20]);
430 printf(" Delete 1 \t %lld\t %lld \t %lld\n\n",timevalues
[21],timevalues
[22],timevalues
[23]);
431 printf(" Join(10K*1K)1 \t %lld\t %lld \t %lld\n",timevalues
[24],timevalues
[25],timevalues
[26]);
432 printf(" Join(10K*10K*1K)1 \t %lld\t %lld \t %lld\n",timevalues
[27],timevalues
[28],timevalues
[29]);
433 printf("*******************************************************\n");
437 int main (int ac
, char **av
)
443 rc
= SQLAllocHandle (SQL_HANDLE_ENV
, SQL_NULL_HANDLE
, &henv
);
444 checkrc (rc
, __LINE__
);
445 SQLSetEnvAttr(henv
, SQL_ATTR_ODBC_VERSION
, (void *) SQL_OV_ODBC3
, 0);
447 rc
= SQLAllocHandle (SQL_HANDLE_DBC
, henv
, &hdbc
);
448 checkrc (rc
, __LINE__
);
449 rc
= SQLConnect (hdbc
,
450 (SQLCHAR
*) "test", (SQLSMALLINT
) strlen ("test"),
452 (SQLSMALLINT
) strlen ("root"),
453 (SQLCHAR
*) "manager",
454 (SQLSMALLINT
) strlen (""));
456 if (SQL_SUCCEEDED(rc
)) {
457 printf("Connected\n");
459 checkrc (rc
, __LINE__
);
460 rc
= SQLSetConnectOption (hdbc
, SQL_AUTOCOMMIT
, SQL_AUTOCOMMIT_OFF
);
461 checkrc (rc
, __LINE__
);
462 rc
= SQLAllocHandle (SQL_HANDLE_STMT
, hdbc
, &hstmt
);
463 checkrc (rc
, __LINE__
);
464 int ins
=1,upd
=2,del
=3;
466 timevalues
[0] = runReadTest (henv
, hdbc
, hstmt
,true);
467 timevalues
[3] = runReadTest (henv
, hdbc
, hstmt
,false);
468 timevalues
[6] = onePerSel(henv
, hdbc
, hstmt
);
469 timevalues
[9] = aggregate(henv
, hdbc
, hstmt
,true);
470 timevalues
[12] = aggregate(henv
, hdbc
, hstmt
,false);
471 timevalues
[15] = dmlstatement(henv
, hdbc
, hstmt
,ins
);
472 timevalues
[18] = dmlstatement(henv
, hdbc
, hstmt
,upd
);
473 timevalues
[21] = dmlstatement(henv
, hdbc
, hstmt
,del
);
474 timevalues
[24] = joining(henv
, hdbc
, hstmt
,1);
475 timevalues
[27] = joining(henv
, hdbc
, hstmt
,2);
476 printf("NON INDEX OVER\n");
479 createHashIndex( hstmt
,true);
480 timevalues
[1] = runReadTest (henv
, hdbc
, hstmt
,true);
481 timevalues
[4] = runReadTest (henv
, hdbc
, hstmt
,false);
485 timevalues
[16] = dmlstatement(henv
, hdbc
, hstmt
,ins
);
486 timevalues
[19] = dmlstatement(henv
, hdbc
, hstmt
,upd
);
487 timevalues
[22] = dmlstatement(henv
, hdbc
, hstmt
,del
);
488 timevalues
[25] = joining(henv
, hdbc
, hstmt
,1);
489 timevalues
[28] = joining(henv
, hdbc
, hstmt
,2);
490 dropHashIndex(hstmt
,true);
491 printf("HASH INDEX OVER\n");
495 createTreeIndex(hstmt
,true);
496 timevalues
[2] = runReadTest (henv
, hdbc
, hstmt
,true);
497 timevalues
[5] = runReadTest (henv
, hdbc
, hstmt
,false);
498 timevalues
[8] = onePerSel(henv
, hdbc
, hstmt
);
499 timevalues
[11] = aggregate(henv
, hdbc
, hstmt
,true);
501 timevalues
[17] = dmlstatement(henv
, hdbc
, hstmt
,ins
);
502 timevalues
[20] = dmlstatement(henv
, hdbc
, hstmt
,upd
);
503 timevalues
[23] = dmlstatement(henv
, hdbc
, hstmt
,del
);
504 timevalues
[26] = joining(henv
, hdbc
,hstmt
,1);
505 timevalues
[29] = joining(henv
, hdbc
,hstmt
,2);
506 dropTreeIndex(hstmt
,true);
507 printf("TREE INDEX OVER\n");
510 rc
= SQLEndTran(SQL_HANDLE_DBC
, hdbc
, SQL_COMMIT
);
511 checkrc (rc
, __LINE__
);
513 rc
= SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
514 checkrc (rc
, __LINE__
);
515 rc
= SQLDisconnect (hdbc
);
516 checkrc (rc
, __LINE__
);
517 rc
= SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
518 checkrc (rc
, __LINE__
);
519 rc
= SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
520 checkrc (rc
, __LINE__
);