Query SQL API in CosmosDB using C#

Question

Query SQL API in CosmosDB using C# :

My requirement is to query jsonData from CosmosDB (SQL API). The Below code is doing that work but it is taking 5-6 seconds to execute and return the Data.

            using (var cosmosClient = new CosmosClient(cosmosAccountEndpoint))
            {
                
                var sqlQueryText = "SELECT * FROM c where c.id = //our id";

                QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);

                FeedIterator<dynamic> queryResultSetIterator = cosmosClient.GetDatabase(databaseName).GetContainer(containerName).GetItemQueryIterator<dynamic>(queryDefinition);
                
                FeedResponse<dynamic> currentResultSet = await queryResultSetIterator.ReadNextAsync(); //this Line itself is taking 4-5 seconds time

                result = currentResultSet.Resource.ToList().FirstOrDefault(); //this line taking out the required response.
            }
            
            
            

As an alternative, I'm trying out the below (its not working). In below code, CollectionUri is not getting loaded properly and thrwoing System.InvalidOperationException.

                using (var client = new DocumentClient("uri", "key", new ConnectionPolicy { ConnectionMode = Microsoft.Azure.Documents.Client.ConnectionMode.Gateway, ConnectionProtocol = Protocol.Tcp, RetryOptions = retryOptions }))
                {
                    var sqlQueryText = "SELECT * FROM c where c.id = //our id";
                    Uri collectionUri = Microsoft.Azure.Documents.Client.UriFactory.CreateDocumentCollectionUri(databaseName, containerName);
                    CustomersInfo = client.CreateDocumentQuery<dynamic>(collectionUri, new SqlQuerySpec() {QueryText = sqlQueryText,Parameters = new SqlParameterCollection() { } }, DefaultOptions).AsDocumentQuery();
                };
                
                

Is there any alternative to the first code OR any changes to be made in second code to work.......

Thanks in Advance....

Answer 1

Are you querying for a single item?

The reason why it's so slow is because this is a cross partition query.

Rewrite this as a point read, ie. ReadItemAsync() and pass in the partition key value and this will execute much faster.

Also, if this is a new application I recommend using the v3 .NET SDK rather than the v2 SDK. This is very old and is not getting any new enhancements.