In this detailed overview of SQL database engine operations, the author explains the concept of a “prepared statement” and its significance in database execution. The paper explores two common methods of implementing prepared statements: Bytecode, used by SQLite, and Tree-Of-Objects, used by MySQL and PostgreSQL. The advantages of each method are discussed, with Bytecode being praised for its ease of understanding, debugging, incremental evaluation, memory efficiency, and potentially faster performance. On the other hand, the Tree-Of-Objects approach offers benefits such as deferred query planning decisions and easy parallelization for large analytic queries. The author encourages feedback and discussion on the topic.
https://sqlite.org/draft/whybytecode.html