Preparing a Statement

When you ask SQLite to prepare a statement, your painstakingly handcrafted SQL is dissected (parsed), analyzed (query planned), and boiled down (compiled) into a bytecode program SQLite’s VDBE is capable of executing.

Tokenizing & Parsing

Just like any programming language, SQL starts as a bunch of text. To get from a string of text into something SQLite can understand, that text needs to be broken down and understood. This is what we call parsing.

SQLite’s parsing approach is made abundantly clear throughout the official documentation. For example, if you’ve ever ended up at sqlite.org while looking for how you are supposed to write an INSERT statement — you’ve likely seen a diagram that explains a part of how SQLite’s parser works:

INSERT syntax diagram (from sqlite.org)

Diagrams like the one above are a way of visualizing the Backus-Naur Form (BNF) description of the SQL grammar that SQLite understands. Reading the syntax diagrams is a pretty straightforward process when you know what to look for:

Ovals with text in ALL CAPS are keywords.

are keywords. Ovals with lower-case-and-hypenated text represent reusable grammar clauses (their syntax diagrams will be listed further down the page in the documentation).

text represent reusable grammar clauses (their syntax diagrams will be listed further down the page in the documentation). Ovals with punctuation like parentheses, commas, etc are literal tokens that are required.

The arrows that connect the ovals tell you the order in which the keywords, clauses, and tokens need to appear to construct a valid statement.

Query Planning

After the statement has been parsed into its component parts, SQLite needs to decide how to approach executing the statement.

For any given SQL statement, there might be hundreds or thousands or even millions of different algorithms of performing the operation. All of these algorithms will get the correct answer, though some will run faster than others. The query planner is an AI that tries to pick the fastest and most efficient algorithm for each SQL statement. — SQLite.org

Understanding how SQLite determines the best way to execute your statements is a big enough topic to warrant its own post. However, for this article it’s just important to know that there is an optimization step between parsing and compilation.

Compilation

Finally: after SQLite has determined how to best approach running your statement, it puts together a list of low-level bytecode instructions that describe the whole operation. That list of instructions is, quite literally, a program which will be run on the VDBE. A more popular name for the program compiled by SQLite is “prepared statement”.

Each bytecode instruction consists of an opcode (name of the instruction) and up to 5 parameters (input values or references to registers). In modern SQLite versions, there are over a hundred different types of instructions. They run the gamut between simple control-flow instructions like Eq : “jump to an instruction if two registers have the same value” and more database-specific instructions like ResultRow : which provides data to the database cursor at the current position, pointing at values which have been loaded into the VDBE’s registers.

Once compiled into bytecode, a prepared statement does not need to be parsed or to go through the query planning process again. This is precisely what makes re-using prepared statements so fast when compared with opting not to re-use them.