code reorg
[csql.git] / demo / wisc / odbcBench.c
blob1c32b630b9925df6a62ae6ac43341a7ba05015de
2 #include <iostream>
3 #include <stdio.h>
4 #include <stdlib.h>
5 #include <string.h>
6 #include <time.h>
7 #include <sql.h>
8 #include <sqlext.h>
9 #include <NanoTimer.h>
10 using namespace std;
12 NanoTimer timer;
13 long long int timevalues[30]={0};
15 void extract_error(
16 char *fn,
17 SQLHANDLE handle,
18 SQLSMALLINT type)
20 SQLINTEGER i = 0;
21 SQLINTEGER native;
22 SQLCHAR state[ 7 ];
23 SQLCHAR text[256];
24 SQLSMALLINT len;
25 SQLRETURN ret;
27 fprintf(stderr,
28 "\n"
29 "The driver reported the following diagnostics whilst running "
30 "%s\n\n",
31 fn);
35 ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
36 sizeof(text), &len );
37 if (SQL_SUCCEEDED(ret))
38 printf("%s:%ld:%ld:%s\n", state, i, native, text);
40 while( ret == SQL_SUCCESS );
43 inline void
44 check_error (SQLSMALLINT handleType, SQLHANDLE handle, int rc, int line)
46 if (rc)
48 cout << "Error " << rc << " at line: " << line << endl;
49 SQLCHAR state[10];
50 SQLINTEGER native;
51 SQLCHAR mesg[300];
52 int i=0;
53 while (SQLGetDiagRec (handleType, handle, i++, state, &native, mesg, 300,
54 NULL) == SQL_SUCCESS)
56 mesg[299] = '\0';
57 cout << "state: " << state << endl;
58 cout << "native: " << native << endl;
59 cout << "mesg: " << mesg << endl;
61 exit (1);
66 inline void checkrc (int rc, int line)
68 if (rc)
70 printf("Error %d at line: %d\n", rc, line);
71 exit (1);
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)
95 int rc;
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__);
107 return;
109 void createTreeIndex(SQLHANDLE hstmt,bool flag)
111 int rc;
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__);
122 return;
124 void dropHashIndex(SQLHANDLE hstmt,bool flag)
126 int rc;
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__);
137 return;
139 void dropTreeIndex(SQLHANDLE hstmt,bool flag)
141 int rc;
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__);
152 return;
155 long long int onePerSel(SQLHANDLE henv, SQLHANDLE hdbc, SQLHANDLE hstmt)
157 int uni1;
158 int uni2;
159 char str1[200];
160 char str2[200];
161 int val[] = {1, 5, 10, 50, 100, 500, 1000, 5000 ,7500, 9900};;
162 int tempTermVar=0;
163 int tempTermVar2=0;
164 char tempval[124]="Value";
165 int rc;
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__);
180 int i, j, k;
181 int Count;
183 timer.reset ();
185 for (i = 0; i < 10; i++)
187 Count=0;
188 strcpy(tempval,"Value");
189 tempTermVar = val[i] ;
190 tempTermVar2 = val[i]+99 ;
191 timer.start ();
192 rc = SQLExecute (hstmt);
193 checkrc (rc, __LINE__);
194 while(SQL_SUCCEEDED(rc = SQLFetch(hstmt)))
196 Count++;
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__);
203 timer.stop ();
205 return timer.avg()/1000;
208 long long int runReadTest (SQLHANDLE henv, SQLHANDLE hdbc,SQLHANDLE hstmt,bool flag)
210 int uni1;
211 int uni2;
212 char str1[200];
213 char str2[200];
215 int val[] = {1, 10, 100, 500, 1000, 5000 ,7500, 8000, 9000, 9999};
216 int tempTermVar=0;
217 char tempval[124]="Value";
218 int rc;
220 if(flag){
221 rc = SQLPrepare (hstmt, (unsigned char *) "SELECT unique1, unique2, stringu1, stringu2 from big1 where unique1=?;", SQL_NTS);
223 else {
224 rc = SQLPrepare (hstmt, (unsigned char *) "SELECT unique1, unique2, stringu1, stringu2 from big1 where stringu1=?;", SQL_NTS);
226 checkrc (rc, __LINE__);
227 long sz = 0;
228 if(flag){
229 rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar, 0, NULL);
230 checkrc (rc, __LINE__);
232 else{
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__);
246 int i, j, k;
247 int Count;
249 timer.reset ();
251 for (i = 0; i < 10; i++)
253 strcpy(tempval,"Value");
254 if(flag){
255 tempTermVar = val[i] ;}
256 else{
257 char buf[10];
258 sprintf(buf,"%d",val[i]);
259 strcat(tempval,buf);
261 timer.start ();
262 rc = SQLExecute (hstmt);
263 checkrc (rc, __LINE__);
264 rc = SQLFetch (hstmt);
265 checkrc (rc, __LINE__);
266 Count++;
267 rc = SQLCloseCursor (hstmt);
268 checkrc (rc, __LINE__);
269 rc = SQLTransact (henv, hdbc, SQL_COMMIT);
270 checkrc (rc, __LINE__);
271 timer.stop ();
273 return timer.avg()/1000;
277 //aggregate
278 long long int aggregate(SQLHANDLE henv, SQLHANDLE hdbc,SQLHANDLE hstmt,bool flag)
280 int summin;
281 int rc;
282 if(flag){
283 rc = SQLPrepare (hstmt, (unsigned char *) "SELECT MIN(unique1) from big1;", SQL_NTS);
285 else {
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__);
292 int i, j, k;
293 int Count;
295 timer.reset ();
296 for (i = 0; i < 10; i++)
298 timer.start ();
299 rc = SQLExecute (hstmt);
300 checkrc (rc, __LINE__);
301 rc = SQLFetch (hstmt);
302 checkrc (rc, __LINE__);
303 Count++;
304 // printf("value=%d",summin);
305 rc = SQLCloseCursor (hstmt);
306 checkrc (rc, __LINE__);
307 rc = SQLTransact (henv, hdbc, SQL_COMMIT);
308 checkrc (rc, __LINE__);
309 timer.stop ();
311 return timer.avg()/1000;
315 long long int dmlstatement(SQLHANDLE henv, SQLHANDLE hdbc,SQLHANDLE hstmt,int val)
317 int tempTermVar=10000,tempTermVar2=10000;
318 int rc;
319 if(val==1){
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);
322 else if(val==2){
323 rc = SQLPrepare (hstmt, (unsigned char *) "UPDATE big1 SET two=1 WHERE unique1=?;", SQL_NTS);
324 }else
326 rc = SQLPrepare (hstmt, (unsigned char *) "DELETE FROM big1 WHERE unique1=?;", SQL_NTS);
328 checkrc (rc, __LINE__);
330 int i, j, k;
331 int Count;
333 timer.reset ();
334 for (i = 0; i < 100; i++)
335 { tempTermVar+=i;
336 rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar, 0, NULL);
337 checkrc (rc, __LINE__);
338 if(val==1)
340 tempTermVar2+=i;
341 rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar2, 0, NULL);
342 checkrc (rc, __LINE__);
344 timer.start ();
345 rc = SQLExecute (hstmt);
346 checkrc (rc, __LINE__);
347 rc = SQLTransact (henv, hdbc, SQL_COMMIT);
348 checkrc (rc, __LINE__);
349 timer.stop ();
350 tempTermVar=10000;tempTermVar2=10000;
352 return timer.avg()/1000;
356 //Joining
357 long long int joining(SQLHANDLE henv, SQLHANDLE hdbc, SQLHANDLE hstmt,int flag)
359 int uni1;
360 int uni2,uni3,uni4,uni5;
361 char str1[200];
362 char str2[200];
364 int val[] = {1, 5, 10, 50, 100, 250, 500, 750, 900, 999};
365 int tempTermVar=0;
366 char tempval[124]="Value";
367 int rc;
369 if(flag==1){
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);
372 else {
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__);
376 long sz = 0;
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__);
387 if(flag==2){
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__);
393 int i, j, k;
394 int Count;
396 timer.reset ();
398 for (i = 0; i < 10; i++)
400 tempTermVar = val[i] ;
401 timer.start ();
402 rc = SQLExecute (hstmt);
403 checkrc (rc, __LINE__);
404 rc = SQLFetch(hstmt);
405 Count++;
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__);
411 timer.stop ();
413 return timer.avg()/1000;
417 void printTime()
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)
439 int rc;
440 SQLHENV henv;
441 SQLHDBC hdbc;
442 SQLHSTMT hstmt;
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"),
451 (SQLCHAR *) "root",
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;
465 //Query 1
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");
478 //Hash
479 createHashIndex( hstmt,true);
480 timevalues[1] = runReadTest (henv, hdbc, hstmt,true);
481 timevalues[4] = runReadTest (henv, hdbc, hstmt,false);
482 timevalues[7] = 0;
483 timevalues[10] = 0;
484 timevalues[13] = 0;
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");
492 //printTime();
494 //TREE
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);
500 timevalues[14] = 0;
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");
509 printTime();
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__);
521 return 0;