title: Static vs non-static prepared statment in SQLite C API
body: <p>I am working on a terminal app in C and SQL for tagging files. The file/tag relationships are saved in an SQLite 3 database. Up until now I've been using statically allocated prepared statements, which are reset and bound on every call to the wrapper functions. Recently I was testing for memory leaks etc with <code>valgrind</code>, and it was complaining a lot because obviously the static <code>sqlite3_stmt</code> in the wrappers are not freed upon program termination. This also causes the database to not be closed properly (because of non-finalized statements).</p>
  
  <p>The original reason for doing this is performance. I read in <a href="http://www.sqlite.org/cintro.html" rel="nofollow">the C interface intro</a> that reusing prepared statements is very good for performance.</p>
  
  <blockquote>
    <p>Using sqlite3_reset() on an existing prepared statement rather than creating a new prepared statement avoids unnecessary calls to sqlite3_prepare(). In many SQL statements, the time needed to run sqlite3_prepare() equals or exceeds the time needed by sqlite3_step(). So avoiding calls to sqlite3_prepare() can result in a significant performance improvement.</p>
  </blockquote>
  
  <p>Here's some code to show what I'm doing:</p>
  
  <pre class="lang-c prettyprint-override"><code>int tag_file(const char *file, const char *tag)
  {
      static sqlite3_stmt *sql_prep = NULL;
      static const char *sql_str = "INSERT OR IGNORE INTO Tag VALUES (?, ?);";
  
      // Prepare if null, else reset
      prepare_or_reset(&amp;sql_prep, sql_str);
  
      if (sqlite3_bind_text(sql_prep, 1, file, -1, SQLITE_STATIC) != SQLITE_OK ||
          sqlite3_bind_text(sql_prep, 2, tag, -1, SQLITE_STATIC) != SQLITE_OK)
          return ERROR;
  
      if (sqlite3_step(sql_prep) != SQLITE_DONE)
          return ERROR;
  
      return SUCCESS;
  }
  </code></pre>
  
  <p>What I tried, which will satisfy <code>valgrind</code>, but completely misses the performance gain of reusing the prepared statement is to make <code>sql_prep</code> non-static and calling <code>sqlite3_finalize(sql_prep)</code> at the end of the function, but I guess this is worse performance wise. Can I prepare the statement just once without a memory leak in an elegant way?</p>
  
  <p>Another thing I started worrying about was memory consumption. In the future I am planning to create a GUI for this app, which would keep all this in memory much longer. The performance gain could potentially be bigger, but that statement would just be sitting there on the heap from the first call to exit. Is this a fair <a href="http://en.wikipedia.org/wiki/Space%E2%80%93time_tradeoff" rel="nofollow">space-time tradoff</a>?</p>
  
  <p><em>Edit:</em> Would it be ugly/weird to keep all "static" prepared statements in a global array, which I can free <code>atexit</code>?.</p>