Count is terribly slow!

[Dustin Blair]

Using C# driver 1.5 with MongoDB v 2.2

~12 million rows of data in Mongo

the mongo query:

        IMongoQuery query = new QueryDocument();

        query = Query.GTE("InsertUTC", msgReceivedDate);

        query = Query.And(query, Query.NE("Header.AssetSerialNumber", BsonNull.Value));

        query = Query.And(query, Query.EQ("Header.Dealer", (int)DealerNetworkEnum.SomeDealer));

When trying to get a simple COUNT using this query:

var result = MongoCollection.Count(query);

return (int)result;

it takes almost 3 minutes!

by itself, the actual query returns data instantly!

        var result = _mongoStorage.MongoCollection.Find(GetMongoQuery(requestDateTime)).AsQueryable()

          .Skip((page - 1) * pageSize).Take(pageSize)

          .Select(t => new DeviceMessage { Header = t.Header, Message = t.DevicePacket })

          .OrderBy(f => f.Header.InsertUTC).ToList();

What black magic is needed to return a count quickly??

THANK YOU!

Dustin

[craiggwilson]

Can you do an explain on your query?  (instead of running it).  I'd image that you are missing an index or your query is not hitting the index you think it is.

Second of all: Your Find(...).AsQueryable() isn't doing what you think it is.  Those skips, selects, and orderbys are not getting run on the server, but rather client side.  It feels immediate because the server immediately start returning values and then your client side starts processing them.  I'm surprised you are seeing results so fast because to perform the OrderBy(...) it has to load all 12 million documents into memory.

Also, as an FYI, doing paging the way you are doing it is not recommended for large data sets.  Skip will run the query and the move the specified number of records (the skip count) and then start returning results.  It is much better to keep track of the last document's unique sort key and then send in a $gt query with it.  It enables the query engine to jump immediately to the proper record.