8 ms·

The page is the result of this exchange on Twitter: https://twitter.com/gorilla0513/status/1784756577465200740 https://twitter.com/gorilla0513/status/178475657

• paulddraper 2y ago
  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.
  • branko_d 2y ago
    > 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.
    • gpderetta 2y ago
      There is also the option of native code generation from bytecode at install time as opposed of runtime.
      • usrusr 2y ago
        Aka missing out on all the delicious profiling information approaches with later translation enjoy. There's no simple best answer.
        • 0x457 2y ago
          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.
          • neonsunset 2y ago
            There are a few concerns here that make it confusing for the general public to reason about AOT vs JIT performance. Different JIT compilers have different levels of sophistication, being able to dynamically profile code at runtime or otherwise, and the range of applied optimizations of this type may also vary significantly. Then, AOT imposes a different restrictions in the form of what types of modularity the language/runtime wants to support (Swift ABI vs no AOT ABI in .NET NativeAOT or GraalVM Native Image), what kind of operations modifying type system are allowed, if any, and how the binary is produced. In more trivial implementations, where JIT does not perform any dynamic recompilation and profile based optimizations, the difference might come down to simply pre-JITing the code, with the same quality of codegen. Or the JIT may be sophisticated by AOT mode might still be a plain pre-JIT which makes dynamic profiling optimizations off-limits (which was historically the case for .NET's ReadyToRun and partially NGEN, IIRC JVM situation was similar up until GrallVM Native Image). In more advanced implementations, there are many concerns which might be at odds with each other: JIT compilers have to maintain high throughput in order to be productive but may be able to instrument intermediate compilations to gather profile data, AOT compilers, in the absence of static profile data (the general case), have to make a lot more assumptions about the code, but can reason about compiled code statically, assuming such compilation comes with "frozen world" type of packaging (rather than delegating dynamic parts to emulation with interpreter). And then there are smaller details - JIT may not be able to ever emit pure direct calls for user code where a jump is performed to an immediate encoded in the machine code, because JIT may have to retain the ability to patch and backpatch the callsites, should it need to de/reoptimize. Instead, the function addresses may be stored in the memory, and those locations are encoded instead where calls are emitted in the form of dereference of a function pointer from a static address and then a jump to it. JIT may also be able to embed the values of static readonly fields as JIT constants in codegen, which .NET does aggressively, but is unable to pre-initialize such values by interpreting static initialization at compile time in such an aggressive way that AOT can (constexpr style). So in general, a lot of it comes down to offering a different performance profile. A lot of the beliefs in AOT performance stem from the fact lower-level languages rely on it, and the compilers offering it are very advanced (GCC and Clang mainly), which can expend very long compilation times on hundreds of optimization passes JIT compilers cannot. But otherwise, JIT compilers can and do compete, just a lot of modern day advanced implementations are held back by the code that they are used for, in particular in Java land where OpenJDK is really, really good, but happens to be hampered by being targeted by Java and JVM bytecode abstraction, which is not as much of a limitation in C# that can trade blows with C++ and Rust quite confidently the moment the abstraction types match (when all use templates/struct generics for example). More on AOT and JIT optimizations (in the case of .NET): - https://devblogs.microsoft.com/dotnet/performance-improvements-in-net-8/#tiering-and-dynamic-pgo https://devblogs.microsoft.com/dotnet/performance-improvemen... - https://migeel.sk/blog/2023/11/22/top-3-whole-program-optimizations-for-aot-in-net-8/ https://migeel.sk/blog/2023/11/22/top-3-whole-program-optimi... If someone has similar authoritative content on what GraalVM Native Image does - please post it.
  • DeathArrow 2y ago
    There is also JIT bytecode.
    • paulddraper 2y ago
      Yes, the timing of that compilation of bytecode and machine code can be either AOT or JIT. For example, Java/JVM compiles bytecode AOT and compiles machine code JIT.
  • tanelpoder 2y ago
    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 required order) Edit: It's worth clarifying that the entire codebase does not run like that, not even the entire plan tree - just the scans and tight vectorized aggregation/join loops on the columns/data ranges that happen to be held in RAM in a columnar format.
    • FridgeSeal 2y ago
      That’s a really cool idea! Is there any writeups or articles about this in more detail?
      • UncleEntity 2y ago
        Sounds like 'copy & patch compilation' is a close cousin.
      • JonChesterfield 2y ago
        It's called a template JIT. You get to remove the interpreter control flow overhead but tend to end up with a lot of register shuffling at the boundaries. Simpler than doing things properly, usually faster than a bytecode interpreter.
    • lolinder 2y ago
      Isn't what you're describing just an interpreter, bytecode or otherwise? An interpreter walks through a data structure in order to determine which bits of precompiled code to execute in which order and with which arguments. In a bytecode interpreter the data structure is a sequential array, in a plain interpreter it's something more complicated like an AST. Is this just the same as "interpret the query plan" or am I missing something?
      • tanelpoder 2y ago
  • GartzenDeHaes 2y ago
    You can also parse the code into an augmented syntax tree or code DOM and then directly interpret that. This approach eliminates the intermediate code and bytecode machine at the cost of slower interpretation. It's slower due to memory cache and branch prediction issues rather than algorithmic ones.
  • fancyfredbot 2y ago
    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.
    • galaxyLogic 2y ago
      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.
      • tristor 2y ago
        > 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? You would hope, but no that's not reality. In reality most of the people "writing SQL" in a business scenario don't even know SQL, they're using an ORM like Hibernate, SQLAlchemy, or similar which is dynamically generating queries that likely subtly change every time the application on top changes.
      • fancyfredbot 2y ago
        I didn't mean to imply that compilation never makes sense, I'm only pointing out that it isn't always the fastest way of executing a query In situations where you have a limited number of queries and these are used repeatedly then some kind of cache for the complied code will be able to amortise the cost of compilation. I certainly agree that a situation where an analyst enters SQL manually is a weird niche and not common. However most applications can dynamically construct queries on behalf of users and so getting a good hit rate on your cache of precompiled queries isn't a certainty.
      • lambdaxyzw 2y ago
        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).
  • SQLite 2y ago
    Performance analysis indicates that SQLite spends very little time doing bytecode decoding and dispatch. Most CPU cycles are consumed in walking B-Trees, doing value comparisons, and decoding records - all of which happens in compiled C code. Bytecode dispatch is using less than 3% of the total CPU time, according to my measurements. So at least in the case of SQLite, compiling all the way down to machine code might provide a performance boost 3% or less. That's not very much, considering the size, complexity, and portability costs involved. A key point to keep in mind is that SQLite bytecodes tend to be very high-level (create a B-Tree cursor, position a B-Tree cursor, extract a specific column from a record, etc). You might have had prior experience with bytecode that is lower level (add two numbers, move a value from one memory location to another, jump if the result is non-zero, etc). The ratio of bytecode overhead to actual work done is much higher with low-level bytecode. SQLite also does these kinds of low-level bytecodes, but most of its time is spent inside of high-level bytecodes, where the bytecode overhead is negligible compared to the amount of work being done.
    • blacklion 2y ago
      Looks like SQLIte bytecode is similar to IBM RPG language from "IBM i" platform, which is used directly, without translation from SQL :) Edit: PRG->RPG.
      • wglb 2y ago
        I'm curious. Would you have a pointer to the documentation of PRG language?
        • blacklion 2y ago
          Oooops, it is IBM RPG, not PRG! My bad! There are some links in Wikipedia. I never used it, but only read big thread about SQL vs RPG on Russian-speaking forum, and there was a ton of examples from person who works with IBM i platform in some big bank. Basic operations look like SQLite bytecodes: open table, move cursor after record with key value "X", get some fields, update some field, plus loops, if's, basic arithmetic, etc.
  • AtlasBarfed 2y ago
    optimizing runtimes (usually bytecode) can beat statically compiled code, because they can profile the code over time, like the JVM. ... which isn't going to close the cold startup execution gap, which all the benchmarks/lies/benchmarks will measure, but it is a legitimate thing. I believe Intel CPUs actually sort of are #2. All your x86 is converted to microcode ops, sometimes optimized on the fly (I remember Intel discussing "micro ops fusion" a decade ago I think).
    • ehaliewicz2 2y ago
      Yeah, for example, movs between registers are generally effectively no-ops and handled by the register renaming hardware.
    • deleted 2y ago
      [deleted]
  • vmchale 2y ago
    APL interpreters are tree-walking, but they're backed by C procedures. Then GCC optimizes quite well and you get excellent performance! With stuff like vector instructions. Getting on par with GCC/Clang with your own JIT is pretty hefty.
• coolandsmartrr 2y ago
  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 https://twitter.com/gorilla0513/status/1784623660193677762
  • jonp888 2y ago
    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.
    • mardifoufs 2y ago
      I've never seen more people use it, as in being actually active on it, and it pops up everywhere due to the community note memes. But yeah I really need to get around creating a Mastodon account since some very good posters moved there too.
    • BiteCode_dev 2y ago
      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.
      • PaulHoule 2y ago
        I’d argue with that bit about creativity and youth. In sports, for instance, if you are talented and 15 there are pipelines into established and pro sports that make sense to follow. If you are 30 and missed your chance for that you can still be a pioneer in an emerging “extreme” sport. A lot of young people seem to think creativity is 99% asking for permission and 1% “just do it”, it takes some experience before people realize it is really the other way around.