CosmosDB: Linq vs SqlQuerySpec Performance when querying CosmosDB

Question

I have regularly performed LINQ predicates when querying CosmosDB specific documents. However, today I filled up my CosmosDB with 100 k+ documents. The performance was very slow. As the SQL Query in the Azure Portal was noticeably faster, I tried using SqlQuerySpec. Voilá! It worked so much faster.

Can anyone tell me the what is happening under the hood, when using Linq predicates with CosmosDB and why it slows my queries down?

The below code is used in my method for getting the document. NB: The id is the partition key in this case.

        var collectionUri = UriFactory.CreateDocumentCollectionUri(CDBdatabase, CDBcollection);

        var sqlStatement = new SqlQuerySpec
        {
            QueryText = "SELECT * FROM c where c.id = @id",
            Parameters = new SqlParameterCollection()
                {
                          new SqlParameter("@id", consumerId),
                },
        };

        IDocumentQuery<T> query = documentClient.CreateDocumentQuery<T>(
            collectionUri,
            sqlStatement,
            .AsDocumentQuery();

        List<ConsumerDetails> results = new List<ConsumerDetails>();
        while (query.HasMoreResults)
        {
            results.AddRange(await query.ExecuteNextAsync<ConsumerDetails>());
        }

        return results.FirstOrDefault();

Versus, the slower code:

        return documentClient.CreateDocumentQuery<ConsumerDetails>(
            collectionUri,
            .Where(f => f.Id == consumerId).AsEnumerable().FirstOrDefault();

Slightly unrelated to your question but, if you are truly querying by a document's id, then ReadDocument() will be faster than a query, always (1 RU for 1KB doc, for example), as it doesn't need to go through the query engine. — David Makogon
You could call ToString() on IQueryable obtained from LINQ chain and inspect the difference between manually built query with those one from CosomsDB linq provider. — Vitaliy Kalinin
Ok there are several things missing to give a good answer. Can you please post how your ConsumerDetails DTO looks like? Did you try providing the partition key value as part of the FeedOptions object on the LINQ approach? Did you try doing .AsDocumentQuery and ExecuteNextAsync on the linq query? — Nick Chapsas

Nick Chapsas Nick Chapsas · Accepted Answer · 2018-08-29T16:29:29

The answer lies on the way you query.

The two code piece that you posted, are not the same.

For them to be the same the second one would have to look like this:

var collectionUri = UriFactory.CreateDocumentCollectionUri(CDBdatabase, CDBcollection);

var query = documentClient.CreateDocumentQuery<ConsumerDetails>(
    collectionUri)
    .Where(f => f.Id == consumerId)
    .AsDocumentQuery();

List<ConsumerDetails> results = new List<ConsumerDetails>();
while (query.HasMoreResults)
{
    results.AddRange(await query.ExecuteNextAsync<ConsumerDetails>());
}

return results.FirstOrDefault();

In both cases it is SQL that you will execute against CosmosDB. However in the LINQ case, a LINQ translator will kick in to convert your expression to a SQL query.

Also in the SQL example, because you are pointing directly to the lowercase id (which is the CosmosDB id) which is also the partition key, CosmosDB will recognise that and limit the query from a cross partition one to a partition specific one, making it faster and cheaper. The .Where(f => f.Id == consumerId) (with uppercase Id), once it goes through the LINQ provider, will be translated to SELECT * FROM c where c.Id = consumerId unless if there is a JsonAttribute("id") decorating the Id property. This means that you need to provide the PartitionKey value in the FeedOptions for the query.

CosmosDB: Linq vs SqlQuerySpec Performance when querying CosmosDB

1 Answers