Cosmos DB SDK Query speeds are really slow compared to query statistics in Azure Portal

Question

I created a POC to test Cosmos Db. For that purpose i created a container. Each Partition Key has 120 item. I provided 500 "partitions" so it means, that i have 60k documents loaded into container.

My document looks like this:

{
    "id": // id
    "SteamId": // partition key as string
    "Profile": {
        // object
    }
}

One document is 5kb of size.

For that scenario i prepared indexing policy tailored for my needs:

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/SteamId/?"
        }
    ],
    "excludedPaths": [
        {
            "path": "/*"
        },
        {
            "path": "/\"_etag\"/?"
        }
    ]
}

When i execute query in portal:

SELECT c.Profile FROM c where c.SteamId = "76561198053126645"

Thats my stats:

So it looks really fast and efficient. I try to download full partition which is 500kb of data.

And now i prepared really simple c# code:

        public async Task<IActionResult> test(string steamid)
        {
            CosmosClient client = new(
                accountEndpoint: "endpoint",
                tokenCredential: new DefaultAzureCredential()
            );
            
            Database database = client.GetDatabase("items");
            Container container = database.GetContainer("items3");

            var stopwatch = new Stopwatch();
            var queryDefinition = new QueryDefinition($"SELECT c.Profile FROM c where c.SteamId = {steamid}");
            var iterator = container.GetItemQueryIterator<aaa>(queryDefinition,
                requestOptions: new QueryRequestOptions()
                {
                    PartitionKey = new PartitionKey(steamid),
                });

            var results = new List<aaa>();

            stopwatch.Start();

            while (iterator.HasMoreResults)
            {
                var result = await iterator.ReadNextAsync();
                results.AddRange(result.Resource);
            }
            stopwatch.Stop();
            

            return Ok(new { items = results, time = stopwatch.ElapsedMilliseconds });
        }

And that request through SDK take 10 seconds to load the same query that in portal took 1 ms. What am i doing wrong in SDK that it take so much time in local environment? I am living in the same region where Cosmos Db resource is provisioned. I am using SDK version 3.36 for Microsoft.Azure.Cosmos

Metrics from Cosmos DB SQL Studio:

Answer 1

Analyzing latency on a post here is rather impossible as it is not a programming problem. Latency should be analyzed throughout a longer period of time than 1 operation.

Particularly because connections always have a warm-up phase, and according to https://learn.microsoft.com/azure/cosmos-db/nosql/sdk-connection-modes#direct-mode, the first ever operation you do initializes and bootstraps the client with all the information needed to route requests, it will always have higher latency. This is known generally as cold start.

Services measure latency on P99 basis.

In general, when looking at latency troubleshooting, the main article is: https://learn.microsoft.com/azure/cosmos-db/nosql/troubleshoot-dotnet-sdk-slow-request?tabs=cpu-new