Thanks both of you for replying for my questions
Fair enough. I was vague enough to deserve the comments.
Here are more details and the code which I believe pretty straightforward
We are trying to send maximum number of pairs, (one integer and one text) in one transaction. ( FVIdx, hashkey are the corresponding names of the item in the pair in the following code section)
We use the SQLITE_LIMIT_VARIABLE_NUMBER (=32768 in our case) to create the INSERT command as following. We end up with 16383 rows.
In this case, the prepare call takes 10-20 secs depending on the machine ( we tried 5+ different machines BTW)
// 16383 rows
size_t keyRowsPerInsert = (size_t) (sqlite3_limit( pDB, SQLITE_LIMIT_VARIABLE_NUMBER, -1) / 2);
// INSERT command string , Note that KEYTABLE is the name of the table
const char *tuple_sep = "";
stringstream keySqlStream;
keySqlStream << "INSERT INTO KEYTABLE (FVIdx,hashkey) VALUES ";
for (size_t idx = 0; idx < keyRowsPerInsert; ++idx) {
keySqlStream << tuple_sep << "(@J" << idx << ", @KEY" << idx << ")";
tuple_sep = ",";
}
keySqlStream << ";";
std::string keySqlStr = keySqlStream.str();
// prepare the statement
sqlite3_prepare_v3(pDB, keySqlStr.c_str(), -1, SQLITE_PREPARE_PERSISTENT, &feat_stmt, NULL);