unixODBC Programming - improve performance using multiple statements

 ODBC programming generally has the following structure.


And the SQL query statement is processed using the statement handle.
However, if there are many sql statements to be processed, the above structure should use one statement handle to process the work sequentially. However, RDBMS such as MySQL, MariaDB, MS-SQL, and DB2 are parallel structures that process multiple commands at the same time. Therefore, it is advantageous to create multiple statement handles and process them simultaneously.

<parallel processing>


Determining whether the RDBMS supports parallelism

ODBC SDK provides an API to know the number of statement handles that RDBMS can support simultaneously. You can use the SQLGetInfo function.

    SQLUSMALLINT    max_concur_act = -1;
    rc1 = SQLGetInfo(dbc,  SQL_MAX_CONCURRENT_ACTIVITIES , &max_concur_act, 0,0);
    if (SQL_SUCCEEDED(rc1)) {
        if(0 == max_concur_act) printf("SQL_MAX_CONCURRENT unlimited or undefined\n");
        else printf("SQL_MAX_CONCURRENT:%d\n",max_concur_act);
    }

If the return value is 0, there is usually no special limit. Returns 0 for MySQL and MariaDB. If you are using another RDBMS, you can check it yourself using this function.


performance test


Single statement

The following is a test code that repeats 64,000 query statements using one statement.

#include <stdio.h>
#include <string.h>
#include <string>
#include <iostream>
#include <sql.h>
#include <sqlext.h>
#include <time.h>

using namespace std;

void debug_err(SQLRETURN rc, SQLHSTMT stmt){
    SQLLEN numRecs = 0;
    SQLSMALLINT   i, MsgLen;
    SQLRETURN     rc2; 
    SQLCHAR       SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER    NativeError;  
    
    SQLGetDiagField(SQL_HANDLE_STMT, stmt, 0, SQL_DIAG_NUMBER, &numRecs, 0, 0);
    i = 1;  
    while (i <= numRecs && (rc2 = SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i, SqlState, &NativeError,  
            Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {  
        printf("SQLSTATE[%s] NATIVE ERROR[%d] Msg[%s]\n",SqlState,NativeError,Msg);
        i++;  
    }         
}

int main( int argc, char** argv ) {
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN ret; /* ODBC API return status */
    SQLSMALLINT columns; /* number of columns in result-set */
    int row = 0;
    char sql[1024] = "";
    SQLRETURN rc1;
    string conn("DSN=myodbc");

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

    rc1 = SQLDriverConnect(dbc, NULL, (SQLCHAR*)conn.c_str(), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    if(SQL_SUCCESS != rc1){
        printf("SQLDriverConnect return [%d]\n", rc1);
    }
    SQLUSMALLINT    max_concur_act = -1;
    rc1 = SQLGetInfo(dbc,  SQL_MAX_CONCURRENT_ACTIVITIES , &max_concur_act, 0,0);
    if (SQL_SUCCEEDED(rc1)) {
        if(0 == max_concur_act) printf("SQL_MAX_CONCURRENT unlimited or undefined\n");
        else printf("SQL_MAX_CONCURRENT:%d\n",max_concur_act);
    }     
    
    /* Allocate a statement handle */
    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    /* execute a query */
    sprintf(sql, "select belong, phone from professor");
    int count = 64 * 1000;

    double diff;
    struct tm file_tm{};
    time_t now, end;
    time(&now);
    
    for(int x = 0; x < count; x++){
        rc1 = SQLExecDirect(stmt, (SQLCHAR*)sql, SQL_NTS);
        if(SQL_SUCCESS != rc1 && SQL_SUCCESS_WITH_INFO != rc1){
            debug_err(rc1, stmt);
            exit(0);
        }
        char belong[16], phone[16];
        SQLBindCol(stmt, 1, SQL_C_CHAR,  belong, 16, NULL);
        SQLBindCol(stmt, 2, SQL_C_CHAR,  phone, 16, NULL);


        for ( int i = 0; ; i++ ) {
            rc1 = SQLFetch( stmt );
            if ( rc1 == SQL_ERROR || rc1 != SQL_SUCCESS_WITH_INFO )
                debug_err(rc1, stmt);
            if ( rc1 == SQL_SUCCESS || rc1 == SQL_SUCCESS_WITH_INFO ) {
                cout << "Record [" << i + 1 << "]  ";
                cout << " belong : " << belong;
                cout << " \tphone : " << phone;
                cout << "\n";
            }
            else
                break;
        }
    }
    time(&end);
   
    rc1 = SQLFreeHandle( SQL_HANDLE_STMT, stmt );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_STMT)err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLDisconnect( dbc );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLDisconnect err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLFreeHandle( SQL_HANDLE_DBC, dbc );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_DBC)err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLFreeHandle( SQL_HANDLE_ENV, env );    
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_ENV)err:"<< rc1 << endl;
        exit(0);
    } 
    cout << "basic odbc test end" << endl;    
    diff = difftime(end, now);
    cout <<"Elapsed time:" <<  diff <<endl;
    return 0;
}

<basic_perf.cpp>

Let's compile and run the program.

[root@localhost odbc]# g++ basic_perf.cpp -lodbc -o basic_perf
[root@localhost odbc]# ./basic_perf
......
......
......
Record [1]   belong : IDE       phone : 01112345678
Record [2]   belong : MSE       phone : 01121342443
Record [3]   belong : ESE       phone : 01123424343
Record [4]   belong : IME       phone : 08200003333
Record [5]   belong : NBA       phone : 01023452222
Record [6]   belong : IME       phone : 01134343222
Record [7]   belong : IDE       phone : 01123432432
basic odbc test end
Elapsed time:38

It took about 38 seconds to process 64000 queries.

This time, we will create multiple statement handles for parallel processing.


Multiple statements

This time, we will create 64 statement handles for 64000 query statements and process them simultaneously in 64 threads.

#include <stdio.h>
#include <string.h>
#include <string>
#include <iostream>
#include <sql.h>
#include <sqlext.h>
#include <time.h>
#include <pthread.h>

using namespace std;

SQLHDBC dbc;

void debug_err(SQLRETURN rc, SQLHSTMT stmt){
    SQLLEN numRecs = 0;
    SQLSMALLINT   i, MsgLen;
    SQLRETURN     rc2; 
    SQLCHAR       SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER    NativeError;  
    
    SQLGetDiagField(SQL_HANDLE_STMT, stmt, 0, SQL_DIAG_NUMBER, &numRecs, 0, 0);
    i = 1;  
    while (i <= numRecs && (rc2 = SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i, SqlState, &NativeError,  
            Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {  
        printf("SQLSTATE[%s] NATIVE ERROR[%d] Msg[%s]\n",SqlState,NativeError,Msg);
        i++;  
    }         
}

void *statement_thread(void *data)
{
    char sql[1024] = "";
    SQLRETURN rc1;

    SQLHSTMT stmt;

    /* Allocate a statement handle */
    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    /* execute a query */
    sprintf(sql, "select belong, phone from professor");
    for(int x = 0; x < 100; x++){
        rc1 = SQLExecDirect(stmt, (SQLCHAR*)sql, SQL_NTS);
        if(SQL_SUCCESS != rc1 && SQL_SUCCESS_WITH_INFO != rc1){
            debug_err(rc1, stmt);
            exit(0);
        }
        char belong[16], phone[16];
        SQLBindCol(stmt, 1, SQL_C_CHAR,  belong, 16, NULL);
        SQLBindCol(stmt, 2, SQL_C_CHAR,  phone, 16, NULL);


        for ( int i = 0; ; i++ ) {
            rc1 = SQLFetch( stmt );
            if ( rc1 == SQL_ERROR || rc1 != SQL_SUCCESS_WITH_INFO )
                debug_err(rc1, stmt);
            if ( rc1 == SQL_SUCCESS || rc1 == SQL_SUCCESS_WITH_INFO ) {
                cout << "Record [" << i + 1 << "]  ";
                cout << " belong : " << belong;
                cout << " \tphone : " << phone;
                cout << "\n";
            }
            else
                break;
        }
    }

    rc1 = SQLFreeHandle( SQL_HANDLE_STMT, stmt );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_STMT)err:"<< rc1 << endl;
    } 
    return NULL;
}


int main( int argc, char** argv ) {
    SQLHENV env;
    SQLRETURN ret; /* ODBC API return status */
    SQLSMALLINT columns; /* number of columns in result-set */
    int row = 0;
    SQLRETURN rc1;
    string conn("DSN=myodbc");

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

    rc1 = SQLDriverConnect(dbc, NULL, (SQLCHAR*)conn.c_str(), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    if(SQL_SUCCESS != rc1){
        printf("SQLDriverConnect return [%d]\n", rc1);
    }
    SQLUSMALLINT    max_concur_act = -1;
    rc1 = SQLGetInfo(dbc,  SQL_MAX_CONCURRENT_ACTIVITIES , &max_concur_act, 0,0);
    if (SQL_SUCCEEDED(rc1)) {
        if(0 == max_concur_act) printf("SQL_MAX_CONCURRENT unlimited or undefined\n");
        else printf("SQL_MAX_CONCURRENT:%d\n",max_concur_act);
    }     
    
    int count = 64 * 1000;

    double diff;
    struct tm file_tm{};
    time_t now, end;
    time(&now);
    
    pthread_t p_thread[64];
    for(int x = 0; x < 64; x++){
        pthread_create(&p_thread[x], NULL, statement_thread, NULL);
    }
    
    for(int x = 0; x < 64; x++){
        pthread_join(p_thread[x], NULL);
    }
    
    time(&end);
   
    rc1 = SQLDisconnect( dbc );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLDisconnect err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLFreeHandle( SQL_HANDLE_DBC, dbc );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_DBC)err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLFreeHandle( SQL_HANDLE_ENV, env );    
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_ENV)err:"<< rc1 << endl;
        exit(0);
    } 
    cout << "basic odbc test end" << endl;    
    diff = difftime(end, now);
    cout <<"Elapsed time:" <<  diff <<endl;
    return 0;
}

<multi_perf.cpp>


Let's compile and run the program.

[root@localhost odbc]# g++ multi_perf.cpp -lodbc -o multi_perf
[root@localhost odbc]# ./multi_perf
......
......
......
Record [1]   belong : IDE       phone : 01112345678
Record [2]   belong : MSE       phone : 01121342443
Record [3]   belong : ESE       phone : 01123424343
Record [4]   belong : IME       phone : 08200003333
Record [5]   belong : NBA       phone : 01023452222
Record [6]   belong : IME       phone : 01134343222
Record [7]   belong : IDE       phone : 01123432432
basic odbc test end
Elapsed time:3

It took about 3 seconds to process 64000 queries. A speed improvement of more than 10 times was achieved.

The speed improvement could change more dramatically with the following factors:

  • The number of CPU cores, memories on the server on which the RDBMS is running.
  • network bandwidth
  • The number of CPU cores, memories of the client PC that executes the query statement


Wrapping up

If you need to execute a large number of queries at the same time, you can get much better performance by creating several statements at the same time and then executing the code using multi-threading.

You can download source codes at C-Python-Cooking .


댓글

댓글 쓰기

이 블로그의 인기 게시물

MQTT - C/C++ Client

이미지
  This article records how to implement the client directly using c/c++ , which is linked to the Mosquito MQTT broker set-up on the Ubuntu 20.04, introduced in MQTTT - Mosquito MQT broker set-up on the Ubuntu 20.04 .   Install Paho MQTT C Client  To use MQTT on c/c++, first download the required module from the eclipse foundation. You can download files for your operating system from https://www.eclipse.org/paho/index.php?page=clients/c/index.php . <Paho MQTT C Client download page> Of course I use Linux, so I'll refer to the build contents for Linux in the middle of the document for "Building from Source." apt-get install openssl libssl-dev  cd / usr / local / src git clone https :// github . com / eclipse / paho . mqtt . c . git cd paho . mqtt . c . git make && sudo make install  Now we are ready to develop mqtt client using c/c++.   Build Example  We will proceed with the example of mqttt as described in MQTT - Mosquito MQTT broke...
자세한 내용 보기

RabbitMQ - C++ Client #1 : Installing C/C++ Libraries

이미지
 This article documents how to connect to RabbitMQ using c/c++ to publish and subscribe to messages. I'm working on Ubuntu 20.04. RabbitMQ supports various development languages. Java, Python, C/C++, C#, Ruby, Javascript, as well as recently released languages such as Go and Rust are supported. How to use various development languages is introduced in detail at https://www.rabbitmq.com/devtools.html . Those who use C/C++ can also get download information for some Client tools on this page. <Development tool introduction page> Install SimpleAmqpClient In the middle of this page there is an introduction to tools for C/C++ developers. C and C++ RabbitMQ C client SimpleAmqpClient , a C++ wrapper around rabbitmq-c amqpcpp , a C++ message library for RabbitMQ AMQP-CPP , a C++ RabbitMQ client I'll use SimpleAmqpClient here. The reason for using SimpleAmqpClient is that the API is very concise. RabbitMQ has the advantage of being easy to port when porting Python code becaus...
자세한 내용 보기

C/C++ - Everything about time, date

 Important data types  time_t Although not defined by the C standard, this is almost always an integral value holding the number of seconds (not counting leap seconds) since 00:00, Jan 1 1970 UTC, corresponding to POSIX time.  Unix and POSIX systems define time_t as an integer (usually a 32-bit or 64-bit integer) or a floating-point type. Note : Time_t can only be handled in seconds, but not in milliseconds or microsec.  struct tm The tm structure has the following structure. struct tm { int tm_sec; int tm_min; int tm_hour; int tm_mday; int tm_mon; int tm_year; int tm_wday; int tm_yday; int tm_isdst; }; time_t is a variable that stores seconds that have elapsed since 1970. It is often used to convert from this variable to the easy-to-use year, month, day, hour, minute, and second formats. struct timeval The time_t discussed above has only a value in seconds. Time_t cannot be used if ms (0.001sec) and us (0.000001sec) value...
자세한 내용 보기