SQLite Optimizations: Query Planning

If a query is running really slowly in SQLite immediately after you have performed a large number of inserts, the query optimizer may be out of sync. If you run the ANALYZE command, SQLite calculate a bunch of statistics about your tables which the query analyzer can then use to optimize further queries. It can be an expensive command to run, but you can more than make up for it with improved query performance afterwards.


COMMIT; BEGIN TRANSACTION;
ANALYZE;
COMMIT; BEGIN TRANSACTION;

If you want to check what the optimizer has calculated, you can check the query plan:


/** \brief Gets a query plan for debugging SQLite performance issues
\details Compiles a EXPLAIN QUERY PLAN command to report on the prepared statement
and returns the report. Modified from https://www.sqlite.org/eqp.html
*/
std::string explainQueryPlan() {
#ifdef _DEBUG
std::string buffer;

const char* sql = sqlite3_sql(m_stmt); //Input SQL
if(sql==0) { return "Error: SQLITE_ERROR: Cannot acquire SQL"; }
buffer += fmt("SQL: %s\nPlan:\n", sql);

const char* explainPlan = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", sql); //SQL with EXPLAIN QUERY PLAN prepended
if(explainPlan==0) { return "Error: SQLITE_NOMEM"; }

SQLite3Stmt explainStmt; //Compiled EXPLAIN QUERY PLAN command
int rc = sqlite3_prepare_v2(sqlite3_db_handle(m_stmt), explainPlan, -1, &explainStmt.m_stmt, 0);
sqlite3_free((void*)explainPlan);
if(rc!=SQLITE_OK) { return "Error: Failed to compile query plan"; };

while(explainStmt.step()==SQLITE_ROW){
int selectID = explainStmt.getColumnInt(0);
int order = explainStmt.getColumnInt(1);
int from = explainStmt.getColumnInt(2);
const char* details = explainStmt.getColumnText(3);

buffer += fmt("%d %d %d %s\n", selectID, order, from, details);
}

rc = explainStmt.finalize();
if(rc!=SQLITE_OK) { return "Error: Failed to finalize"; }
return buffer;
#else
return "Query Plan Not Available";
#endif
}

Leave a Reply

Your email address will not be published. Required fields are marked *

*