Re: [sqlite] C precompiler to bytecode
Christian Smith <[EMAIL PROTECTED]> wrote: > > > Run time compilation is not that expensive if the generated vm is cached. > Just have a per-connection hash, use the SQL as the hash key, and the > resulltingvm as the value. Upon first use, the SQL will not be in the > hash, and will be compiled and inserted. Subsequent uses of the same SQL > will be quickly retrieved from the hash and used as is. > > This is how the TCL wrappers work, I believe. > Yes, this is how the TCL wrapper works. But there are some complications. (1) It is important to limit the cache size. Otherwise the size of the cache might grow without bound. The TCL extension caches the last 10 statements by default (the cache size can be adjusted at runtime). The cache employs LRU replacement. (2) For any precompiled and cached statement, your code needs to be ready to deal with an SQLITE_SCHEMA error by deleting the old statement and rebuilding it using sqlite3_prepare(). An SQLITE_SCHEMA error occurs when the precompiled statement detects that the current database schema is different from the schema at the time the statement was first compiled. The TCL wrapper handles SQLITE_SCHEMA errors automatically so that a TCL programmer never has to know about them. The second point is the primary reason why Daniel's original idea of precompiling statements in C++ programs might not be as helpful as he imagines. If statements were precompiled into the C++ program, then the C++ program would have to be recompiled whenever the database schema changed, which could quickly get to be bothersome. As Joe Wilson pointed out, I sell a proprietary version of SQLite that does something like what Daniel suggests. The proprietary version omits the SQL parser and code generator and is thus much smaller than the standard SQLite - as small 65KiB. There is currently just one user of this extension - a manufacturer who puts the code on smart-cards with extremely tight code memory constraints. Another consumer device manufacturer is also looking into this technology. Using the proprietary extension, statements are prepared on a workstation and then the compiled forms of the statements are stored in a special table in the database. The database is then transfered to the device. The device has just enough code to read the special table, extract the prepared statements, and run the prepared statements. You can also bind values the prepared statements so that INSERT, and UPDATE statements are useful. Note, however, that the device is unable to modify the database schema in any way, because if it were to do so, all of the precompiled statements would have to be compiled again, and the device lacks the SQL parser and code generator needed to do that. So this extension, while useful in certain niche applications, is not particularly helpful to most people. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] C precompiler to bytecode
Daniel's concept is that of Embedded SQL, a marriage not quite made in heaven probably because of the clumsiness in integrating the database schema and the data definitions of legacy languages. At one time we built a byte-coded implementation of a legacy language which did exactly as Daniel proposes, compiled the embedded SQL into the equivalent of the Sqlite VBDE and executed that at runtime. It realized the benefits Daniel predicts. The benefits were more evident back in an area of far slower machines than you would see today. Christian Smith wrote: Daniel Önnerby uttered: Hi everyone! I'm just a bit curios if it would be possible to make like a C precompiler or a macro of some kind that compiles/interpret the SQL-statements to bytecode just like the sqlite_prepare does but does this when compiling/precompiling your application instead of at runtime. Since most application written in C/C++ use static SQL-statements (unless you are building your SQL-strings on the fly) and then bind the values, I guess there would be several benefits for your compiled application: * Faster: Since the SQL-statement is already interpreted. * Smaller (not that sqlite needs to be smaller): The executable does not need to contain the part of sqlite that interprets the SQL-statements since this was made at compile time. Run time compilation is not that expensive if the generated vm is cached. Just have a per-connection hash, use the SQL as the hash key, and the resulltingvm as the value. Upon first use, the SQL will not be in the hash, and will be compiled and inserted. Subsequent uses of the same SQL will be quickly retrieved from the hash and used as is. This is how the TCL wrappers work, I believe. Just a thought :) Best regards Daniel Önnerby -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] C precompiler to bytecode
On 8/8/06, Nuno Lucas <[EMAIL PROTECTED]> wrote: On 8/8/06, Daniel Önnerby <[EMAIL PROTECTED]> wrote: > I'm just a bit curios if it would be possible to make like a C > precompiler or a macro of some kind that compiles/interpret the > SQL-statements to bytecode just like the sqlite_prepare does but does > this when compiling/precompiling your application instead of at runtime. > Since most application written in C/C++ use static SQL-statements > (unless you are building your SQL-strings on the fly) and then bind the > values, I guess there would be several benefits for your compiled > application: Now the cons: * Forget about using your program concurrently with others. Future sqlite versions may decide to use different low-level implementations that can clash with your hardcoded functions (this is the same as statically linking the sqlite library with your application). This is an issue for the on-disk file format. Static vs dynamic linking and the sqlite versions used have no bearing here.
Re: [sqlite] C precompiler to bytecode
Daniel Önnerby uttered: Hi everyone! I'm just a bit curios if it would be possible to make like a C precompiler or a macro of some kind that compiles/interpret the SQL-statements to bytecode just like the sqlite_prepare does but does this when compiling/precompiling your application instead of at runtime. Since most application written in C/C++ use static SQL-statements (unless you are building your SQL-strings on the fly) and then bind the values, I guess there would be several benefits for your compiled application: * Faster: Since the SQL-statement is already interpreted. * Smaller (not that sqlite needs to be smaller): The executable does not need to contain the part of sqlite that interprets the SQL-statements since this was made at compile time. Run time compilation is not that expensive if the generated vm is cached. Just have a per-connection hash, use the SQL as the hash key, and the resulltingvm as the value. Upon first use, the SQL will not be in the hash, and will be compiled and inserted. Subsequent uses of the same SQL will be quickly retrieved from the hash and used as is. This is how the TCL wrappers work, I believe. Just a thought :) Best regards Daniel Önnerby -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] C precompiler to bytecode
--- Bill KING <[EMAIL PROTECTED]> wrote: > Daniel Önnerby wrote: > > Hi everyone! > > > > I'm just a bit curios if it would be possible to make like a C > > precompiler or a macro of some kind that compiles/interpret the > > SQL-statements to bytecode just like the sqlite_prepare does but does > > this when compiling/precompiling your application instead of at > > runtime. Since most application written in C/C++ use static > > SQL-statements (unless you are building your SQL-strings on the fly) > > and then bind the values, I guess there would be several benefits for > > your compiled application: > > * Faster: Since the SQL-statement is already interpreted. > > * Smaller (not that sqlite needs to be smaller): The executable does > > not need to contain the part of sqlite that interprets the > > SQL-statements since this was made at compile time. > > > > Just a thought :) > > > > Best regards > > Daniel Önnerby > > > I second this. I use a lot of different database handles (due to a lot > of multi-threading), so there's no way I've seen yet to compile an sql > query that i can distribute to the different handles, so every statement > gets prepared just before run. Huge performance hit. If we could even > just pre-compile once per run, and attach to a database handle afterwards... I recall reading on this mailing list that DRH sells a version of SQLite that can be compiled without a SQL parser that works with precompiled statements. > > -- > Bill King, Software Engineer > Trolltech, Brisbane Technology Park > 26 Brandl St, Eight Mile Plains, > QLD, Australia, 4113 > Tel + 61 7 3219 9906 (x137) > Fax + 61 7 3219 9938 > mobile: 0423 532 733 > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] C precompiler to bytecode
Daniel Önnerby wrote: > Hi everyone! > > I'm just a bit curios if it would be possible to make like a C > precompiler or a macro of some kind that compiles/interpret the > SQL-statements to bytecode just like the sqlite_prepare does but does > this when compiling/precompiling your application instead of at > runtime. Since most application written in C/C++ use static > SQL-statements (unless you are building your SQL-strings on the fly) > and then bind the values, I guess there would be several benefits for > your compiled application: > * Faster: Since the SQL-statement is already interpreted. > * Smaller (not that sqlite needs to be smaller): The executable does > not need to contain the part of sqlite that interprets the > SQL-statements since this was made at compile time. > > Just a thought :) > > Best regards > Daniel Önnerby > I second this. I use a lot of different database handles (due to a lot of multi-threading), so there's no way I've seen yet to compile an sql query that i can distribute to the different handles, so every statement gets prepared just before run. Huge performance hit. If we could even just pre-compile once per run, and attach to a database handle afterwards... -- Bill King, Software Engineer Trolltech, Brisbane Technology Park 26 Brandl St, Eight Mile Plains, QLD, Australia, 4113 Tel + 61 7 3219 9906 (x137) Fax + 61 7 3219 9938 mobile: 0423 532 733
Re: [sqlite] C precompiler to bytecode
On 8/8/06, Daniel Önnerby <[EMAIL PROTECTED]> wrote: I'm just a bit curios if it would be possible to make like a C precompiler or a macro of some kind that compiles/interpret the SQL-statements to bytecode just like the sqlite_prepare does but does this when compiling/precompiling your application instead of at runtime. Since most application written in C/C++ use static SQL-statements (unless you are building your SQL-strings on the fly) and then bind the values, I guess there would be several benefits for your compiled application: * Faster: Since the SQL-statement is already interpreted. * Smaller (not that sqlite needs to be smaller): The executable does not need to contain the part of sqlite that interprets the SQL-statements since this was made at compile time. Now the cons: * Hardcoded to a specific SQLite version. Even if the *undocumented* API doesn't change on the next version, there's a very good chance it will latter. * Probably not smaller on the easy way.. If you have two pre-compiled statements, it would need a smart pre-compiler to know what can be shared and what can't, implying a level of complexity that maybe doesn't gain nothing. * You can already do the same (with the same problems). By looking at the explain output you can hardcode the same yourself, without the need of a pre-compiler that will need to be constantly up-to-date * If your queries are known at start, you can even forget about SQLite. If you know what you need to do (so, don't need the sqlite library linked, but just a subset of it), maybe it would be best to just call functions and optimize them with a specific data structure that fits you better than sqlite. * Forget about using your program concurrently with others. Future sqlite versions may decide to use different low-level implementations that can clash with your hardcoded functions (this is the same as statically linking the sqlite library with your application). Just my .02 cents... Regards, ~Nuno Lucas
Re: [sqlite] C precompiler to bytecode
That's a very good idea!, and I would like to help for do that On 8/8/06, Daniel Önnerby <[EMAIL PROTECTED]> wrote: Hi everyone! I'm just a bit curios if it would be possible to make like a C precompiler or a macro of some kind that compiles/interpret the SQL-statements to bytecode just like the sqlite_prepare does but does this when compiling/precompiling your application instead of at runtime. Since most application written in C/C++ use static SQL-statements (unless you are building your SQL-strings on the fly) and then bind the values, I guess there would be several benefits for your compiled application: * Faster: Since the SQL-statement is already interpreted. * Smaller (not that sqlite needs to be smaller): The executable does not need to contain the part of sqlite that interprets the SQL-statements since this was made at compile time. Just a thought :) Best regards Daniel Önnerby
[sqlite] C precompiler to bytecode
Hi everyone! I'm just a bit curios if it would be possible to make like a C precompiler or a macro of some kind that compiles/interpret the SQL-statements to bytecode just like the sqlite_prepare does but does this when compiling/precompiling your application instead of at runtime. Since most application written in C/C++ use static SQL-statements (unless you are building your SQL-strings on the fly) and then bind the values, I guess there would be several benefits for your compiled application: * Faster: Since the SQL-statement is already interpreted. * Smaller (not that sqlite needs to be smaller): The executable does not need to contain the part of sqlite that interprets the SQL-statements since this was made at compile time. Just a thought :) Best regards Daniel Önnerby
