How to tune Vertica ODBC driver performance?

Question

I am using Vertica ODBC driver (the newest 7.1.1 version), and want to test its performance.

After referring some materials, I configure the following options in odbc.ini:

TransactionIsolation = Read Committed
AutoCommit = 0  

The application spawns 20 thread, and every thread will do 1000 insert operations. For every thread, it will commit once for 20 insert operations. The code is like:

......
#define LOOP_COUNT (1000)
#define COMMIT_COUNT (20)

for (i = 0; i < LOOP_COUNT / COMMIT_COUNT; i++)
{
    ret = SQLAllocHandle(SQL_HANDLE_STMT, conn_handle, &stmt_handle);
    if (!SQL_SUCCEEDED(ret))
    {
        printf("Allocate statement handle failed\n");
        goto TEST_THREAD_END; 
    }

    snprintf(sql, sizeof(sql), "insert into test(name, city) values('Nan', 'Nanjing')");
    for (j = 0; j < COMMIT_COUNT; j++)
    {
        ret = SQLExecDirect(stmt_handle, (SQLCHAR*)sql, SQL_NTS);
        if (!SQL_SUCCEEDED(ret))
        {
            printf("Execute statement failed\n");
            goto TEST_THREAD_END; 
        }
    }

    SQLEndTran(SQL_HANDLE_DBC, conn_handle, SQL_COMMIT);

    ret = SQLFreeHandle(SQL_HANDLE_STMT, stmt_handle);
    if (!SQL_SUCCEEDED(ret))
    {
        printf("Free statement handle failed\n");
        goto TEST_THREAD_END; 
    }
}
......

But the test operation is very frustrating: the client and server run on the same machine, and the whole operation will take about 55 seconds. The same operation will take less than 1 second for MySQL(client and server run on different machines and auto commit is off).

After checking vertica ODBC log, I find the following logs:

Dec 02 09:41:26 INFO  3747604224 VDataEngine::Prepare: Original Query: insert into test(name, city) values('Nan', 'Nanjing')
Dec 02 09:41:26 INFO  3747604224 VDataEngine::Prepare: Query is issued as ExecDirect
Dec 02 09:41:26 INFO  3747604224 VDataEngine::Prepare: Query: insert into test(name, city) values('Nan', 'Nanjing')
Dec 02 09:41:26 INFO  3747604224 VDataEngine::Prepare: Initializing prepared statement: _PLAN0x7f7694019de0_0
Dec 02 09:41:26 INFO  3747604224 VQueryExecutor::InitializePreparedExecutor: can't promote to streaming: COPY public.test ( 
name AS 'Nan', city AS 'Nanjing' ) FROM LOCAL STDIN NATIVE VARCHAR ENFORCELENGTH RETURNREJECTED AUTO NO COMMIT
Dec 02 09:41:27 INFO  3747604224 StatementState::InternalPrepare: Preparing query: insert into test(name, city) values('Nan'
, 'Nanjing')
Dec 02 09:41:27 INFO  3747604224 VDataEngine::Prepare: Original Query: insert into test(name, city) values('Nan', 'Nanjing')
Dec 02 09:41:27 INFO  3747604224 VDataEngine::Prepare: Query is issued as ExecDirect
Dec 02 09:41:27 INFO  3747604224 VDataEngine::Prepare: Query: insert into test(name, city) values('Nan', 'Nanjing')
Dec 02 09:41:27 INFO  3747604224 VDataEngine::Prepare: Initializing prepared statement: _PLAN0x7f7694028890_1
Dec 02 09:41:27 INFO  3747604224 VQueryExecutor::InitializePreparedExecutor: can't promote to streaming: COPY public.test ( 
name AS 'Nan', city AS 'Nanjing' ) FROM LOCAL STDIN NATIVE VARCHAR ENFORCELENGTH RETURNREJECTED AUTO NO COMMIT
Dec 02 09:41:27 INFO  3747604224 StatementState::InternalPrepare: Preparing query: insert into test(name, city) values('Nan'
, 'Nanjing')

I doubt the "VQueryExecutor::InitializePreparedExecutor: can't promote to streaming" will slow the operation, but after googling, not any valuable information can be found.

Could anyone give some clues about tuning Vertica ODBC driver performance? Thanks very much in advance!

Answer 1

Your bad performance have probably nothing to do with your ODBC setup.

You say you want to test the same code with Vertica and Mysql, but they are 2 very different kind of databases, and cannot be efficiently used the same way for the same use case.

Vertica is meant to have a few (10s) of connection in parallel, doing a lot of work each, as for instance batch-loading a lot of data, typical analytics loads.

Mysql is meant to have a lot (100s, 1000s) of connections in parallel, doing not much (single INSERT, single SELECT), typical OLTP loads.

That said, Vertica is SQL compliant and your statement will work, albeit slowly as you noticed. If you were to use a COPY statement instead of looping through INSERTs you would see a huge difference. Even then, 1000 rows is not much. Connection setup with Vertica is slower than mysql, and if you were to load say 50k or 100k rows then it would become obvious what the strength of Vertica is compared to mysql.

Answer 2

I have no experience with Vertica but you C code and use of the ODBC API is inefficient. You construct your insert statement for every insert and that will cause the database to parse it each time. A better strategy is to prepare (SQLPrepare) the insert once like this "insert into test(name, city) values(?,?)" then to bind the parameters (SQLBindParam) and lastly (in the loop) to call SQLExecute. Also, your wasting time allocating and freeing a statement handle.