An algorithm to optimize database queries that run multiple times https://pubkey.github.io/event-reduce/
An algorithm to optimize database queries that run multiple times
In the browser demo you can see that for randomly generated events, about 94% of them could be optimized by EventReduce. In real world usage, with non-random events, this can be even higher. For the different implementations in common browser databases, we can observe an up to 12 times faster displaying of new query results after a write occurred.
EventReduce uses 18 different state functions
to 'describe' an event+previousResults combination. A state function is a function that returns a boolean value like isInsert()
, wasResultsEmpty()
, sortParamsChanged()
and so on.
Also there are 16 different action functions
. An action function gets the event+previousResults and modifies the results array in a given way like insertFirst()
, replaceExisting()
, insertAtSortPosition()
, doNothing()
and so on.
For each of our 2^19
state combinations, we calculate which action function gives the same results that the database would return when the full query is executed again.
From this state-action combinations we create a big truth table that is used to create a binary decision diagram. The BDD is then optimized to call as few state functions
as possible to determine the correct action of an incoming event-results combination.
The resulting optimized BDD is then shipped as the EventReduce algoritm and can be used in different programming languages and implementations. The programmer does not need to know about all this optimisation stuff and can directly use three simple functions like shown in the javascript implementation
You can use this to
EventReduce only works with queries that have a predictable sort-order for any given documents. (you can make any query predicable by adding the primary key as last sort parameter)
EventReduce can be used with relational databases but not on relational queries that run over multiple tables/collections. (you can use views as workarround so that you can query over only one table). In theory Event-Reduce could also be used for relational queries but I did not need this for now. Also it takes about one week on an average machine to run all optimizations, and having more state functions looks like an NP problem.
At the moment there is only the JavaScript implementation that you can use over npm. Pull requests for other languages are welcomed.
Meteor uses a feature called OplogDriver that is limited on queries that do not use skip
or sort
. Also watch this video to learn how OpLogDriver works.
RxDB used the QueryChangeDetection
which works by many handwritten if-else comparisons. RxDB switched to EventReduce since version 9.0.0.
Baqend is creating a database that optimizes for realtime queries. Watch the video Real-Time Databases Explained: Why Meteor, RethinkDB, Parse & Firebase Don't Scale to learn more.