The issue is: one of requests performed in my app was executed in ~7
seconds in v.3.10, but in v.3.14 execution time is ~13-15 seconds. That's
really itching in my case.
The sql request in question includes "inner join" constructs.
I also remember that in version prior to 3.10 the execution time for that
particular request was much larger than 10 seconds.
The full code of the routine is below. I can put on share somewhere a copy
of the db which is used (it is not a secret data, but it is a little large
- about 290 MB).
I would appreciate any hint or advice on any detail of the implementation.
============================================
int ClippedUpdatesData::prepareClippedUpdates_sqlite(cMem& m_memUpdtReqSrv,
int
exSetID,
CComQIPtr<ICrypt, &IID_ICrypt>& m_pICrypt,
cMem&
m_memBufNames3,
cMem&
m_memBufData,
long
m_MaxLengthData,
const char*
m_path)
{
int processedFiles = 0;
const std::string sql_createTempTable = "CREATE TEMP TABLE temp.trtu
(name TEXT, edition INTEGER, update_no INTEGER, pos INTEGER)";
const std::string sql_dropTempTable = "DROP TABLE temp.trtu";
const std::string sql_insertTemp = "INSERT INTO temp.trtu VALUES
(@name, @edition, @update_no, @pos)";
int dbResult;
sqlite3_stmt *pStmt;
if(sqlite3_prepare_v2(m_sqlite_pDb, sql_createTempTable.c_str(),
sql_createTempTable.length(), &pStmt, NULL) != SQLITE_OK) {
throw std::runtime_error(sqlite3_errmsg(m_sqlite_pDb));
}
dbResult = sqlite3_step(pStmt);
sqlite3_finalize(pStmt);
sqlite3_exec(m_sqlite_pDb, "BEGIN TRANSACTION", NULL, NULL, NULL);
if(sqlite3_prepare_v2(m_sqlite_pDb, sql_insertTemp.c_str(),
sql_insertTemp.length(), &pStmt, NULL) != SQLITE_OK) {
throw std::runtime_error(sqlite3_errmsg(m_sqlite_pDb));
}
LOG4CPLUS_DEBUG(m_logger, "prepareClippedUpdates_sqlite - fill
temporary table");
int tempDataCounter = 0;
int nDatasets = m_memUpdtReqSrv.GetStructNumbers();
UPDATE_REC_SRV *aDataset = (UPDATE_REC_SRV
*)m_memUpdtReqSrv.GetStructPointer();
for (int i = 0; i < nDatasets; ++i) {
const UPDATE_REC_SRV& theDataset = aDataset[i];
if (theDataset.numbUpdt != 0) {
tempDataCounter += 1;
sqlite3_bind_text(pStmt, 1, theDataset.name, 8, SQLITE_STATIC);
sqlite3_bind_int(pStmt, 2, theDataset.numbEdit);
sqlite3_bind_int(pStmt, 3, (theDataset.numbUpdt - 1));
sqlite3_bind_int(pStmt, 4, i);
dbResult = sqlite3_step(pStmt);
sqlite3_reset(pStmt);
}
}
sqlite3_exec(m_sqlite_pDb, "END TRANSACTION", NULL, NULL, NULL);
sqlite3_finalize(pStmt);
if(tempDataCounter > 0) {
LOG4CPLUS_DEBUG(m_logger, "getting datasets info from the DB...");
char szSql[1024];
sprintf(szSql,
"SELECT cu.name name, cu.edition edition, cu.update_no
update_no, t.pos pos, cu.data data "
"FROM CLIPPED_UPDATE cu "
" INNER JOIN temp.trtu t ON cu.name=t.name AND
cu.edition=t.edition "
" INNER JOIN EXSET_DATASET ed ON ed.name=t.name AND
ed.edition=t.edition AND ed.coverage_id=cu.coverage_id "
" WHERE ed.exset_id=%d AND cu.update_no>t.update_no "
"ORDER BY name, edition, update_no", exSetID);
if(sqlite3_prepare_v2(m_sqlite_pDb, szSql, strlen(szSql), &pStmt,
NULL) != SQLITE_OK) {
throw std::runtime_error(sqlite3_errmsg(m_sqlite_pDb));
}
int nDataset = 0; // last processed dataset
while(true) {
dbResult = sqlite3_step(pStmt);
if(dbResult == SQLITE_ROW) {
int nEdition = sqlite3_column_int(pStmt, 1);
nDataset = sqlite3_column_int(pStmt, 3);
const void* blobBuf = sqlite3_column_blob(pStmt, 4);
int blobSize = sqlite3_column_bytes(pStmt, 4);
CComVariant vUpdate;
if((HRESULT)m_pICrypt->Decompress((BYTE*)blobBuf, blobSize,
&vUpdate.parray) < 0) {
throw std::runtime_error("'prepareClippedUpdates_mssql'
- crypt problem");
}
BEG_HEAD_UPDT_FILE *pBegHeadUpdt = (BEG_HEAD_UPDT_FILE
*)vUpdate.parray->pvData;
if (pBegHeadUpdt->nTotalNumberOfRecords == 1) {
// possible termination
if(pBegHeadUpdt->wEditionNumber != nEdition //
empty update
&& pBegHeadUpdt->wEditionNumber != nEdition + 1 //
new edition available
&& pBegHeadUpdt->wEditionNumber != 0 //
terminated
) {
throw
std::runtime_error("'prepareClippedUpdates_mssql' - data consistency
error");
}
} else {
if(pBegHeadUpdt->wEditionNumber != nEdition) {
throw
std::runtime_error("'prepareClippedUpdates_mssql' - data consistency
error");
}
}
// should always be version 3 (checked above)
HEAD_BLOCK_UPDATE_V3* p =
(HEAD_BLOCK_UPDATE_V3*)m_memBufNames3.Expand();
p->len = vUpdate.parray->rgsabound[0].cElements;
p->nmbFile = nDataset;
LPBYTE pBuf = (LPBYTE)m_memBufData.Expand(p->len);
memcpy(pBuf, vUpdate.parray->pvData, p->len);
if (m_memBufData.GetBufferSize() > m_MaxLengthData)
break;
} else if(dbResult == SQLITE_DONE) {
break;
} else {
std::stringstream ss;
ss << "Unexpected SQLite code: " << dbResult;
throw std::runtime_error(ss.str());
}
}
processedFiles = nDataset;
sqlite3_finalize(pStmt);
LOG4CPLUS_DEBUG(m_logger, "prepareClippedUpdates_sqlite - done, "
<< processedFiles);
}
if(sqlite3_prepare_v2(m_sqlite_pDb, sql_dropTempTable.c_str(),
sql_dropTempTable.length(), &pStmt, NULL) != SQLITE_OK) {
throw std::runtime_error(sqlite3_errmsg(m_sqlite_pDb));
}
sqlite3_step(pStmt);
sqlite3_finalize(pStmt);
return processedFiles;
}
============================================
--
LinkedIn: http://www.linkedin.com/pub/aleksey-smirnov/13/598/3b3
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
