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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users