Re: [sqlite] SQLite VM questions

[Simon Slavin]

On 14 Oct 2012, at 8:07pm, Ryan Johnson  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  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

Re: [sqlite] SQLite VM questions

[Richard Hipp]

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



-- 
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

[Pavel Ivanov]

On Sun, Oct 14, 2012 at 12:07 PM, Ryan Johnson
 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

[Ryan Johnson]

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

[Pavel Ivanov]

> 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
 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