distinct slow with query

[GenericUser]

Hi,

Trying to get distinct of a field from ~15 million documents.

  var command = new CommandDocument

                    {

                        {"distinct", collection},                       

                        {"key", column} ,

                         {"query", BsonValue.Create(query)}         

                    };

                    var rseponse= mongo.RunCommand(command).Response

I have created sparse index on the column. This query takes 15 secs to respond. can anyone help improve the performance.

SInce this is shard enviroment, I do not get stats as part of the response.

Thanks in advance.

[Bernie Hackett]

If I were to guess, you don't have an index (or at least not a good one) for the query.

--
--
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
 
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[GenericUser]

Thanks for your response.

I have sparse index on the column, as the column doesnt exist in all documents. If this is not a good index, can you please suggest what index should I create?

Thanks.

[Asya Kamsky]

One way to verify what's going on is to connect directly to the shards and run the command to see the stats output.

It will show how the index is used (since the index exists on all the shards that have this collection).

What's the query that you are passing in?

[GenericUser]

When I run the query directly on shards, it returns pretty quickly. It uses the index on the query.

The query is just one of the filters of the columns in the document. It is column other than the column on which I want to perform distinct on.

E.g.,

 var command = new CommandDocument

                    {

                        {"distinct", "myCollection"},                       

                        {"key", "distinctColumn"} ,

                         {"query", {"col1","val1"}         

                    };

The stats when I run this directly on shard:

"stats" : {

        "n" : 292172,

        "nscanned" : 292172,

        "nscannedObjects" : 292172,

        "timems" : 2910,

        "cursor" : "BtreeCursor col1_index"

},

Any thoughts why it would be slow when run using mongos?

[Asya Kamsky]

That's not very quickly - I would call 2.9 seconds extraordinarily slow!  

That's because it has to look at every document to see how many unique values "distinctColumn" has...

[GenericUser]

I said quickly as compared to 13 secs :(

Is there any way to improve the performance??

[Asya Kamsky]

How many distinct values are there (on each shard and together on the entire collection)?

Asya