sqlite - Precompiled Sql

Not logged in

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

• Contents
• 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.