Unoptomized queries
Because of the way fetching data from the backend is optimized, several features do not work for joined tables (the fields that start with the joindown_ or joinup_ prefix):
- pagination (requesting data in pages and diplaying paging information - the
pageInfofield) - counting the total number of rows that match the filter (the
totalCountfield)
In order to have these features working properly, you must explicitly request that the execution of the query is not optimized by the system. This is done with a Boolean field argument called unoptimized. The way this works is described below. First, let’s understand the problem.
Query optimization
When you request data from joined tables we are faced with a problem called the N+1 problem. Let’s say you want to fetch the last 10 orders of a customer, but along with the order heads also the order lines info. Typically, this means we will do one request to fetch the order heads first, and then, for each order, we perform one request to fetch the lines. For 10 orders that is 10 more request for the lines, amounting to a total of 11 requests. For N orders, that amounts to N+1 requests. This incurs a performace loss and this is aggravated if more tables are joined together. For instance, fetching the orders, and for each order the order lines, and for each order line the order notes, just to give an example.
To avoid the performance penalty, our system is optimizing the queries. As a result, we are performing only 2 requests instead of N+1. For the orders and orders line example, we perform a first request to fetch the heads and then a second request to fetch the lines.
The result is a potential 10x or more speed-up for executing a query. The downside is the features mentioned above no longer work.
The problem with the optimization
The understand the problem with optimization let us discuss the following scenario.
Consider a query that fetches the first 10 orders and for each order the first 3 order lines. That means that for each order, the query should return a maximum of 3 order lines, therefore, potentially, a total of 30 order lines in total. However, some orders may have less than 3 order lines others may have more. The current implementation takes the first 30 order lines (that match the given filter, if any) regardless to which order they belong. Let’s explain this with an example:
| Order no | No. of order lines | No. of expected returned lines | No. of actual returned lines |
|---|---|---|---|
| 1 | 2 lines | 2 lines | 2 lines |
| 2 | 5 lines | 3 lines | 5 lines |
| 3 | 2 line | 2 lines | 2 lines |
| 4 | 1 lines | 1 lines | 1 lines |
| 5 | 7 lines | 3 lines | 7 lines |
| 6 | 3 lines | 3 lines | 3 lines |
| 7 | 5 lines | 3 lines | 5 lines |
| 8 | 8 lines | 3 lines | 5 lines |
| 9 | 5 lines | 3 lines | 0 lines |
| 10 | 2 lines | 2 lines | 0 lines |
The third column in this table shows the expected result when you ask for the first 3 order lines for each order. What is returned instead is the data of the fourth column. This is shown with the following query:
query ($cid :Int!, $pagesize : Int)
{
useCompany(no: $cid)
{
order(first: $pagesize)
{
totalCount
items
{
orderNo
joindown_OrderLine_via_Order(first: 3)
{
items
{
lineNo
}
}
}
}
}
}
{
"data": {
"useCompany": {
"order": {
"totalCount": 348,
"items": [
{
"orderNo": 1,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
}
]
}
},
{
"orderNo": 2,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
},
{
"lineNo": 4
},
{
"lineNo": 5
}
]
}
},
{
"orderNo": 3,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
}
]
}
},
{
"orderNo": 4,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
}
]
}
},
{
"orderNo": 5,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
},
{
"lineNo": 4
},
{
"lineNo": 5
},
{
"lineNo": 6
},
{
"lineNo": 7
}
]
}
},
{
"orderNo": 6,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
}
]
}
},
{
"orderNo": 7,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
},
{
"lineNo": 4
},
{
"lineNo": 5
}
]
}
},
{
"orderNo": 8,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
},
{
"lineNo": 4
},
{
"lineNo": 5
}
]
}
},
{
"orderNo": 9,
"joindown_OrderLine_via_Order": {
"items": null
}
},
{
"orderNo": 10,
"joindown_OrderLine_via_Order": {
"items": null
}
}
]
}
}
}
}
The reason for this is that we fetch a total of 10*3 order lines from the order lines table, in a single request. This means the result is not what was actually requested from with the query.
Notice this is not a problem when no pagination is requested. The following example, that fetches some orders and all their order lines works without any problem.
query ($cid :Int!, $pagesize : Int)
{
useCompany(no: $cid)
{
order(first: $pagesize)
{
totalCount
items
{
orderNo
joindown_OrderLine_via_Order
{
items
{
lineNo
}
}
}
}
}
}
{
"data": {
"useCompany": {
"order": {
"totalCount": 348,
"items": [
{
"orderNo": 1,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
}
]
}
},
{
"orderNo": 2,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
},
{
"lineNo": 4
},
{
"lineNo": 5
}
]
}
},
{
"orderNo": 3,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
}
]
}
},
{
"orderNo": 4,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
}
]
}
},
{
"orderNo": 5,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
},
{
"lineNo": 4
},
{
"lineNo": 5
},
{
"lineNo": 6
},
{
"lineNo": 7
}
]
}
},
{
"orderNo": 6,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
}
]
}
},
{
"orderNo": 7,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
},
{
"lineNo": 4
},
{
"lineNo": 5
}
]
}
},
{
"orderNo": 8,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
},
{
"lineNo": 4
},
{
"lineNo": 5
},
{
"lineNo": 6
},
{
"lineNo": 7
},
{
"lineNo": 8
}
]
}
},
{
"orderNo": 9,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
},
{
"lineNo": 4
},
{
"lineNo": 5
}
]
}
},
{
"orderNo": 10,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
}
]
}
}
]
}
}
}
}
However, fetching the total count of order lines per each order and displaying paging information for each chunk of order lines does not work in this case either.
Unoptimized queries
To solve the problem of the optimized queries for joined tables, you can explicitly request to run the query without optimizations. This means there will be N+1 requests to the backend and the execution time will increase significantly. However, counting records from the joined table and fetching data in pages works as expected.
The unoptimized execution is requested with a Boolean argument for the field called unoptimized that must be set to true. This is shown in the following example:
query ($cid :Int!, $pagesize : Int)
{
useCompany(no: $cid)
{
order(first: $pagesize)
{
totalCount
items
{
orderNo
joindown_OrderLine_via_Order(first: 3,
unoptimized: true)
{
items
{
lineNo
}
}
}
}
}
}
{
"data": {
"useCompany": {
"order": {
"totalCount": 348,
"items": [
{
"orderNo": 1,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
}
]
}
},
{
"orderNo": 2,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
}
]
}
},
{
"orderNo": 3,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
}
]
}
},
{
"orderNo": 4,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
}
]
}
},
{
"orderNo": 5,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
}
]
}
},
{
"orderNo": 6,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
}
]
}
},
{
"orderNo": 7,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
}
]
}
},
{
"orderNo": 8,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
}
]
}
},
{
"orderNo": 9,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
},
{
"lineNo": 3
}
]
}
},
{
"orderNo": 10,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1
},
{
"lineNo": 2
}
]
}
}
]
}
}
}
}
The unoptimized flag is an argument to a connection (see Queries). However, a connection may contain other inner connections. These are the joindown tables discussed here and seen in the previous examples.
When you are requesting the fetching of data in an unoptimized manner, this applies not only to the connection on which the argument was specified, but also on all its descendants.
This behavior can be overridden by using the unoptimized argument again on a descendant connection. Here are several examples:
- the order lines are fetched in an optimized manner but not the order line notes
query ($cid :Int!, $pagesize : Int)
{
useCompany(no: $cid)
{
order(first: $pagesize)
{
totalCount
items
{
orderNo
joindown_OrderLine_via_Order(first: 3)
{
items
{
lineNo
joindown_OrderLineNote_via_OrderLine(unoptimized : true)
{
items
{
note
}
}
}
}
}
}
}
}
- both the order lines and the order line notes are fetched unoptimized
query ($cid :Int!, $pagesize : Int)
{
useCompany(no: $cid)
{
order(first: $pagesize)
{
totalCount
items
{
orderNo
joindown_OrderLine_via_Order(first: 3, unoptimized : true)
{
items
{
lineNo
joindown_OrderLineNote_via_OrderLine
{
items
{
note
}
}
}
}
}
}
}
}
- the order lines are fetched unoptimized but the fetching of order line notes is optimized
query ($cid :Int!, $pagesize : Int)
{
useCompany(no: $cid)
{
order(first: $pagesize)
{
totalCount
items
{
orderNo
joindown_OrderLine_via_Order(first: 3, unoptimized : true)
{
items
{
lineNo
joindown_OrderLineNote_via_OrderLine(unoptimized : false)
{
items
{
note
}
}
}
}
}
}
}
}
Using the unoptimized flag on a top-level table (such as the orders table in these examples) has no effect on fetching data from that table. However, it will affect the manner of fetching data from all its joined tables (the fields prefixed with either joindown_ or joinup_).
Asynchronous execution
Executing the queries with joined down tables fast and having pagination or counting the total items in the joined down table are mutually exclusive. However, if you do need these features but do not want to wait for the execution of the query you can execute the query asynchronously.
To learn more about this, see Async queries.