sqlite - Precompiled Sql

Not logged in

• Honeypot
• Browse
• Home
• Login
• Reports
• Search
• Ticket
• Timeline
• Wiki

• Contents
• Diff
• Text

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.