Re: [sqlite] SQLite VM questions
What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? SQLite doesn't have interpreter, it has parser. I guess this makes the rest of your email inapplicable. Pavel On Sun, Oct 14, 2012 at 4:38 AM, Elefterios Stamatogiannakis est...@gmail.com wrote: I have some questions for those that know the innards of SQLite. What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? Concerning above questions, i've found a great article about a portable interpreter implementation that produces a close to JITed performance, interpreter: http://www.emulators.com/docs/nx25_nostradamus.htm Another idea for producing a portable JIT (without an assembly backend) is what QEMU does, by chaining precompiled functions. Arguably QEMU's way is more heavy/complex than using an interpreter, but maybe it wouldn't bloat SQLite that much, and SQLite would remain portable across platforms. I'm asking above questions, because i believe that due to SQLite running on billions of devices it needs to be as efficient as possible. Due to the number of deployments, it may burn GWatts of power across all these devices (i haven't done the calculation). Thanks, lefteris. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite VM questions
On 14/10/2012 2:26 PM, Pavel Ivanov wrote: What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? SQLite doesn't have interpreter, it has parser. I guess this makes the rest of your email inapplicable. Umm... yes it does. http://www.sqlite.org/vdbe.html For the OP's question, it's very efficient compared to what I've seen in, say, postgres, but I don't know how it would compare to something like python or ruby. Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite VM questions
On Sun, Oct 14, 2012 at 12:07 PM, Ryan Johnson ryan.john...@cs.utoronto.ca wrote: On 14/10/2012 2:26 PM, Pavel Ivanov wrote: What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? SQLite doesn't have interpreter, it has parser. I guess this makes the rest of your email inapplicable. Umm... yes it does. http://www.sqlite.org/vdbe.html Maybe there's some conflict of terminology here. But as I understand it converting SQL query into a set of opcodes representing all operations needed to execute the query and then executing these opcodes is not interpreting, it's parsing. Interpreting is more related to some full-blown execution languages like python, perl, javascript or something like that. These languages indeed require some technologies like JIT. But they are not applicable to SQL. Maybe only to PL/SQL-like language, but it doesn't exist in SQLite. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite VM questions
On Sun, Oct 14, 2012 at 7:38 AM, Elefterios Stamatogiannakis est...@gmail.com wrote: I have some questions for those that know the innards of SQLite. What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? SQLite uses a simple byte-code interpreter implemented as a loop around a big switch statement with a separate case for each opcode. The implementation is in the file vdbe.c. See http://www.sqlite.org/src/artifact/31523df2b986?ln for the latest version of this file. The opcodes in the SQLite byte-code are unlike what you would fine in the VMs for javascript or python. SQLite opcodes are much higher level. In procedural languages, the VM needs to be dominated by opcodes to (for example) add a pair of values together and store the result. SQLite has such opcodes, but they are infrequently used and do not amount to much in terms of performance cost. The bulk of CPU time in the SQLite VM is spent in higher level opcodes such as OP_Insert (which inserts a new entry into a B-Tree) or OP_Column (which decodes the bytes of a row as described at http://www.sqlite.org/fileformat2.html#record_format and return the N-th column of that row). These high-level opcodes are implemented as thousands of lines of highly tuned C code. The overhead of instruction dispatch is insignificant in comparison, according to measurements we have done using cachegrind. Concerning above questions, i've found a great article about a portable interpreter implementation that produces a close to JITed performance, interpreter: http://www.emulators.com/docs/**nx25_nostradamus.htmhttp://www.emulators.com/docs/nx25_nostradamus.htm Another idea for producing a portable JIT (without an assembly backend) is what QEMU does, by chaining precompiled functions. Arguably QEMU's way is more heavy/complex than using an interpreter, but maybe it wouldn't bloat SQLite that much, and SQLite would remain portable across platforms. I'm asking above questions, because i believe that due to SQLite running on billions of devices it needs to be as efficient as possible. Due to the number of deployments, it may burn GWatts of power across all these devices (i haven't done the calculation). Thanks, lefteris. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite VM questions
On 14 Oct 2012, at 8:07pm, Ryan Johnson ryan.john...@cs.utoronto.ca wrote: On 14/10/2012 2:26 PM, Pavel Ivanov wrote: What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? SQLite doesn't have interpreter, it has parser. I guess this makes the rest of your email inapplicable. Umm... yes it does. http://www.sqlite.org/vdbe.html For the OP's question, it's very efficient compared to what I've seen in, say, postgres, but I don't know how it would compare to something like python or ruby. SQLite is pretty good. Python is widely seen as slow, though it's not really, it's just difficult to write a fast interepreter for such general code. Ruby is about the same, with the same problems. SQLite can and is highly optimised for doing the things SQL needs done and nothing else. It doesn't have to have the flexibility of a general-purposes programming language, so the opcodes are better designed for database management. And it has a great benefit that EXPLAIN and EXPLAIN QUERY PLAN are excellent tools which have been used to figure out problems and improve it still further. Someone without experience is not going to be able to just dive in and make something better. Huge effort is going on right now to speed up JavaScript so that's a crowded market. If you're looking for something to speed up, aim at Python. On 14 Oct 2012, at 9:08pm, Pavel Ivanov paiva...@gmail.com wrote: Maybe there's some conflict of terminology here. But as I understand it converting SQL query into a set of opcodes representing all operations needed to execute the query and then executing these opcodes is not interpreting, it's parsing. Interpreting is more related to some full-blown execution languages like python, perl, javascript or something like that. The 'interpreter' phase of SQLite is turning SQL commands into VDBE opcodes. The first phase of that is parsing. But at this level were just haggling over language. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users