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

71–80 of 231 posts

Re: Why SQLite Uses Bytecode

#71

Post by immibis »

hickelpickle wrote: post #61
eklavya wrote: post #50

Earlier quoted context omitted.

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.

There is threaded bytecode as well, which uses direct jumping vs a switch for dispatch. This can improve branch prediction, though it is a debated topic and may not offer much improvement for modern processors.

How does it know where to jump to?

Re: Why SQLite Uses Bytecode

#72

Post by nraynaud »

thayne wrote: post #62
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 wondering the same thing. And in particular if a new query language that avoided many of the pitfalls of SQL could compile down to that bytecode and avoid having to deal with SQL as an intermediate representation. Also, if you can compile to bytecode ahead of time, then that could save the time needed to parse the text of a sql query to bytecode at runtime.

I'm puttig my wish list here:

- be able to put the projection in a varable and reuse it (and I think orm people might love it)

- have a quick way to forward the the non-aggregated fields of projection to a group by (maybe with the aforementionned variables)

Re: Why SQLite Uses Bytecode

#73

Post by gpderetta »

branko_d wrote: post #41
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.

> 2. compiled then interpreted bytecode You can also compile to bytecode (when building), and then compile that bytecode to the machine code (when running). That way, you can take advantage of the exact processor that is running your program. This is the approach taken by .NET and Java, and I presume most other runtime environments that use bytecode.

There is also the option of native code generation from bytecode at install time as opposed of runtime.

Re: Why SQLite Uses Bytecode

#74

Post by gpderetta »

bane wrote: post #13
chrisaycock wrote: post #2

SQLite's design docs were the first time I had seen a database use a virtual machine instead of walking a tree. I later noticed VMs in libraries, embedded DSLs, and other applications outside of large general-purpose programming languages. That really drove home for me that VMs could be anywhere and were often a useful step in handling a user's expressions.

VMs really can be. They have a long history in code portability. In the old days it really wasn't uncommon to use an approach centered around some interpretable byte code running in a vm, where the reusable vm was all that needed porting to different architectures and operating systems. This all happened well before Java. It was really big in gaming, Zork, Sierra games, LucasArts games, and even a few more "action" g…

Don't know about Flashback, but Another World famously was VM based.

Re: Why SQLite Uses Bytecode

#75

Post by Someone »

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 of the SQLite code and file format will help it remain viable on future platforms.”

Re: Why SQLite Uses Bytecode

#76

Post by alex_smart »

sitkack wrote: post #64
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…

You should look at https://janino-compiler.github.io/janino/ it can compile Java into class files in memory that can be directly executed.

I found this by looking at spark source code to figure out what they were doing under the hood to deal solve this problem. :)

Re: Why SQLite Uses Bytecode

#77

Post by bryancoxwell »

userbinator wrote: post #8

The problem of rendering a tree-of-objects as a table is sufficiently difficult that nobody does it, as far as I know. Hence, no tree-of-objects database engine provides the level of detail in their "EXPLAIN" output that SQLite provides. I believe Microsoft SQL Server uses an object tree internally, and yet its query plan output is a table: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-s...

I don’t doubt the author, but what is it that makes rendering a tree of objects to a table a difficult problem? Is that not what browsers do when they render a table element?

Re: Why SQLite Uses Bytecode

#78

Post by BiteCode_dev »

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.

Simple, I'm on Mastodon, Substack, Threads and Bluesky.

And they are all barely breathing.

The community density is low (because of the split), the discovery sucks (because of decentralization and no algo to suggest content), the culture is homogeneous (I encounter people mostly from one political side) and the publication of limited quality.

You can't start new communities from old users. Communities are built by the young, they are the ones with the creativity, motivation and free time to do it.

That's why twitter, made mostly by young people from 20 years ago, still got the inertia of it, are is more interesting and active.

And that's why tik tok and roblox are full of life. I'm sure the kids are also elsewhere, where we are not looking, creating the communities of tomorrow.

Re: Why SQLite Uses Bytecode

#79

Post by fancyfredbot »

paulddraper wrote: post #34
wolf550e wrote: post #29

The page is the result of this exchange on Twitter: https://twitter.com/gorilla0513/status/1784756577465200740 I was surprised to receive a reply from you, the author. Thank you :) Since I'm a novice with both compilers and databases, could you tell me what the advantages and disadvantages are of using a VM with SQLite? https://twitter.com/DRichardHipp/status/1784783482788413491 It is difficult to summarize the advan…

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.

For simple functions which are not called repeatedly you have to invert this list - interpreted code is fastest and compiling the code is slowest.

The complied code would still be faster if you excluded the compilation time. It's just that the overhead of compiling it is sometimes higher than the benefit.

Re: Why SQLite Uses Bytecode

#80

Post by IsTom »

bryancoxwell wrote: post #77
userbinator wrote: post #8

The problem of rendering a tree-of-objects as a table is sufficiently difficult that nobody does it, as far as I know. Hence, no tree-of-objects database engine provides the level of detail in their "EXPLAIN" output that SQLite provides. I believe Microsoft SQL Server uses an object tree internally, and yet its query plan output is a table: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-s...

I don’t doubt the author, but what is it that makes rendering a tree of objects to a table a difficult problem? Is that not what browsers do when they render a table element?

At least they're hard to display on a terminal. Reading EXPLAIN ANALYZE diagrams from psql does not spark joy.
Post reply on HN