SQLite CVSTrac

Page History

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.

sqlite - Precompiled Sql

Page History