LINQ provider ThenBy performance

[object]

I have a collection with two separate indexes: Name (string) and Created (date). The collection is large, it contains several hundred thousands items. Each combination of Name and Created is unique, i.e. there are not items having the same Name and Created.

I execute the following queries on the collection:

1. collection.AsQueryable<Item>().OrderBy(x => x.Created).Take(1);

2. collection.AsQueryable<Item>().OrderBy(x => x.Created).ThenBy(x => x.Name).Take(1);

First query is executed instantly, but it takes several minutes to return the result from the second query, although it's the same single item. It looks like the evaluation of the second query is done in two phases, as in-memory sort of the results ordered by Created. If so, it makes such nested sort operations almost unusable in case of large databases. This is probably not a best practice of using MongoDB, but I am just trying to understand performance implications of such queries.

Vagif

[craiggwilson]

What indexes have you created for this collection?

[object]

Each of these fields (Name, Created) is indexed with its own single field index.

[craiggwilson]

So, mongodb only uses a single index per query.  In the case of your first where you sorted on just Created, it chose the Created index and was easily able to grab the first one.  In the case of your second query, it most likely chose the Created index.  After it had the Created index, it still had to sort all the results from the Created index in memory by their Name.

To fix this, you should create an compound index on Created and Name (order is important).  By doing that, both your queries can get answered by a single index.  (However, if you do a query on just Name, then the compound index would not get used.  You'd need to create a new one that starts with Name.)

[Vagif Abilov]

Craig,

Thank you for the clarification. Of course having a compound index would fix the issue, this was just an ad-hoc query. But I am still puzzled why it took so long time having in mind that for each Created value there is only one Name value. After Mongo built a list of results sorted by Created it would in fact have it also sorted by Name (after Created), so an attempt to apply the inner sorting would end up with the same result set - no need to move any data. However the inner sorting looks like a massive in-memory reordering, at least judging by time it takes. This is what puzzles me.

Vagif

Sent from my iPad

--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com
To unsubscribe from this group, send email to
mongodb-user...@googlegroups.com
See also the IRC channel -- freenode.net#mongodb

[craiggwilson]

Since the index didn't contain the data it needed (the Name), it had to load every document from disc to get it's Name value and then it had to sort them all to find the first one in the list.

mongodb-user+unsubscribe@googlegroups.com

[Vagif Abilov]

I see. Good to know when planning queries.

Thanks again!

Sent from my iPad

mongodb-user...@googlegroups.com

[Vagif Abilov]

Yes, I got the same performance when trying from Mongo console:

db.mycollection.find({}).sort({Created:1}).limit(1) works fast,

db.mycollection.find({}).sort({Created:1, Name:1}).limit(1) works extremely slow.

So this has nothing to do with LINQ provider, it's just the way Mongo works. Different from traditional SQL databases, so it's good to know for thosed used to SQL Server.

Vagif