Subtable query under the hood

[Mr.Rubi]

I am working on optimizing the backend of my project and exploring the optimization of database queries. In the course of numerous tests and comparisons, it turned out that the most productive solution is the standard Wappler queries with subtables. This outstripped even MongoDB queries in speed, where documents with the entire complex structure were stored in the database. It is also faster than custom queries with aggregation to obtain a complex structure of documents. And so, in order to completely close the optimization needs, I lack some features when using sub-table queries in the Wappler:

1. Important! The ability to query sub-tables on several levels. If there are more sub-tables in the sub-table, when we request, we will receive data only of the 1st level of the sub-table. Data acquisition from all levels is required.
2. There is not enough opportunity to select certain fields when selecting a subtable.
3. There is not enough possibility to attach data from another table when selecting a subtable.

@patrick please tell me exactly what a sub-table query looks like in the code? Is this a separate request? Is the data merging happening outside the database?

These questions would help me recreate a complex query with my settings and get the maximum possible speed of receiving data from the server.

[George]

@Mr.Rubi

it turned out that the most productive solution is the standard Wappler queries with subtables. This outstripped even MongoDB queries in speed, where documents with the entire complex structure were stored in the database. It is also faster than custom queries with aggregation to obtain a complex structure of documents.

Great to hear, yes sub queries are indeed very optimized and do merging of data on the fly.

See more details on:

Sub Tables and Nested Queries Versus Views

Well sub tables or just related one-to-many tables that you probably already have. And Multi References are Junctions Tables for Many-to-many relations. Wappler’s Database query builder is optimized so it does the most optimal sub query to get the nested data only for the selected parents with minimal number of queries. MySQL Views are just predefined queries. The speed of queries depends on the number of indexes and how well data is linked. So the speed all depends on the type of data and q…

Joins vs Sub Tables

Sub tables are just like any other tables with relation to a parent table. You probably want to compare joins agains nested queries with sub tables. The difference between those two is the amount of data and the structure. There isn’t much of performance difference. A regular query with a join to a sub table will actually produce a lot of data replicating the parent records to all sub table records and you will need to filter or group this data later on. It is done in a single query. While a…

I’m sure @patrick can also provide some insights

[patrick]

@Mr.Rubi

please tell me exactly what a sub-table query looks like in the code? Is this a separate request? Is the data merging happening outside the database?

There are queries for each table. The main table is retrieved first. Then the sub table is queried, the query is extended to be limited only to the ids which were retrieved from the main table. After that both results are being merged based on the relation (primary and foreign key).

For example, as the main query you get all admins from the users table, we extend the query to include the key

SELECT *, id AS __dmxPrimary FROM users WHERE role = 'admin'

Then as with a sub table we want to retrieve some detail information, the main query returned 2 admins with id 1 and 5. The query for the sub table looks like:

SELECT *, id AS __dmxPrimary, userid AS __Foreign FROM userdetails WHERE userid IN (1, 5)

The __Foreign column in the sub results tells us which result from the main it belongs to. We loop through the main results and lookup the sub results and then merge it together.

There will only be a single query per table, if you have multiple sub tables it will create a query for each one. Normally when not optimized you would see users query the main table and then loop the results and query the sub table for each result, with our implementation we have optimized this.

In my example above the best optimization would be to have the userdetails combined within the users table since it is a 1 on 1 relation, to get it still as a structured data we could have put it in a JSON type column.

[Mr.Rubi]

@patrick

For example, as the main query you get all admins from the users table, we extend the query to include the key

SELECT *, id AS __dmxPrimary FROM users WHERE role = 'admin'

Then as with a sub table we want to retrieve some detail information, the main query returned 2 admins with id 1 and 5 . The query for the sub table looks like:

SELECT *, id AS __dmxPrimary, userid AS __Foreign FROM userdetails WHERE userid IN (1, 5)

The __Foreign column in the sub results tells us which result from the main it belongs to. We loop through the main results and lookup the sub results and then merge it together.

Thanks @patrick for the clarification. When I was thinking about how it works, I imagined exactly such an algorithm.

It turns out that these are two separate queries. In this case, I can recreate this by two normal queries, where the second query will get the record id of the first one as a filter. I can also call a third query that will take as a filter the id from the second query if I need to get several levels of nesting. Then I need to get this data into my module, in which to combine by iterating through the first array and attaching data from the second array to it. Theoretically, this will be the same thing that happens inside the Wappler query when we query a subtable?

If so, could you write a sample code to combine the query data that you are using (in nodejs)? Based on it, I will try to make a custom module that will do the union based on my parameters.