Many people have asked for the ability to precompile commonly
used SQL statements.  The idea is that if the same SQL statement
is reused two or more times, the compilation process can be skipped
for the second and subsequent uses of that statement, and thus
make the statement execute faster.

SQLite does not currently have the ability to precompile.  But
perhaps this will be added as a future enhancement.

How much of a speed advantage can be expected?  To test this, I
profiled version 2.8.1 on a script of 51000 INSERT statements.
When writing to an in-memory database, the execution of the statements
required about 36.3% of the time and the parsing and code
generation required 43.1% of the time.  The remaining 20.6%
was used to read the test script off of disk.
For a disk-based database, the numbers were 59.0% for execution,
33.3% for parsing and code generation, and 7.3% for reading
the script.  These measurements suggest a speed increase of
between 150% to 225% can be achieved by reusing a previously
compiled program.

The results above are for individual INSERT statements that
add a single row of data to the database.  For complex SELECT
statements or INSERT statements that take their data from a
SELECT, many rows of data are visited and the speed advantage
of precompiling is greatly reduced.

-----

22-July-2003:

There is now an *experimental* API in SQLite CVS (checkin [1060]) that 
can be used for pre-compiled queries:

  sqlite_reset( sqlite_vm *, const char **, sqlite_vm ** ppVm);

This call is exactly the same as sqlite_finalize() except a new
virtual machine is created and stored in *ppVm. The new virtual
machine is the same as the original was when it was returned
by sqlite_compile() - same query, same callback, same callback
context pointer. So if you need to, you can re-execute the query
without the overhead of going through the parsing and compilation
stage.

Most real applications will want parameters to pre-compiled queries, 
for example to pre-compile "SELECT val FROM values WHERE oid = %1" and
then change the value of %1 for each execution. To simulate this in 
SQLite, you could create a function using sqlite_create_function() to
retrieve the query "parameter" from your program. ie. Instead of
"SELECT value FROM values WHERE oid = %1", use 
"SELECT value FROM values WHERE oid = get_query_parameter(1)". Or 
whatever, you get the idea.

It would be good if people could post any performance measurements they
make here.

-----

24-July-2003:

Some performance results for pre-compiled queries. The temp db was using the in-memory backend.

  Insert 10000 records (real db)              - 2036 ms
  Insert 10000 records (real db, pre-compile) - 1080 ms

  Insert 10000 records (temp db)              - 1563 ms
  Insert 10000 records (temp db, pre-compile) - 706 ms

  Select 10000 records (real db)              - 5430 ms
  Select 10000 records (real db, pre-compile) - 3733 ms

  Select 10000 records (temp db)              - 1766 ms
  Select 10000 records (temp db, pre-compile) - 460 ms

The schema for the test is:

  CREATE TABLE tbl( key PRIMARY KEY, val );

Each insert test is:

  BEGIN;
  INSERT INTO tbl VALUES(1, <string about 40 chars long>);
  INSERT INTO tbl VALUES(2, <string about 40 chars long>);
  ...
  INSERT INTO tbl VALUES(9999, <string about 40 chars long>);
  INSERT INTO tbl VALUES(10000, <string about 40 chars long>);
  COMMIT;

Each select test is:

  SELECT * FROM tbl WHERE key = 1;
  SELECT * FROM tbl WHERE key = 2;
  ...
  SELECT * FROM tbl WHERE key = 9999;
  SELECT * FROM tbl WHERE key = 10000;

If you use an INTEGER PRIMARY KEY instead of a PRIMARY KEY things are slightly faster all round. The relative benefit of pre-compiled queries is slightly increased (5-10%) as well.