First Query Very Slow (~3s) on Chrome (64.0.3282.167)

[soaxelbrooke]

I'm seeing slow first queries against an database loaded via XHR. All the queries are fairly trivial, and after the first they complete quickly. Here's a picture of the chrome dev-tools:

Firefox (58.0.2) doesn't have this problem, so perhaps something browser related?

The following JS recreates this:

let db = null;

let experimentUrl = 'https://storage.googleapis.com/axelbrooke-public/experiments/toxic-comment-clf/experiments.sqlite';

(function() {
    let xhr = new XMLHttpRequest();
    xhr.open('GET',
        experimentUrl,
        true);
    xhr.responseType = 'arraybuffer';

    xhr.onload = function (e) {
        let uInt8Array = new Uint8Array(this.response);
        db = new SQL.Database(uInt8Array);
        console.log(new Date(), 'Database created.');
        console.log(queryDb('SELECT * FROM hparams'));
        console.log(new Date(), 'Database loaded.');
        app();
    };
    xhr.send();
})();

[soaxelbrooke]

Problem is even worse on mobile Chrome 😓

[bmcbride]

This still appears to be an issue. Any updates on a possible fix for this? Thanks!

[lovasoa]

It should improve once we switch to wasm. See #255

[bmcbride]

@lovasoa I tested with @Taytay latest wasm build at https://github.com/Taytay/sql.js/tree/wasm/dist and while it definitely seems to improve performance on Chrome desktop, Chrome mobile on Android/ioS still takes a long time for the initial load.

[Taytay]

I don't know much about WASM support on Chrome Mobile. Can you tell from any sort of dev tools (not even sure if they are available) where the time is being spent? Is it on compilation of the .wasm file that gets streamed, or is it truly on the first call into the WASM where you see this penalty? I am wondering if this is some sort of memory allocation issue on the mobile browsers? From your screenshot, it looks like the new sql.Database is pretty fast though. Do you see this same issue on smaller DBs or empty databases calling something simple like CREATE TABLE?

[celasoft]

This does appear to be still an issue on mobile chrome. The first query takes anywhere from 3 to 10 seconds depending on device, I don't know of any tools to check where this is happening but my experience is loading the database from uInt8Array happens quickly and the penalty only appears on the very first query. All subsequent queries are fast as expected. This is for a 2mb database and any first query no matter how complex or simple.

I've tried both with and without WASM and results are the same.

I don't see this issue in mobile Safari.

[bmcbride]

FWIW - explicitly enabling the "WebAssembly compiled module cache" flag in Chrome on Android seems to significantly improve initial load time. I came across this thread, which is way over my head, but it all looks encouraging!

chrome://flags/#enable-webassembly-code-cache