Page History
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.
The program to run this test is precomp_test.c. It builds against 2.8.5.