0

I have the following query - it takes about 20-40 seconds to complete (similar queries without RegEx on the same collection take milliseconds at most):

var filter = Builders<BsonDocument>.Filter.Regex("DescriptionLC", new BsonRegularExpression(descriptionStringToFindFromCallHere, "i"));

var mongoStuff = GetMongoCollection<BsonDocument>(MongoConstants.StuffCollection);

var stuff = await mongoStuff
    .Find(filter)
    .Limit(50)
    .Project(x => Mapper.Map<BsonDocument, StuffViewModel>(x))
    .ToListAsync();

I saw an answer here that seems to imply that this query would be faster using the following format (copied verbatim): 

var names = namesCollection.AsQueryable().Where(name =>
    name.FirstName.ToLower().Contains("hamster"));

However, the project is using MongoDb .NET Driver 2.0 and it doesn't support LINQ. So, my question comes down to:

a). Would using LINQ be noticeably faster, or about the same? I can update to 1, but I would rather not.
b). Is there anything I can do to speed this up? I am already looking for a lower-case only field.

------------END ORIGINAL OF POST------------

Edit: Reducing the number of "stuff" returned via changing .Limit(50) to say .Limit(5) reduces the call time linearly. 40 seconds drops to 4 with the latter, I have experimented with different numbers and it seems to be a direct correlation. That's strange to me, but I don't really understand how this works.

Edit 2: It seems that the only solution might be to use "starts with" instead of "contains" regular expressions. Apparently the latter doesn't use indices efficiently according to the docs ("Index Use" section).

Edit 3: In the end, I did three things (field was already indexed):

1). Reduce the number of results returned - this help dramatically, linear correlation between number of items returned and amount of time the call takes.
2). Changed the search to lower-case only - this helped only slightly.
3). Changed the regular expression to only search "starts with" rather than "contains", again, this barely helped, changes for that were:

//Take the stringToSearch and make it into a "starts with" RegEx
var startingWithSearchRegEx = "^" + stringToSearch;

Then pass that into the new BsonRegularExpression instead of just the search string.

Still looking for any feedback!!

Community
  • 1
  • 1
VSO
  • 11,546
  • 25
  • 99
  • 187

1 Answers1

0

Regex on hundreds of thousands documents is not recommend as it's essentially doing document scan so no index is being used at all.

This is the main reason why your query is so slow. It has nothing to do with .net driver.

If you have a lot of text or searching for text patterns often, I'll suggest create text index on field of interest and do full text search. Please see docs for $text

Saleem
  • 8,728
  • 2
  • 20
  • 34