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
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;
}
}
......
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')
Could anyone give some clues about tuning Vertica ODBC driver performance? Thanks very much in advance!