How to tune Vertica ODBC driver performance?
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!
Comments
You program is doing single row insert for 20 times and then commit, prepare statement would take longer time.
Review the below link for more details about ODBC batch insert
http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/ProgrammersGuide/ClientODBC/UsingBatchInse...