Does Data API Builder support parallel queries & API calls ?

[rajendraas]

We are using Data API Builder to connect to MSSQL server. We are able to establish the connection & run queries as well.

1. When we are trying to hit /graphql API in parallel, the server seems to wait for other request to process, and that is causing the performance issue. Is the Data API Builder handling parallel API calls ?

Ex - This example show the time taken by API to reply when run in parallel, vs time taken when run individually.

API call number | time taken when run individually | time taken when run parallely
1 | 100 ms | 100 ms
2 | 135 ms | 235 ms
3 | 425 ms | 660 ms
4 | 105 ms | 765 ms

2. When we are trying to run multiple queries at once in the single API call, the execution also seems to be sequential considering the total time taken for the query is addition of all the subqueries.
   Ex- This is the kind of combination we tried to execute. Note that allBooks & allAuthors are fetched in single query, and not 2 separate.

{
allBooks {
items{ID}
}
allAuthors {
items{ID}
}
}

If the time taken for allBooks is 40 ms & time taken for allAuthors is 50 ms, then the query is taking 90 ms. Should this whole query make parallel calls to DB for each query in it ?

The DB doesn't seem to be a bottleneck that is blocking anything for the DAB / Graphql server.

Does Data API Builder support parallel queries & API calls ?

[rajendraas]

Hello, is there any update on above question ?

Adding on top above description, when multiple users are using the application, we are not able to serve all users, as there is a timeout for some requests. Again, Data API Builder is waiting for previous call to complete.

There seems to be only one query at a time per connection between DAB & MSSQL. If this is the case, Data API Builder cannot be used in production.

Can someone please help here ? Are we missing anything in our setup, or parallel requests are not supported in Data API Builder itself ? If it is not supported now, is there any timeline for this ?

[severussundar]

Hi @rajendraas, thank you for the questions.

For your 1st question, could you please share more details about the workload you are running
a) Number of queries you are running in parallel.
b) The exact queries that are being executed in parallel.
c) From our testing, we've observed that multiple parallel requests run in parallel. But, would very much love to understand your use-case which is hitting the case where sequential execution happens. If you could share more details about your setup, it would be helpful for us to repo and investigate.

[rajendraas]

Hi @severussundar , thanks for the reply :)

I tried to answer your questions as much as I could. Hope this helps.

1. We tested with 9 concurrent queries. Following is the result with sequential & parallel calls. I've used Faker here just to mention which API took how much time.

Time taken by individual query when called sequentially -

starting - Leslie at 2023-10-05T14:40:01.990352400Z
Time taken for Leslie is - 2063
starting - Lamar at 2023-10-05T14:40:04.055779700Z
Time taken for Lamar is - 110
starting - Vashti at 2023-10-05714:40:04.166926Z
Time taken for Vashti is - 57
starting - Synthia at 2023-10-05T14:40:04.224088Z
Time taken for Synthia is - 1289
starting - Titus at 2023-10-0514:40:05.514451900Z
Time taken for Titus is - 106
starting - Palma at 2023-10-05714:40:05.621658400Z
Time taken for Palma is - 1504
starting - Candra at 2023-10-05T14:40:07.126681900Z
Time taken for Candra is - 123
starting - Contessa at 2023-10-05T14:40:07.251317800Z
Time taken for Contessa is - 1536
starting - Dwight at 2023-10-05714:40:08.788769600Z
Time taken for Dwight is - 53

Time taken when same queries are executed in parallel -

starting - Randell at 2023-10-05T14:42:37.866748Z
starting - Effie at 2023-10-0514:42:37.867732800Z
starting - Juliane at 2023-10-0514:42:37.865738700Z
starting - Mohamed at 2023-10-05T14:42:37.867732800Z
starting - Krystyna at 2023-10-05T14:42:37.867732800Z
starting - Lemuel at 2023-10-0514:42:37.8667487
starting - Vanita at 2023-10-05T14:42:37.866748Z
starting - Cristen at 2023-10-05T14:42:37.867732800Z
Time taken for Vanita is - 920
starting - Larry at 2023-10-05T14:42:38.7897305007
Time taken for Larry is - 75
Time taken for Mohamed is - 1224
Time taken for Randell is - 1753
Time taken for Effie is - 1994
Time taken for Cristen is - 3526
Time taken for Krystyna is - 5954
Time taken for Juliane is - 6086
Time taken for Lemuel is - 6230

2. Due to limitations, I wont be able to give all the details. But the typical structure for all queries is something like this -

{
    authors(filter: { and: [ { first_name: { eq: "Robert" } } { last_name: { eq: "Heinlein" } } ] }) {
    items {
      id
      title
    }
  }
}

Each query has filter on eq and a list of columns. We don't have a structure in the items list. The number of columns vary though, from 2 to approx 20 columns.

3. We have setup the Data API Builder as mentioned in the documentation here - https://learn.microsoft.com/en-us/azure/data-api-builder/

• We have mix of columns & views in the entities.
• The connection string doesn't have any extra properties, and has only basics.
• We are doing read-only operations. There are no mutations.