Having poor performance when getting data from cosmos db by code

Author: Itani

Originally Sourced from: https://stackoverflow.com/questions/74371755/having-poor-performance-when-getting-data-from-cosmos-db-by-code

We have aroung 140K documents in a collection, and a typical search returns around 6 MB of data. This process is taking around 30 to 40 seconds, and I would like to know why, and how can I improve it?

Sample data to see how the structure looks like: https://pastebin.com/0eJJfVKB

_id is the primarmy key, and there's a compound index on HotelID, GiataCityID, and CountryCode.

This is how I'm getting my data from cosmos by code:

The first part is to only get these data , as I figured out why return everything, just get what I need, that should speed things up.

Would appreciate any input, and ready to provide more details.

var hoteldetails = new List<HotelDetails>();
var projection = Builders<HotelDetails>.Projection.Expression(
        t => new HotelDetails
        {
            Id = t.Id,
            PropertyName = t.PropertyName,
            GiataCityId = t.GiataCityId,
            City = t.City,
            CityId = t.CityId,
            SupplierList = t.SupplierList,
            CountryCode = t.CountryCode,
            CountryName = t.CountryName,
            longitude = t.longitude,
            latitude = t.latitude,
            Images = t.Images,
            HotelId = t.HotelId,
            SupplierRoomList = t.SupplierRoomList
        });

this.GetDBClient();
var database = this.client.GetDatabase(this.settings.DataBase);
var collection = database.GetCollection<HotelDetails> (MongoCollections.HotelDetails.ToString());
if (HotelIDs != null && HotelIDs.Count > 0)
{
     hoteldetails = await collection
     .Aggregate()
     .Match(x => x.CountryCode == CountryCode && (x.CityId == CityId || x.GiataCityId ==CityId.ToString()) 
         &&  x.SupplierList.Any(y => HotelIDs.Contains(y.HotelId) && y.SupplierName == SupplierNameType))
     .Project(projection)
     .Limit(Limit)
     .ToListAsync();
}

The RU data page

RUs

I tried to use projecting to only get the properties i need, that sped things up a bit but not by a whole lot.

I tried to play with the filter, but really to no effect.