Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why SQLite Uses Bytecode

sqlite.org

Post reply on HN
Print view View original

81–90 of 231 posts

Re: Why SQLite Uses Bytecode

#81

Post by wruza »

manx wrote: post #60

I'm wondering if one could write this bytecode directly (or with a higher level imperative language) instead of SQL. Often, the programmer knows exactly which index lookups need to happen in a loop, while it seems like a burden to express that in SQL. This might also be an opportunity to create a different type safe dsl for database access.

I was advocating for it for decades, but everyone dismisses it with “you don’t know better than rdbms”. That’s almost a religion. Despite the fact that most of the tables most people have are never any big (except for a few oltps that you ought to leave to specific sql server wizards anyway) and you usually do have the idea how it should work. SQL is a cool solution for a set of problems that has a very non-zero xor with a set of problems that we actually have. And most of our complex queries are trivially expressible in imperative loops. Index lookup and a complex plan building are key features of an rdbms, everything else it steals from you and forces to use an arcane inconvenient language that in practice isn’t even a standard. Make plan building and indexing core ops and leave everything else to some sort of a general purpose bytecode, everyone will be happier and will create dozens of DSLs for all their needs.

Re: Why SQLite Uses Bytecode

#83

Post by alex_smart »

eklavya wrote: post #50
alex_smart wrote: post #35

I recently implemented my own expression evaluator in java for in-memory data frames, and once you think about doing that deeply, you very quickly understand the need for bytecode. If you directly evaluate the expression using a tree representation, you basically have to do a whole lot of branching (either via switch statements or polymorphism) for every single line of useful operation. Yes, the branch predictor kick…

Bytecode will only impact packing, so more efficient ram, cache and cpu wise. But I don't understand how it would help with branching? You still have to make the same decisions? As in the bytecode executor still needs to do differnt things based on the op code, its not in hardware.

Consider evaluating an expression like "col1 + col2 == col3"

A tree based evaluator has to do something like -

    for (int i = 0; i
whereas a bytecode based evaluator is able to run something equivalent to -

    for (int i = 0; i

Re: Why SQLite Uses Bytecode

#84

Post by gpderetta »

Dwedit wrote: post #14

Running bytecode is much lower latency than compiling into native code. If you're not bottlenecked by running the bytecode (as opposed to memory or disk speed), you don't really have to JIT it any further into native code.

Depends how much compilation you want to do. For example converting threaded code into native direct jumps need not be more expensive than plain bytecode. Of course the gains are also limited.

Re: Why SQLite Uses Bytecode

#85

Post by matharmin »

What I find interesting is that this approach means the query plan is chosen when "compiling" the statement, not when running it. However, there are still some cases where SQLite will recompile the statement when running, such as when the schema changed.

Re: Why SQLite Uses Bytecode

#86

Post by FridgeSeal »

tanelpoder wrote: post #54
paulddraper wrote: post #34

Earlier quoted context omitted.

There are three approaches: 1. interpreted code 2. compiled then interpreted bytecode 3. compiled machine code The further up, the simpler. The further down, the faster.

4. lots of small building blocks of static machine code precompiled/shipped with DB software binary, later iterated & looped through based on the query plan the optimizer came up with. Oracle does this with their columnar/vector/SIMD processing In-Memory Database option (it's not like LLVM as it doesn't compile/link/rearrange the existing binary building blocks, just jumps & loops through the existing ones in the req…

That’s a really cool idea! Is there any writeups or articles about this in more detail?

Re: Why SQLite Uses Bytecode

#88

Post by zarathustreal »

jonp888 wrote: post #59
coolandsmartrr wrote: post #46

Earlier quoted context omitted.

I am amazed that the author (D Richard Hipp) made an effort to find and respond to a tweet that was (1) not directed/"@tted" at him or (2) written in his native language of English (original tweet is in Japanese[1]). [1] https://twitter.com/gorilla0513/status/1784623660193677762

Side note, but I'm amazed that anyone that is not a journalist or a politician still actively uses X/twitter. Everyone I used to follow has stopped.

[flagged]

Re: Why SQLite Uses Bytecode

#89

Post by layer8 »

MrBuddyCasino wrote: post #30
tadfisher wrote: post #26

Earlier quoted context omitted.

Do clients typically communicate with the server in some AST representation instead of, well, SQL? I'd be surprised if that much parsing/planning happens on the client.

Since prepared statements are created by the driver, I was assuming this was the case - but I might be completely wrong here.

No, prepared statements are server-side.

Re: Why SQLite Uses Bytecode

#90

Post by adontz »

Someone wrote: post #75
adontz wrote: post #45

Looks like SQLite could benefit from copy-and-patch JIT compiler.

I think that would make it cross a border where ‘Lite’ no longer applies. It also would be quite a challenge given their long-term-support statement ( https://www.sqlite.org/lts.html ): “Cross-platform Code → SQLite runs on any platform with an 8-bit byte, two's complement 32-bit and 64-bit integers, and a C compiler. It is actively tested on all currently popular CPUs and operating systems. The extreme portability o…

There is no implied requirement to support JIT everywhere.
Post reply on HN