SQLite Optimizations: Query Planning

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 *