c - How to tune Vertica ODBC driver performance? -

i using vertica odbc driver (the newest 7.1.1 version), , want test performance.

after referring materials, configure following options in odbc.ini:

transactionisolation = read committed autocommit = 0   

the application spawns 20 thread, , every thread 1000 insert operations. every thread, commit once 20 insert operations. code like:

...... #define loop_count (1000) #define commit_count (20)  (i = 0; < 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 test(name, city) values('nan', 'nanjing')");     (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 test operation frustrating: client , server run on same machine, , whole operation take 55 seconds. same operation take less 1 second mysql(client , server run on different machines , auto commit off).

after checking vertica odbc log, find following logs:

dec 02 09:41:26 info  3747604224 vdataengine::prepare: original query: insert test(name, city) values('nan', 'nanjing') dec 02 09:41:26 info  3747604224 vdataengine::prepare: query issued execdirect dec 02 09:41:26 info  3747604224 vdataengine::prepare: query: insert 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 streaming: copy public.test (  name 'nan', city 'nanjing' ) local stdin native varchar enforcelength returnrejected auto no commit dec 02 09:41:27 info  3747604224 statementstate::internalprepare: preparing query: insert test(name, city) values('nan' , 'nanjing') dec 02 09:41:27 info  3747604224 vdataengine::prepare: original query: insert test(name, city) values('nan', 'nanjing') dec 02 09:41:27 info  3747604224 vdataengine::prepare: query issued execdirect dec 02 09:41:27 info  3747604224 vdataengine::prepare: query: insert 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 streaming: copy public.test (  name 'nan', city 'nanjing' ) local stdin native varchar enforcelength returnrejected auto no commit dec 02 09:41:27 info  3747604224 statementstate::internalprepare: preparing query: insert test(name, city) values('nan' , 'nanjing') 

i doubt "vqueryexecutor::initializepreparedexecutor: can't promote streaming" slow operation, after googling, not valuable information can found.

could give clues tuning vertica odbc driver performance? in advance!

your bad performance have nothing odbc setup.

you want test same code vertica , mysql, 2 different kind of databases, , cannot efficiently used same way same use case.

vertica meant have few (10s) of connection in parallel, doing lot of work each, instance batch-loading lot of data, typical analytics loads.

mysql meant have lot (100s, 1000s) of connections in parallel, doing not (single insert, single select), typical oltp loads.

that said, vertica sql compliant , statement work, albeit noticed. if use copy statement instead of looping through inserts see huge difference. then, 1000 rows not much. connection setup vertica slower mysql, , if load 50k or 100k rows become obvious strength of vertica compared mysql.