How does MaxItemCount from FeedOption and RetrievedDocumentCount from QueryMetric works in Cosmos DB and why both never match?

user_5118386 0
Viewed 1185

I am currently facing query performance issue with Cosmos DB and I am quite sure I have followed most of the performance tips from Microsoft page but still query takes > 1 second.

Connection policy

private static readonly ConnectionPolicy ConnectionPolicy = new ConnectionPolicy
    {
        ConnectionMode = ConnectionMode.Direct,
        ConnectionProtocol = Protocol.Tcp,
        RequestTimeout = new TimeSpan(1, 0, 0),
        MaxConnectionLimit = 1000,
        RetryOptions = new RetryOptions
        {
            MaxRetryAttemptsOnThrottledRequests = 10,
            MaxRetryWaitTimeInSeconds = 60
        }
    };

Document Client

this.Client = new DocumentClient(new Uri(config.DocumentDBURI), config.DocumentDBKey, ConnectionPolicy);

Document Query

FeedOptions options = new FeedOptions
                {
                    MaxItemCount = config.getSearchLimit,//// which is 100
                    PartitionKey = new PartitionKey(partitionKey),
                    RequestContinuation = responseContinuation
                };

                var documentQuery = Client.CreateDocumentQuery<SearchByAttributesResult>(
                    this.TenantCollectionUri,
                    querySpec,
                    options).AsDocumentQuery();

Query 1

SELECT p.Doc.id, p.Doc.Name, p.Doc.isOrganization,p.Doc.organizationLegalName, p.Doc.isFactoryAutoUpdate,p.Doc.StartDate, p.Doc.EndDate, p.Doc.InactiveReasonCode,p.Doc.Specialty.specialty AllSpecialty, Address from p JOIN Address IN p.Doc.Address.address  WHERE  (p.Doc.EndDate = null or (p.Doc.StartDate <= @STARTDATE and p.Doc.EndDate >= @ENDDATE)) and  CONTAINS(p.Doc.Name, @PROVIDERNAME) and  Address.alpha2Code= @ALPHA2CODE

Query 2

    SELECT p.Doc.id, p.Doc.Name, p.Doc.isOrganization,p.Doc.organizationLegalName, p.Doc.isFactoryAutoUpdate,p.Doc.StartDate, p.Doc.EndDate, p.Doc.InactiveReasonCode,p.Doc.Specialty.specialty AllSpecialty, Address from p JOIN Address IN p.Doc.Address.address  WHERE  (p.Doc.EndDate = null or (p.Doc.StartDate <= @STARTDATE and p.Doc.EndDate >= @ENDDATE)) and  STARTSWITH(Address.postalCode, @POSTALCODE) and  Address.alpha2Code= @ALPHA2CODE

above query changes based on user search condition

I have only 900 documents in my collection but still query takes > 1 seconds always.

trying to understand few points here

  • Though I set MaxItemCount to 100 why I am seeing RetrievedDocumentCount from QueryMetrics as 900?
  • use of CONTAINS/STARTSWITH causing this performance issue?

What's wrong I am doing here and how can i improve this query performance into sub-seconds ( <.5s)

azure-cosmosdb
1 Answers

First things first, MaxItemCount doesn't mean that you will get the top 100 documents.

It means that every iteration of ExecuteNextAsync will return up to 100 documents at a time, but up to everything that matches this query.

If you want to limit your results to the top 100 then, in LINQ use the .Take(100) method before you use AsDocumentQuery or in SQL use the TOP keyword.

In terms of performance, it's bad for three reasons.

  • Checking for records between range of dates
  • You are using the CONTAINS/STARTSWITH function.
  • You are joining

At this point, if changing the schema isn't an option, I would recommend reading more about Indexing and optimising it based on the querying requirements of your application.

user_75239740
Related