11,863 reads

Execute millions of SQL statements in milliseconds in the browser with WebAssembly and Web Workers.

by Mike ParsonsJanuary 14th, 2017

Too Long; Didn't Read

That’s a pretty long title but hopefully it caught your attention!

Companies Mentioned

featured image - Execute millions of SQL statements in milliseconds in the browser with WebAssembly and Web Workers.

That’s a pretty long title but hopefully it caught your attention!

I’ve always been fascinated by what can be achieved in terms of HTML and JavaScript in the browser. The amazing work being done by browser vendors on JavaScript performance is nothing short of amazing. With each iteration of Chrome V8, Microsoft Chakra, Mozilla SpiderMonkey and Apple JavaScriptCore, JavaScript performance narrows the gap with native code execution.

WebAssembly (http://webassembly.org/) is the latest innovation that brings us one step close to true native code execution in the browser. I wanted to see just how far we have come so I threw together an example that tests a realistic code base, in this case, the Sqlite database — http://sqlite.org/. For my test, I used the Emscripten compiled version of sqlite found here — https://github.com/kripken/sql.js/.

My tests are using the latest version of Chrome Canary with the following flag set to enabled:

chrome://flags/#enable-webassembly

My test machine is a Dell XPS desktop with :

Processor Intel(R) Core(TM) i7–4770 CPU @ 3.40GHz, 3401 Mhz, 4 Core(s), 8 Logical Processor(s)

24 Gigs of RAM256 SSD Drive

I created 2 files, the main index.html

<html><head><title>WebAssembly Test</title><script>

var aListeners = [];var results=[];var times=[];var workers=[];

//load up our workersfor(var i=0;i<8;i++){workers[i]=new Worker("worker.js");workers[i].onmessage = function (oEvent) {if (aListeners[oEvent.data.id]) { aListeners[oEvent.data.id](oEvent.data.evaluated,this); }delete aListeners[oEvent.data.id];};}

function getRandomWorker(arr) {return arr[Math.floor(Math.random()*arr.length)];}

var asyncEval = (function () {return function (worker, sCode, fListener) {aListeners.push(fListener || null);worker.postMessage({"id": aListeners.length - 1,"code": sCode});};

})();

//Our test functionfunction test(numReq,code) {console.time("Total_Elapsed");times=[];results=[];times.push((new Date()).valueOf());var num=numReq || 10000;for(var i=0;i<num;i++) {asyncEval(getRandomWorker(workers),code || "Date()", function (result,worker) {times.push((new Date()).valueOf());results.push(result);if(results.length==num) {console.timeEnd("Total_Elapsed");console.log("Processing Time for all workers: "+(parseInt(times[times.length-1])-parseInt(times[0])).toString()+"ms");var numRequests=results.length*parseInt(results[0].split(": ")[1]);console.log("Total sql statements processed: "+numRequests);}});};return "Executing test ...";}</script></head><body>Press F12 to view console</body></html>

and the Web Worker script worker.js

importScripts("sql.js");

//Init sqlite databasevar db = new SQL.Database();

//Our test functionvar run_sql=function(numReq,sql) {var n=numReq || 10000;var results=[];var elapsed=0;var stmt = db.prepare(sql || "select datetime() as dt;");

return elapsed;}

//Run code to init JIT compilationrun_sql();

//Message Handleronmessage = function (oEvent) {postMessage({"id": oEvent.data.id,"evaluated": process(oEvent.data.code)});}

function process(code) {var result=null;try {result=eval(code);

}catch(e){result = "Error: "+e.message;}return result;}

To run the test, you need to host the three files, index.html, worker.js and sql.js on a web server. I’m using IIS on my Window’s machine but any static web server on any OS should do.

When you first load index.html, you get a simple message telling you to access the development tools in the browser. On my machine, it looks like this

The first thing you will notice is the fine folks working on Chrome takes care of converting sql.js into WebAssembly. The details of how all this works are beyond the scope of this article but you can find out more here:

https://github.com/kripken/sql.js

and here

Developer's Guide - WebAssembly_To compile to WebAssembly, we will at the moment need to compile LLVM from source. The following tools are needed as a…_webassembly.org

To run our first test, we type the following into the developer console:

test(1,"run_sql(1000,'select datetime() as dt;')")

This executes the SQL statement ‘select datetime() as dt;’ 1000 times in a loop within one Web Worker. The results on my machine are as follows:

So we processed 1000 SQL requests in 8.29 milliseconds.

Let’s try it with 10,000 SQL requests in a single web worker:

So we processed 10,000 SQL requests in 67 milliseconds. Since I have 8 cores on my machine, I configured 8 web workers, so lets run the test again, spread across these web workers:

So we can process 10,000 SQL requests in roughly half the time using web workers.

How about 100,000 SQL requests:

Not too bad, 100,000 SQL requests in ~ 211 milliseconds.

The title of the article talks about doing millions of SQL requests in a few seconds so let’s run the test for 1,000,000 SQL requests:

On my machine, it takes

~ 2 seconds to run a million SQL select statements!

How about database inserts? The following code inserts a million records into a SQL table:

So …

~ 2.2 seconds to insert a million rows in a table

That’s pretty darn impressive and WebAssembly is just in it’s infancy.

Obviously, these tests are very simple and depending on the nature of your SQL application, your results may vary. It does illustrate, however just how far we have come in terms of raw JavaScript performance in the browser.

I’d love to hear your feedback so please recommend the article to your followers :-)