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

221–230 of 231 posts

Re: Why SQLite Uses Bytecode

#221

Post by branko_d »

0x457 wrote: post #163
usrusr wrote: post #95

Earlier quoted context omitted.

Aka missing out on all the delicious profiling information approaches with later translation enjoy. There's no simple best answer.

I'm probably wrong, but I always thought for platforms like .Net and JVM, AoT delivers the same performance as JIT in most cases. Since a lot of information is available before runtime, unlike JS where VM always needs to be read, ditch optimized byte code and go back to the whiteboard.

As a rule of thumb: AOT has shorter cold start time, but similar overall performance to JIT.

In fact, JIT can perform slightly better once the program reaches "steady state" because it can target the exact processor running and also perform the dynamic profile-guided optimization, which may or may not yield meaningful improvements in real life.

Nick Chapsas did some benchmarking of .NET AOT vs JIT, if you are interested:

https://youtu.be/gJcPqdbKF90?si=PnSPnFvVhr0pjLL-

Re: Why SQLite Uses Bytecode

#222

Post by canucker2016 »

jpfr wrote: post #68
surfingdino wrote: post #21

Earlier quoted context omitted.

Doesn't MS Word internally run a Forth-like VM? I remember reading an article by someone who decompiled an early MS-DOS version of Word only to discover that there was a VM inside.

They called it p-code at the time. The purpose was (purported) to simplify the porting between architectures. https://casadevall.pro/articles/2020/11/compiling-word-for-w...

from http://www.trs-80.org/multiplan/

    "Originally code-named “EP” (for “Electronic Paper”), Multiplan was written to use a very clever p-code compiler system created by Richard Brodie. This p-code system, which was also used later by Microsoft Word, allowed Microsoft to target Multiplan to a huge number of different 8-bit and 16-bit computer systems. (Charles Simonyi once estimated that Multiplan was available for 100 platforms.)"
Many people say that the 'p' in 'p-code' stands for 'pseudo', i.e. pseudo code. But the article archived at https://techshelps.github.io/MSDN/BACKGRND/html/msdn_c7pcode... says the 'p' is short for 'packed'.

    "Microsoft has introduced a code compression technology in its C/C++ Development System for Windows version 7.0 (C/C++ 7.0) called p-code (short for packed code) that provides programmers with a flexible and easy-to-implement solution for minimizing an application's memory requirements. In most cases, p-code can reduce the size of an executable file by about 40 percent. For example, the Windows Project Manager version 1.0 (resource files not included) shrinks from 932K (C/C ++ 7.0 with size optimizations turned on) to 556K when p-code is employed."

    "Until now, p-code has been a proprietary technology developed by the applications group at Microsoft and used on a variety of internal projects. The retail releases of Microsoft Excel, Word, PowerPoint�, and other applications employ this technology to provide extensive breadth of functionality without consuming inordinate amounts of memory."

Re: Why SQLite Uses Bytecode

#223

Post by atombender »

xymostech wrote: post #219
atombender wrote: post #187

Earlier quoted context omitted.

TrueType (and OpenType, which is an evolution of TT) absolutely includes a bytecode instruction set: https://developer.apple.com/fonts/TrueType-Reference-Manual/...

Oh yes, thank you for the link to that! Looks like that is an instruction set for representing the font glyphs themselves? I was talking about the instruction set in TFM which is for representing meta information, like ligatures and kerning between glyphs, and not for the actual glyphs. The glpyhs for the original TeX fonts are described using Metafont which is an interpreted language.

Glyphs are generally defined as pure outlines (the "glyf" table [1]), and the instruction set is an optional system for things like grid fitting. Ligatures, kerning etc. are normal tables.

[1] https://developer.apple.com/fonts/TrueType-Reference-Manual/...

Re: Why SQLite Uses Bytecode

#224

Post by lambdaxyzw »

galaxyLogic wrote: post #157
fancyfredbot wrote: post #79

Earlier quoted context omitted.

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.

I wonder in actual business scenarios isn't the SQL fully known before an app goes into production? So couldn't it make sense to compile it all the way down? There are situations where the analyst enters SQL into the computer interactively. But in those cases the overhead of compiling does not really matter since this is infrequently done, and there is only a single user asking for the operation of running the SQL.

As far as I know, database often does query caching. So it doesn't have to recompile the query all the time.

On the other hand, query plan may depend on the specific parameters - the engine may produce a different plan for a query for users with deleted=true (index scan) and for deleted=false (99% of users are not deleted, not worth it to use index).

Re: Why SQLite Uses Bytecode

#225

Post by lambdaxyzw »

wruza wrote: post #81
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…

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

My usual problems are:

1) Running queries manually, where SQL is much more friendly than writing bytecode by hand. 2) Running queries using a clueless ORM that has no idea how to optimize them, so leaving this job to the database makes sense.

I believe the actually rare problem is "writing hyper-optimized complex queries tailored to the current state of the database", where bytecode would help. But that is a very unusual usecase.

Maybe there are shops that use databases very differently from me, but it makes sense that SQL is optimized for the common use case.

And since db internals are very different, it's hard to make a single bytecode standard that makes sense to use across different databases. You can probably write something specialized for sqlite or postgres, but since nobody did, probably it's not as useful for other people too.

Re: Why SQLite Uses Bytecode

#226

Post by galaxyLogic »

mfoc wrote: post #167
galaxyLogic wrote: post #161

Earlier quoted context omitted.

Congratulations for your first comment! What I want to do is represent my tree-structure as a table in a relational database and then be able to efficiently get the tree structure back by transforming the table-representation back into a tree. Further I would like to do that in plain standard SQL. This must be a common problem, any documented solutions out there?

Thank you. Great question - you have touched on the key difference between a labeling scheme and an encoding scheme for tree data structures. As mentioned previously, the tree is an abstract data type, that is to say, a conceptual model that defines the nodes in a tree, and their relationships. To be able to evaluate a expression that processes a tree, one needs a labeling scheme. The purpose of a labeling scheme is…

Thanks for the link

Re: Why SQLite Uses Bytecode

#228

Post by wruza »

lambdaxyzw wrote: post #225
wruza wrote: post #81

Earlier quoted context omitted.

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…

>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. My usual problems are: 1) Running queries manually, where SQL is much more friendly than writing bytecode by hand. 2) Running queries using a clueless ORM that has no idea how to optimize them, so leaving this job to the database makes sense. I believe the actually rare problem is "writing hyper-op…

hyper-optimized complex queries tailored to the current state of the database", where bytecode would help

Optimization isn’t the goal here, this quote misrepresents the idea. In the spirit of dismissal through all these years, btw, some people just don‘t get it and think it’s for better performance. It is, but for better performance of a developer, not that of a database. The goal is to have a set of languages above that bytecode that would help with usual programming bookkeeping and with expressing queries in an algorithmic way because that’s how you designed it. SQL lacks DX ergonomics, it’s just a language from the clueless epoch.

Re: Why SQLite Uses Bytecode

#229

Post by forgotmypw100 »

megadal wrote: post #22

Perhaps my understanding is off, but I am pretty sure parsing and translating SQL into bytecode still involves an AST. Just that query processing itself is done from the bytecode (produced presumably from an AST or something similar) rather than directly from the AST itself. If I'm right I can't really see how this performs better unless you're excluding the parsing step from benchmarks

I asked on the mailing list years ago if there was an AST that I could generate from my general purpose language, and the answer was no. It's SQL and then it's bytecode.

Re: Why SQLite Uses Bytecode

#230

Post by immibis »

AnthonyMouse wrote: post #192
cryptonector wrote: post #178

Earlier quoted context omitted.

I've not been following this stuff. Whom did Musk ban?

Like most large social media sites, pre-Musk Twitter had some combination of algorithms and bureaucracy that act as a chaos monkey to shadowban and suspend accounts apparently at random, often with an inability to comprehend satire or under incoherent or undisclosed pretexts. The site's users and staff were predominantly left-leaning, so right-leaning accounts were disproportionately the victims of these false positi…

Musk has personally bragged about banning a lot of left-leaning accounts and unbanning right-leaning. What is your evidence that the site still leans left?
Post reply on HN