test/performance/wisc/odbcBench.c

   1
   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 #define ITERATION 100
  11 using namespace std;
  12 bool mysql;
  13
  14 NanoTimer timer;
  15 long long int timevalues[30][3]={0};
  16 void extract_error(
  17     char *fn,
  18     SQLHANDLE handle,
  19     SQLSMALLINT type)
  20 {
  21     SQLINTEGER   i = 0;
  22     SQLINTEGER   native;
  23     SQLCHAR      state[ 7 ];
  24     SQLCHAR      text[256];
  25     SQLSMALLINT  len;
  26     SQLRETURN    ret;
  27
  28     fprintf(stderr,
  29             "\n"
  30             "The driver reported the following diagnostics whilst running "
  31             "%s\n\n",
  32             fn);
  33
  34     do
  35     {
  36         ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
  37                             sizeof(text), &len );
  38         if (SQL_SUCCEEDED(ret))
  39             printf("%s:%ld:%ld:%s\n", state, i, native, text);
  40     }
  41     while( ret == SQL_SUCCESS );
  42 }
  43
  44 inline void
  45 check_error (SQLSMALLINT handleType, SQLHANDLE handle, int rc, int line)
  46 {
  47   if (rc)
  48     {
  49       cout << "Error " << rc << " at line: " << line << endl;
  50       SQLCHAR state[10];
  51       SQLINTEGER native;
  52       SQLCHAR mesg[300];
  53       int i=0;
  54       while (SQLGetDiagRec (handleType, handle, i++, state, &native, mesg, 300,
  55                             NULL) == SQL_SUCCESS)
  56         {
  57           mesg[299] = '\0';
  58           cout << "state: " << state << endl;
  59           cout << "native: " << native << endl;
  60           cout << "mesg: " << mesg << endl;
  61         }
  62       exit (1);
  63     }
  64 }
  65
  66
  67 inline void checkrc (int rc, int line)
  68 {
  69   if (rc)
  70     {
  71       cout << "Error " << rc << " at line: " << line << endl;
  72       exit (1);
  73     }
  74 }
  75
  76 void readWriteTrans (SQLHDBC hdbc)
  77 {
  78   int rc = SQLSetConnectAttr (hdbc, SQL_ATTR_ACCESS_MODE,
  79                               (SQLPOINTER) SQL_MODE_READ_WRITE, 0);
  80   checkrc (rc, __LINE__);
  81   rc = SQLEndTran (SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
  82   checkrc (rc, __LINE__);
  83 }
  84
  85 void readOnlyTrans (SQLHDBC hdbc)
  86 {
  87   int rc = SQLSetConnectAttr (hdbc, SQL_ATTR_ACCESS_MODE,
  88                               (SQLPOINTER) SQL_MODE_READ_ONLY, 0);
  89   checkrc (rc, __LINE__);
  90   rc = SQLEndTran (SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
  91   checkrc (rc, __LINE__);
  92 }
  93
  94 void createHashIndex(SQLHANDLE hstmt,bool flag)
  95 {
  96     int rc;
  97   if (mysql) {
  98     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx1 USING HASH ON big1(unique1);", SQL_NTS );
  99     checkrc (rc, __LINE__);
 100     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx2 USING HASH ON big2(unique1);", SQL_NTS );
 101     checkrc (rc, __LINE__);
 102     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx3 USING HASH ON small(unique1)", SQL_NTS );
 103     checkrc (rc, __LINE__);
 104     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx4 USING HASH ON big1(stringu1)", SQL_NTS );
 105     checkrc (rc, __LINE__);
 106   } else {
 107     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx1 ON big1(unique1) HASH;", SQL_NTS );
 108     checkrc (rc, __LINE__);
 109     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx2 ON big2(unique1) HASH;", SQL_NTS );
 110     checkrc (rc, __LINE__);
 111     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx3 ON small(unique1) HASH", SQL_NTS );
 112     checkrc (rc, __LINE__);
 113     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx4 ON big1(stringu1) HASH", SQL_NTS );
 114     checkrc (rc, __LINE__);
 115   }
 116 }
 117 void createTreeIndex(SQLHANDLE hstmt,bool flag)
 118 {
 119     int rc;
 120     if (mysql)
 121     {
 122     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx5 USING BTREE ON big1(unique1);", SQL_NTS );
 123     checkrc (rc, __LINE__);
 124     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx6 USING BTREE ON big2(unique1);", SQL_NTS );
 125     checkrc (rc, __LINE__);
 126     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx7 USING BTREE ON small(unique1);", SQL_NTS );
 127     checkrc (rc, __LINE__);
 128     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx8 USING BTREE ON big1(stringu1);", SQL_NTS );
 129     checkrc (rc, __LINE__);
 130     }else {
 131     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx5 ON big1(unique1) TREE;", SQL_NTS );
 132     checkrc (rc, __LINE__);
 133     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx6 ON big2(unique1) TREE;", SQL_NTS );
 134     checkrc (rc, __LINE__);
 135     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx7 ON small(unique1) TREE;", SQL_NTS );
 136     checkrc (rc, __LINE__);
 137     rc = SQLExecDirect(hstmt, (SQLCHAR*) "CREATE INDEX idx8 ON big1(stringu1) TREE;", SQL_NTS );
 138     checkrc (rc, __LINE__);
 139     }
 140
 141 }
 142 void dropHashIndex(SQLHANDLE hstmt,bool flag)
 143 {
 144     int rc;
 145     if (mysql) {
 146     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx1 on big1 ;", SQL_NTS );
 147     checkrc (rc, __LINE__);
 148     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx2 on big2;", SQL_NTS );
 149     checkrc (rc, __LINE__);
 150     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx3 on small", SQL_NTS );
 151     checkrc (rc, __LINE__);
 152     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx4 on big1;", SQL_NTS );
 153     checkrc (rc, __LINE__);
 154     } else {
 155     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx1 ;", SQL_NTS );
 156     checkrc (rc, __LINE__);
 157     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx2 ;", SQL_NTS );
 158     checkrc (rc, __LINE__);
 159     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx3 ;", SQL_NTS );
 160     checkrc (rc, __LINE__);
 161     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx4 ;", SQL_NTS );
 162     checkrc (rc, __LINE__);
 163     }
 164 }
 165 void dropTreeIndex(SQLHANDLE hstmt,bool flag)
 166 {
 167     int rc;
 168     if (mysql)
 169     {
 170     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx5 on big1 ;", SQL_NTS );
 171     checkrc (rc, __LINE__);
 172     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx6 on big2;", SQL_NTS );
 173     checkrc (rc, __LINE__);
 174     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx7 on small;", SQL_NTS );
 175     checkrc (rc, __LINE__);
 176     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx8 on big1;", SQL_NTS );
 177     checkrc (rc, __LINE__);
 178     }else {
 179     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx5 ;", SQL_NTS );
 180     checkrc (rc, __LINE__);
 181     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx6 ;", SQL_NTS );
 182     checkrc (rc, __LINE__);
 183     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx7 ;", SQL_NTS );
 184     checkrc (rc, __LINE__);
 185     rc = SQLExecDirect(hstmt, (SQLCHAR*) "DROP INDEX idx8 ;", SQL_NTS );
 186     checkrc (rc, __LINE__);
 187     }
 188
 189 }
 190
 191 long long int onePerSel(SQLHANDLE henv, SQLHANDLE hdbc, SQLHANDLE hstmt)
 192 {
 193    int uni1;
 194    int uni2;
 195    char str1[52];
 196    char str2[52];
 197    //int val[] = {1, 5, 10, 50, 100, 500, 1000, 5000 ,7500, 9500};
 198    int val[10] = {1, 5, 10, 50, 100, 250, 500, 750, 900, 999};
 199    int tempTermVar=0;
 200    int tempTermVar2=0;
 201    char tempval[124]="Value";
 202    int rc;
 203    readOnlyTrans (hdbc);
 204    rc = SQLPrepare (hstmt, (unsigned char *) "SELECT unique1, unique2, stringu1 from big1 where unique1 between ? and ?;", SQL_NTS);
 205    checkrc (rc, __LINE__);
 206    rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar, 0, NULL);
 207    checkrc (rc, __LINE__);
 208    rc = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar2, 0, NULL);
 209    checkrc (rc, __LINE__);
 210
 211    rc = SQLBindCol (hstmt, 1, SQL_INTEGER, &uni1, 0, NULL);
 212    checkrc (rc, __LINE__);
 213    rc = SQLBindCol (hstmt, 2, SQL_INTEGER, &uni2, 0, NULL);
 214    checkrc (rc, __LINE__);
 215    rc = SQLBindCol (hstmt, 3, SQL_C_CHAR, str1, sizeof (str1), NULL);
 216    checkrc (rc, __LINE__);
 217
 218    int i, j, k;
 219    int Count;
 220
 221    timer.reset ();
 222
 223    for (i = 0; i < 10; i++)
 224    {
 225        Count=0;
 226        strcpy(tempval,"Value");
 227        tempTermVar = val[i] ;
 228        tempTermVar2 = val[i]+99 ;
 229        timer.start ();
 230        rc = SQLExecute (hstmt);
 231        checkrc (rc, __LINE__);
 232        while(SQL_SUCCEEDED(rc = SQLFetch(hstmt)))
 233        {
 234            Count++;
 235        }
 236    //    printf("Count=%d\n",Count);
 237        rc = SQLCloseCursor (hstmt);
 238        checkrc (rc, __LINE__);
 239        rc = SQLTransact (henv, hdbc, SQL_COMMIT);
 240        checkrc (rc, __LINE__);
 241        timer.stop ();
 242        if (Count != 100) {
 243            printf("One % Selection returned %d rows", Count);
 244            readWriteTrans (hdbc);
 245            return 0;
 246        }
 247    }
 248    readWriteTrans (hdbc);
 249    //printf ("Read: 1 %lld\n", timer.avg ());
 250    return timer.avg()/1000;
 251 }
 252
 253 long long int runReadTest (SQLHANDLE henv, SQLHANDLE hdbc,SQLHANDLE hstmt,bool flag)
 254 {
 255   int uni1;
 256   int uni2;
 257   char str1[52];
 258   char str2[52];
 259
 260   //int val[] = {1, 10, 100, 500, 1000, 5000 ,7500, 8000, 9000, 9999};
 261   int val[10] = {1, 5, 10, 50, 100, 250, 500, 750, 900, 999};
 262   int tempTermVar=0;
 263   char tempval[52]="Value";
 264   int rc;
 265
 266   /* Set read-only transaction type */
 267   readOnlyTrans (hdbc);
 268   if(flag){
 269   rc = SQLPrepare (hstmt, (unsigned char *) "SELECT unique1, unique2, stringu1, stringu2 from big1 where unique1=?;", SQL_NTS);
 270   }
 271   else  {
 272   rc = SQLPrepare (hstmt, (unsigned char *) "SELECT unique1, unique2, stringu1, stringu2 from big1 where stringu1=?;", SQL_NTS);
 273   }
 274   checkrc (rc, __LINE__);
 275   long sz = 0;
 276   if(flag){
 277       rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar, 0, NULL);
 278       checkrc (rc, __LINE__);
 279   }
 280   else{
 281       rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_C_CHAR, 196, 0, tempval, 0, NULL);
 282       checkrc (rc, __LINE__);
 283
 284   }
 285   rc = SQLBindCol (hstmt, 1, SQL_INTEGER, &uni1, 0, NULL);
 286   checkrc (rc, __LINE__);
 287   rc = SQLBindCol (hstmt, 2, SQL_INTEGER, &uni2, 0, NULL);
 288   checkrc (rc, __LINE__);
 289   rc = SQLBindCol (hstmt, 3, SQL_C_CHAR, str1, sizeof (str1), NULL);
 290   checkrc (rc, __LINE__);
 291   rc = SQLBindCol (hstmt, 4, SQL_C_CHAR, str2, sizeof (str2), NULL);
 292   checkrc (rc, __LINE__);
 293
 294   int i, j, k;
 295   int Count;
 296
 297   timer.reset ();
 298   char buf[10]="Value";
 299
 300   /* Run 1per Test */
 301   for (i = 0; i < 10; i++)
 302     {
 303           if(flag){
 304               tempTermVar = val[i] ;}
 305           else{
 306               sprintf(tempval,"%s%d",buf, val[i]);
 307           }
 308           timer.start ();
 309           rc = SQLExecute (hstmt);
 310           checkrc (rc, __LINE__);
 311           rc = SQLFetch (hstmt);
 312           checkrc (rc, __LINE__);
 313           Count++;
 314           rc = SQLCloseCursor (hstmt);
 315           checkrc (rc, __LINE__);
 316           rc = SQLTransact (henv, hdbc, SQL_COMMIT);
 317           checkrc (rc, __LINE__);
 318           timer.stop ();
 319     }
 320   //printf ("Read: 1 %lld\n", timer.avg ());
 321   /* Set read-write transaction type */
 322   readWriteTrans (hdbc);
 323   return timer.avg()/1000;
 324 }
 325
 326 //DISTINCT
 327 long long int distinct(SQLHANDLE henv, SQLHANDLE hdbc,SQLHANDLE hstmt)
 328 {
 329     int var_onepercent;
 330     int rc;
 331
 332     readOnlyTrans (hdbc);
 333     rc = SQLPrepare (hstmt, (unsigned char *) "SELECT DISTINCT onepercent FROM big1;", SQL_NTS);
 334     checkrc (rc, __LINE__);
 335
 336     rc = SQLBindCol (hstmt, 1, SQL_INTEGER, &var_onepercent, 0, NULL);
 337     checkrc (rc, __LINE__);
 338
 339     int i,count;
 340     timer.reset ();
 341
 342     for (i = 0; i < 10; i++)
 343     {
 344         count=0;
 345         timer.start ();
 346         rc = SQLExecute (hstmt);
 347         checkrc (rc, __LINE__);
 348         while(SQL_SUCCEEDED(rc = SQLFetch(hstmt))){
 349             count++;
 350         }
 351         //checkrc (rc, __LINE__);
 352         rc = SQLCloseCursor (hstmt);
 353         checkrc (rc, __LINE__);
 354         rc = SQLTransact (henv, hdbc, SQL_COMMIT);
 355         checkrc (rc, __LINE__);
 356         timer.stop ();
 357         if (count != 100) {
 358             printf("distinct returned %d rows\n");
 359             readWriteTrans(hdbc);
 360             return 0;
 361         }
 362     }
 363     readWriteTrans(hdbc);
 364     return timer.avg()/1000;
 365 }
 366 long long int orderBy(SQLHANDLE henv, SQLHANDLE hdbc,SQLHANDLE hstmt)
 367 {
 368     int val[] = {1, 5, 10, 50, 100, 500, 1000, 5000 ,7500, 9500};
 369     int var_two, var_four, var_ten, var_twenty, var_onepercent;
 370     char str1[52];
 371     int tempTermVar=0, tempTermVar2=0, tempTermVar3=0;
 372     int rc;
 373
 374     rc = SQLPrepare (hstmt, (unsigned char *) "SELECT two, four, ten, twenty, onepercent, string4 from big1 where unique1 between ? and ? order by unique1;", SQL_NTS);
 375     checkrc (rc, __LINE__);
 376
 377     readOnlyTrans (hdbc);
 378     rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar, 0, NULL);
 379     checkrc (rc, __LINE__);
 380     rc = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar2, 0, NULL);
 381     checkrc (rc, __LINE__);
 382
 383
 384     rc = SQLBindCol (hstmt, 1, SQL_INTEGER, &var_two, 0, NULL);
 385     checkrc (rc, __LINE__);
 386
 387     rc = SQLBindCol (hstmt, 2, SQL_INTEGER, &var_four, 0, NULL);
 388     checkrc (rc, __LINE__);
 389
 390     rc = SQLBindCol (hstmt, 3, SQL_INTEGER, &var_ten, 0, NULL);
 391     checkrc (rc, __LINE__);
 392
 393     rc = SQLBindCol (hstmt, 4, SQL_INTEGER, &var_twenty, 0, NULL);
 394     checkrc (rc, __LINE__);
 395
 396     rc = SQLBindCol (hstmt, 5, SQL_INTEGER, &var_onepercent, 0, NULL);
 397     checkrc (rc, __LINE__);
 398
 399     rc = SQLBindCol (hstmt, 6, SQL_C_CHAR, str1, sizeof (str1), NULL);
 400     checkrc (rc, __LINE__);
 401
 402
 403     int i, j, k;
 404     int count;
 405
 406     timer.reset ();
 407
 408     for (i = 0; i < 10; i++)
 409     {
 410        count=0;
 411        tempTermVar=val[i];
 412        tempTermVar2 = val[i]+100 ;
 413        timer.start ();
 414        rc = SQLExecute (hstmt);
 415        checkrc (rc, __LINE__);
 416        rc = SQLFetch (hstmt);
 417        while(SQL_SUCCEEDED(rc = SQLFetch(hstmt))) count++;
 418        //checkrc (rc, __LINE__);
 419        rc = SQLCloseCursor (hstmt);
 420        checkrc (rc, __LINE__);
 421        rc = SQLTransact (henv, hdbc, SQL_COMMIT);
 422        checkrc (rc, __LINE__);
 423        timer.stop ();
 424        if (count != 100) {
 425            printf("orderby returned %d rows \n", count);
 426            readWriteTrans(hdbc);
 427            return 0;
 428        }
 429    }
 430    readWriteTrans(hdbc);
 431    return timer.avg()/1000;
 432 }
 433
 434
 435 //aggregate
 436 long long int aggregate(SQLHANDLE henv, SQLHANDLE hdbc,SQLHANDLE hstmt,int val,bool flag)
 437 {
 438     int summinmax=0,tempTermVar=0;
 439     int rc;
 440      /* Set read-only transaction type */
 441     readOnlyTrans (hdbc);
 442     if(val==1 && flag){
 443         rc = SQLPrepare (hstmt, (unsigned char *) "SELECT MIN(unique1) from big1;", SQL_NTS);
 444     }
 445     else if(val==1 && !flag)
 446     {
 447        rc = SQLPrepare (hstmt, (unsigned char *) "SELECT MIN(unique1) from big1 group by onepercent;", SQL_NTS);
 448
 449     }
 450     else if(val==2 && flag) {
 451        rc = SQLPrepare (hstmt, (unsigned char *) "SELECT MAX(unique1) from big1;", SQL_NTS);
 452     }
 453     else if(val==2 && !flag){
 454        rc = SQLPrepare (hstmt, (unsigned char *) "SELECT MAX(unique1) from big1 group by onepercent;", SQL_NTS);
 455     }
 456     else if(val==3 && flag) {
 457        rc = SQLPrepare (hstmt, (unsigned char *) "SELECT SUM(unique1) from big1;", SQL_NTS);
 458     }
 459     else if(val==3 && !flag){
 460        rc = SQLPrepare (hstmt, (unsigned char *) "SELECT SUM(unique1) from big1 group by onepercent;", SQL_NTS);
 461     }
 462     checkrc (rc, __LINE__);
 463
 464     rc = SQLBindCol (hstmt, 1, SQL_INTEGER, &summinmax, 0, NULL);
 465     checkrc (rc, __LINE__);
 466
 467     int i, j, k;
 468     int Count;
 469
 470     timer.reset ();
 471     for (i = 0; i < 10; i++)
 472     {
 473           timer.start ();
 474           rc = SQLExecute (hstmt);
 475           checkrc (rc, __LINE__);
 476           rc = SQLFetch (hstmt);
 477           checkrc (rc, __LINE__);
 478           Count++;
 479          // printf("value=%d",summin);
 480           rc = SQLCloseCursor (hstmt);
 481           checkrc (rc, __LINE__);
 482           rc = SQLTransact (henv, hdbc, SQL_COMMIT);
 483           checkrc (rc, __LINE__);
 484           timer.stop ();
 485     }
 486     //printf ("Read: 1 %lld\n", timer.avg ());
 487     /* Set read-write transaction type */
 488     readWriteTrans (hdbc);
 489     return timer.avg()/1000;
 490
 491 }
 492
 493 long long int dmlstatement(SQLHANDLE henv, SQLHANDLE hdbc,SQLHANDLE hstmt,int val)
 494 {
 495     int tempTermVar=10000,tempTermVar2=10000;
 496     int rc;
 497      /* Set read-only transaction type */
 498     readOnlyTrans (hdbc);
 499     if(val==1){
 500         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);
 501     }
 502     else  if(val==2){
 503         rc = SQLPrepare (hstmt, (unsigned char *) "UPDATE big1 SET two=1 WHERE unique1=?;", SQL_NTS);
 504     }else
 505     {
 506         rc = SQLPrepare (hstmt, (unsigned char *) "DELETE FROM big1 WHERE unique1=?;", SQL_NTS);
 507     }
 508     checkrc (rc, __LINE__);
 509
 510     int i, j, k;
 511     int Count;
 512
 513     timer.reset ();
 514     for (i = 0; i < 100; i++)
 515     {   tempTermVar+=i;
 516         rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar, 0, NULL);
 517         checkrc (rc, __LINE__);
 518         if(val==1)
 519         {
 520            tempTermVar2+=i;
 521            rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar2, 0, NULL);
 522            checkrc (rc, __LINE__);
 523         }
 524         timer.start ();
 525         rc = SQLExecute (hstmt);
 526         checkrc (rc, __LINE__);
 527         rc = SQLTransact (henv, hdbc, SQL_COMMIT);
 528         checkrc (rc, __LINE__);
 529         timer.stop ();
 530         tempTermVar=10000;tempTermVar2=10000;
 531     }
 532     //printf ("Read: 1 %lld\n", timer.avg ());
 533     /* Set read-write transaction type */
 534     readWriteTrans (hdbc);
 535     return timer.avg()/1000;
 536
 537 }
 538
 539 //Joining
 540 long long int joining(SQLHANDLE henv, SQLHANDLE hdbc, SQLHANDLE hstmt,int value)
 541 {
 542     int uni1;
 543     int uni2,uni3,uni4,uni5;
 544     char str1[52];
 545     char str2[52];
 546
 547     int val[10] = {1, 5, 10, 50, 100, 250, 500, 750, 900, 999};
 548     int tempTermVar=0;
 549     char tempval[124]="Value";
 550     int rc;
 551
 552   /* Set read-only transaction type */
 553     readOnlyTrans (hdbc);
 554     if(value==1 ){
 555       rc = SQLPrepare (hstmt, (unsigned char *) "SELECT big1.unique1, big1.unique2, small.unique1, small.stringu1 FROM big1,small WHERE big1.unique1=small.unique1 AND big1.unique1=?;", SQL_NTS);
 556     }
 557
 558     else {
 559       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.unique2 AND big1.unique2=big2.unique1 AND big1.unique1 = ?;", SQL_NTS);
 560     }
 561
 562     checkrc (rc, __LINE__);
 563     long sz = 0;
 564
 565
 566     rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar, 0, NULL);
 567     checkrc (rc, __LINE__);
 568
 569     rc = SQLBindCol (hstmt, 1, SQL_C_LONG, &uni1, 0, NULL);
 570     checkrc (rc, __LINE__);
 571     rc = SQLBindCol (hstmt, 2, SQL_C_LONG, &uni2, 0, NULL);
 572     checkrc (rc, __LINE__);
 573     rc = SQLBindCol (hstmt, 3, SQL_C_LONG, &uni3, 0, NULL);
 574     checkrc (rc, __LINE__);
 575     rc = SQLBindCol (hstmt, 4, SQL_C_CHAR, str1, sizeof (str1), NULL);
 576     checkrc (rc, __LINE__);
 577     if(value==2 ){
 578         rc = SQLBindCol (hstmt, 5, SQL_C_LONG, &uni4, 0, NULL);
 579         checkrc (rc, __LINE__);
 580         rc = SQLBindCol (hstmt, 6, SQL_C_LONG, &uni5, 0, NULL);
 581         checkrc (rc, __LINE__);
 582     }
 583     int i, j, k;
 584     int Count=0;
 585
 586     timer.reset ();
 587
 588     for (i = 0; i < 10; i++)
 589     {
 590         tempTermVar = val[i];
 591         timer.start ();
 592         rc = SQLExecute (hstmt);
 593         checkrc (rc, __LINE__);
 594         rc=SQLFetch(hstmt);
 595         Count++;
 596         rc = SQLCloseCursor (hstmt);
 597         checkrc (rc, __LINE__);
 598         rc = SQLTransact (henv, hdbc, SQL_COMMIT);
 599         checkrc (rc, __LINE__);
 600         timer.stop ();
 601         // printf("uni1=%d uni2=%d uni3=%d str1=%s \n",uni1,uni2,uni3,str1);
 602     }
 603     readWriteTrans (hdbc);
 604     return timer.avg()/1000;
 605 }
 606 //JOINCondition
 607
 608 long long int joinCondition(SQLHANDLE henv, SQLHANDLE hdbc, SQLHANDLE hstmt,int value)
 609 {
 610     int uni1;
 611     int uni2,uni3,uni4,uni5;
 612     char str1[52];
 613     char str2[52];
 614     int rc;
 615     int searchVal[] = {1, 5, 10, 50, 100, 200, 250, 500, 750, 800};
 616
 617   //Set read-only transaction type
 618     readOnlyTrans (hdbc);
 619     if(value==1 )
 620     {
 621       rc = SQLPrepare (hstmt, (unsigned char *) "SELECT big1.unique1, big1.unique2, small.unique1, small.stringu1 FROM big1,small WHERE big1.unique1=small.unique1 AND big1.unique1 > ? and big1.unique1 <= ?;", SQL_NTS);
 622
 623     }
 624     else {
 625       rc = SQLPrepare (hstmt, (unsigned char *) "SELECT small.unique1, big1.unique1, big2.unique1, small.stringu1, big1.unique2, big2.unique2 FROM big1, big2, small WHERE big1.unique1=small.unique1 AND big1.unique1=big2.unique1 AND big1.unique1 > ? and big1.unique1 <= ?;", SQL_NTS);
 626     }
 627
 628     checkrc (rc, __LINE__);
 629     int tempTermVar1 =0, tempTermVar2=0;
 630
 631     rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar1, 0, NULL);
 632     checkrc (rc, __LINE__);
 633     rc = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &tempTermVar2, 0, NULL);
 634     checkrc (rc, __LINE__);
 635
 636     long sz = 0;
 637     rc = SQLBindCol (hstmt, 1, SQL_C_SLONG, &uni1, 0, NULL);
 638     checkrc (rc, __LINE__);
 639     rc = SQLBindCol (hstmt, 2, SQL_C_SLONG, &uni2, 0, NULL);
 640     checkrc (rc, __LINE__);
 641     rc = SQLBindCol (hstmt, 3, SQL_C_SLONG, &uni3, 0, NULL);
 642     checkrc (rc, __LINE__);
 643     rc = SQLBindCol (hstmt, 4, SQL_C_CHAR, str1, sizeof (str1), NULL);
 644     checkrc (rc, __LINE__);
 645     if(value==2){
 646         rc = SQLBindCol (hstmt, 5, SQL_C_SLONG, &uni4, 0, NULL);
 647         checkrc (rc, __LINE__);
 648         rc = SQLBindCol (hstmt, 6, SQL_C_SLONG, &uni5, 0, NULL);
 649         checkrc (rc, __LINE__);
 650     }
 651   int i, j, k;
 652   int count=0;
 653
 654   timer.reset ();
 655   for(i=0;i<10;i++){
 656     count=0;
 657     tempTermVar1 = searchVal[i];
 658     tempTermVar2 = searchVal[i]+100;
 659     timer.start ();
 660     rc = SQLExecute (hstmt);
 661     checkrc (rc, __LINE__);
 662     while(SQL_SUCCEEDED(rc = SQLFetch(hstmt)))
 663     {
 664       count++;
 665       //printf("Uni1:%d uni2=%d uni3=%d \n",uni1,uni2,uni3);
 666     }
 667     rc = SQLCloseCursor (hstmt);
 668     checkrc (rc, __LINE__);
 669     rc = SQLTransact (henv, hdbc, SQL_COMMIT);
 670     checkrc (rc, __LINE__);
 671     timer.stop ();
 672     if (count != 100) {
 673        printf ("range join returned %d rows\n", count);
 674        readWriteTrans (hdbc);
 675        return 0;
 676     }
 677   }
 678   readWriteTrans (hdbc);
 679   return timer.avg()/1000;
 680
 681 }
 682
 683 void printTime()
 684 {
 685     printf("Wisconsin Benchmark Report:\n");
 686     printf("*******************************************************\n");
 687     printf(" Statement               \tNoIndex\tHash\tTree\n");
 688     printf("*******************************************************\n");
 689     printf(" SelectInt 1                \t%lld\t%lld\t%lld\n",timevalues[0][0],timevalues[0][1],timevalues[0][2]);
 690     printf(" SelectStr 1                \t%lld\t%lld\t%lld\n",timevalues[1][0],timevalues[1][1],timevalues[1][2]);
 691     printf(" 1Per Sel           \t%lld\t%lld\t%lld\n",timevalues[2][0],timevalues[2][1],timevalues[2][2]);
 692     printf(" Min All            \t%lld\t%lld\t%lld\n",timevalues[3][0],timevalues[3][1],timevalues[3][2]);
 693     printf(" Min GrBy 1%                \t%lld\t%lld\t%lld\n",timevalues[4][0],timevalues[4][1],timevalues[4][2]);
 694     printf(" Max All            \t%lld\t%lld\t%lld\n",timevalues[5][0],timevalues[5][1],timevalues[5][2]);
 695     printf(" Max GrBy 1         \t%lld\t%lld\t%lld\n",timevalues[6][0],timevalues[6][1],timevalues[6][2]);
 696     printf(" Sum All            \t%lld\t%lld\t%lld\n",timevalues[7][0],timevalues[7][1],timevalues[7][2]);
 697     printf(" Sum GrBy 1%                \t%lld\t%lld\t%lld\n",timevalues[8][0],timevalues[8][1],timevalues[8][2]);
 698     printf(" Distinct 1%                \t%lld\t%lld\t%lld\n",timevalues[9][0],timevalues[9][1],timevalues[9][2]);
 699     printf(" Order By 1%                \t%lld\t%lld\t%lld\n",timevalues[10][0],timevalues[10][1],timevalues[10][2]);
 700     printf(" Insert 1           \t%lld\t%lld\t%lld\n",timevalues[11][0],timevalues[11][1],timevalues[11][2]);
 701     printf(" Update 1           \t%lld\t%lld\t%lld\n",timevalues[12][0],timevalues[12][1],timevalues[12][2]);
 702     printf(" Delete 1           \t%lld\t%lld\t%lld\n",timevalues[13][0],timevalues[13][1],timevalues[13][2]);
 703     printf(" Join(10K*1K)1              \t%lld\t%lld\t%lld\n",timevalues[14][0],timevalues[14][1],timevalues[14][2]);
 704     printf(" Join(10K*10K*1K)   \t%lld\t%lld\t%lld\n",timevalues[15][0],timevalues[15][1],timevalues[15][2]);
 705     printf(" Join(10K*1K)100    \t%lld\t%lld\t%lld\n",timevalues[16][0],timevalues[16][1],timevalues[16][2]);
 706     printf(" Join(10K*10K*1K)100        \t%lld\t%lld\t%lld\n",timevalues[17][0],timevalues[17][1],timevalues[17][2]);
 707
 708
 709     printf("*******************************************************\n");
 710
 711 }
 712
 713 int main (int ac, char **av)
 714 {
 715   int rc;
 716   SQLHENV henv;
 717   SQLHDBC hdbc;
 718   SQLHSTMT hstmt;
 719   mysql=false;
 720   if (ac ==2) mysql = true;
 721   //printf("Mysql Set %d \n", ac); return 0;
 722   rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
 723   checkrc (rc, __LINE__);
 724   SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
 725
 726   rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
 727   checkrc (rc, __LINE__);
 728   if (!mysql) {
 729       rc = SQLConnect (hdbc,
 730                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
 731                    (SQLCHAR *) "root",
 732                    (SQLSMALLINT) strlen ("root"),
 733                    (SQLCHAR *) "manager",
 734                    (SQLSMALLINT) strlen (""));
 735   }else {
 736
 737       SQLCHAR outstr[1024];
 738       SQLSMALLINT outstrlen;
 739       rc = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)"DSN=myodbc3;", SQL_NTS,
 740                          outstr, sizeof(outstr), &outstrlen,
 741                          SQL_DRIVER_NOPROMPT);
 742   }
 743
 744
 745   if (SQL_SUCCEEDED(rc)) {
 746     printf("Connected\n");
 747     //printf("Returned connection string was:\n\t%s\n", outstr);
 748     if (rc == SQL_SUCCESS_WITH_INFO) {
 749       printf("Driver reported the following diagnostics\n");
 750       extract_error("SQLDriverConnect", hdbc, SQL_HANDLE_DBC);
 751     }
 752   } else {
 753     fprintf(stderr, "Failed to connect\n");
 754     extract_error("SQLDriverConnect", hdbc, SQL_HANDLE_DBC);
 755   }
 756
 757   //check_error (SQL_HANDLE_DBC, hdbc, rc, __LINE__);
 758   checkrc (rc, __LINE__);
 759   rc = SQLSetConnectOption (hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
 760   checkrc (rc, __LINE__);
 761   rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
 762   checkrc (rc, __LINE__);
 763   int ins=1,upd=2,del=3;
 764   int min=1, max=2, sum=3;
 765
 766   timevalues[0][0] = runReadTest (henv, hdbc, hstmt,true);
 767   timevalues[1][0] = runReadTest (henv, hdbc, hstmt,false);
 768   timevalues[2][0] = onePerSel(henv, hdbc, hstmt);
 769   timevalues[3][0] = aggregate(henv, hdbc, hstmt,min,true);
 770   timevalues[4][0] = aggregate(henv, hdbc, hstmt,min,false);
 771   timevalues[5][0] = aggregate(henv, hdbc, hstmt,max,true);
 772   timevalues[6][0] = aggregate(henv, hdbc, hstmt,max,false);
 773   timevalues[7][0] = aggregate(henv, hdbc, hstmt,sum,true);
 774   timevalues[8][0] = aggregate(henv, hdbc, hstmt,sum,false);
 775   timevalues[9][0] = distinct(henv, hdbc, hstmt);
 776   timevalues[10][0] = orderBy(henv, hdbc, hstmt);
 777   timevalues[11][0] = dmlstatement(henv, hdbc, hstmt,ins);
 778   timevalues[12][0] = dmlstatement(henv, hdbc, hstmt,upd);
 779   timevalues[13][0] = dmlstatement(henv, hdbc, hstmt,del);
 780   timevalues[14][0] = joining(henv, hdbc, hstmt,1);
 781   timevalues[15][0] = joining(henv, hdbc, hstmt,2);
 782   timevalues[16][0] = joinCondition(henv, hdbc, hstmt,1);
 783   timevalues[17][0] = joinCondition(henv, hdbc, hstmt,2);
 784   printf("NON INDEX OVER\n");
 785   if (!mysql){
 786       rc = SQLExecDirect(hstmt, (SQLCHAR*) "FLUSH CACHE", SQL_NTS );
 787       checkrc (rc, __LINE__);
 788   }
 789
 790 //Hash
 791   createHashIndex( hstmt,true);
 792
 793   timevalues[0][1] = runReadTest (henv, hdbc, hstmt,true);
 794   timevalues[1][1] = runReadTest (henv, hdbc, hstmt,false);
 795   timevalues[2][1] = onePerSel(henv, hdbc, hstmt);
 796   timevalues[3][1] = aggregate(henv, hdbc, hstmt,min,true);
 797   timevalues[4][1] = 0;
 798   timevalues[5][1] = 0;
 799   timevalues[6][1] = 0;
 800   timevalues[7][1] = 0;
 801   timevalues[8][1] = 0;
 802   timevalues[9][1] = distinct(henv, hdbc, hstmt);
 803   timevalues[10][1] = orderBy(henv, hdbc, hstmt);
 804   timevalues[11][1] = dmlstatement(henv, hdbc, hstmt,ins);
 805   timevalues[12][1] = dmlstatement(henv, hdbc, hstmt,upd);
 806   timevalues[13][1] = dmlstatement(henv, hdbc, hstmt,del);
 807   timevalues[14][1] = joining(henv, hdbc, hstmt,1);
 808   timevalues[15][1] = joining(henv, hdbc, hstmt,2);
 809
 810   timevalues[16][1] = joinCondition(henv, hdbc, hstmt,1);
 811   timevalues[17][1] = joinCondition(henv, hdbc, hstmt,2);
 812   dropHashIndex(hstmt,true);
 813   printf("HASH INDEX OVER\n");
 814   //printTime();
 815
 816
 817   if (!mysql){
 818       rc = SQLExecDirect(hstmt, (SQLCHAR*) "FLUSH CACHE", SQL_NTS );
 819       checkrc (rc, __LINE__);
 820   }
 821
 822
 823 //TREE
 824   createTreeIndex(hstmt,true);
 825   timevalues[0][2] = runReadTest (henv, hdbc, hstmt,true);
 826   timevalues[1][2] = runReadTest (henv, hdbc, hstmt,false);
 827   timevalues[2][2] = onePerSel(henv, hdbc, hstmt);
 828   timevalues[3][2] = aggregate(henv, hdbc, hstmt,min,true);
 829   timevalues[4][2] = 0;
 830   timevalues[5][2] = 0;
 831   timevalues[6][2] = 0;
 832   timevalues[7][2] = 0;
 833   timevalues[8][2] = 0;
 834   timevalues[9][2] = distinct(henv, hdbc, hstmt);
 835   timevalues[10][2] = orderBy(henv, hdbc, hstmt);
 836   timevalues[11][2] = dmlstatement(henv, hdbc, hstmt,ins);
 837   timevalues[12][2] = dmlstatement(henv, hdbc, hstmt,upd);
 838   timevalues[13][2] = dmlstatement(henv, hdbc, hstmt,del);
 839   timevalues[14][2] = joining(henv, hdbc, hstmt,1);
 840   timevalues[15][2] = joining(henv, hdbc, hstmt,2);
 841
 842   timevalues[16][2] = joinCondition(henv, hdbc, hstmt,1);
 843   timevalues[17][2] = joinCondition(henv, hdbc, hstmt,2);
 844
 845   dropTreeIndex(hstmt,true);
 846   printf("TREE INDEX OVER\n");
 847
 848
 849   //printTime
 850   printTime();
 851   rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT );
 852   checkrc (rc, __LINE__);
 853
 854   rc = SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 855   checkrc (rc, __LINE__);
 856   rc = SQLDisconnect (hdbc);
 857   checkrc (rc, __LINE__);
 858   rc = SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 859   checkrc (rc, __LINE__);
 860   rc = SQLFreeHandle (SQL_HANDLE_ENV, henv);
 861   checkrc (rc, __LINE__);
 862   exit (0);
 863 }
 864
 865
 866