How to tune Vertica ODBC driver performance?

Moderator: NorbertKrupa

Post Reply
nanxiao
Newbie
Newbie
Posts: 1
Joined: Thu Nov 27, 2014 8:07 am

How to tune Vertica ODBC driver performance?

Post by nanxiao » Tue Dec 02, 2014 6:34 am

Hi all,

I first post this issue at stackoverflow(http://stackoverflow.com/questions/2724 ... erformance), but unfortunately, I can’t get helpful answers there. So I repost this issue here, thanks!

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:

Code: Select all

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:

Code: Select all

......
#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:

Code: Select all

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!

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: How to tune Vertica ODBC driver performance?

Post by NorbertKrupa » Tue Dec 02, 2014 3:22 pm

It looks like you accepted Guillaume's answer.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “ODBC”