Querying large amounts of data in Azure Cosmos SQL API

Question

We have a simple 3 tier application built in Angular, .Net Core 3.1 Web API, and Azure Cosmos database.

This application gathers large amounts of data, including geo location (lat and long), which then users can query and view via the Angular app.

Our problem is that need to query the full dataset to visualize it in a map and we're running into significant performance issues when querying the data.

There are currently 2.5 million records in the database, and it takes ~10 mins to pull them all, with a RU configuration of 200000 RUs. Note, the documents being returned are fairly small, only 4 attributes including the id which is a GUID. Total response size for the 2.5 mill is just under 100MB

After the latest test, had a look at App Insights and got the following data.

It's obvious we don't have an issue with RUs, that means our code/implementation is not harnessing the full power of the allocated throughput.

Below the code we use to pull the records. We've made some modifications based on this post but so far nothing has made an impact.

If anyone can suggest any changes to our implementation that would be highly appreciated.

public async Task<List<object>> RunQuery(string query, string tenantAcronym)
{
   CosmosClient client = new CosmosClient(_endpoint, _accountKey);
   var cosmosDatabase = client.GetDatabase(_databaseId);
   Container container = cosmosDatabase.GetContainer(tenantAcronym);
   QueryRequestOptions options = new QueryRequestOptions() { MaxBufferedItemCount = -1 };
   options.MaxConcurrency = -1;

   var resIterator = container.GetItemQueryIterator<object>(
     new QueryDefinition(query, requestOptions: options)
   );

   var res = new List<object>();
   while (resIterator.HasMoreResults)
   {
     var response = await resIterator.ReadNextAsync();
     res.AddRange(response);
   }

   return res;
}

Getting all the data every time is not a scalable approach. Think about what would happen when your data doubles or triples? Are you doing any data aggregation or something else after getting the data? — Gaurav Mantri
No data aggregation. These records are location data points so they need to be extracted as they are to be plotted in a map. Yes, I agree with your scalability statement, but those are the rules we're playing with. At the moment I'm convinced (or hopefully at the very least) that we can improve that query time. The current amount of records have been collected over the last 6 years, so it'll be some time before, say, this data has doubled or tripled in size — Pilsen
In cosmodb your result time gets calculated based on RU unit and to understand that you need to understand the how RU unit gets calculated. Thats the limitations of the application and its fully dependent on that. Now to overcome this scenario you can try the parallel programming approach. — Rahul Shukla

Stephen Cleary Stephen Cleary · Accepted Answer · 2021-04-19T14:50:05

I strongly suspect that the problem is that there's just too many individual records to efficiently read all of them at once. Going back to the original problem description and some metrics:

Our problem is that need to query the full dataset to visualize it in a map and we're running into significant performance issues when querying the data.

There are currently 2.5 million records in the database, and it takes ~10 mins to pull them all... Total response size for the 2.5 mill is just under 100MB

The first thing of note is that there is no way that a usable UI can realistically display 2.5 million elements. There has got to be some kind of aggregation going on for the UI to be remotely useful. The solution is to take whatever aggregation is happening on the UI side and push it down into the Cosmos DB.

For example, if the current map UI aggregation is by country when zoomed out and by city when zoomed in, then you'd need change feed processors on the original collections that do the aggregation and place the results in other collections. Then the API will query only the pre-aggregated data that the UI currently needs.

If the UI supports arbitrary zooming/panning, then you'll probably need to aggregate "tiles" at multiple "zoom levels" for a complete solution. Essentially what Google Maps does.

The core idea is to take whatever aggregation is currently in the UI and push that down into the DB.

Querying large amounts of data in Azure Cosmos SQL API

1 Answers