very very very slow find query in mongo using java driver

[hitesh kumar]

Hi I am using Win XP as operating system with 3 GB ram ,
I am fetching data from a collection having around 86000 documents.

this is my query

DBObject querynew = null;

Mongo m = new Mongo("localhost",27017);
DB db = m.getDB("employee_testing");
DBCollection coll = db.getCollection("employee");
str = "Science" + ".*?$";
DBObject A =
QueryBuilder.start("first_name").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject B =
QueryBuilder.start("last_name").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject C =
QueryBuilder.start("login_id").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject D =
QueryBuilder.start("sex").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject E =
QueryBuilder.start("contact_no").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject F =
QueryBuilder.start("date_of_birth").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject G =
QueryBuilder.start("city").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject H =
QueryBuilder.start("state").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject I =
QueryBuilder.start("country").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject J =
QueryBuilder.start("post_graduation").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject K =
QueryBuilder.start("post_stream").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject L =
QueryBuilder.start("post_graduation_campus").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject M =
QueryBuilder.start("post_graduation_marks").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject N =
QueryBuilder.start("graduation").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject O =
QueryBuilder.start("g_stream").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject P =
QueryBuilder.start("graduation_campus").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject Q =
QueryBuilder.start("other_graduation_campus").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject R =
QueryBuilder.start("graduation_marks").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject S =
QueryBuilder.start("town").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject T =
QueryBuilder.start("certification").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject U =
QueryBuilder.start("salary_range").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject V =
QueryBuilder.start("ext2").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject W =
QueryBuilder.start("ext3").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject X =
QueryBuilder.start("type").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject Y =
QueryBuilder.start("checked").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject Z =
QueryBuilder.start("last_exam_score").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject A1 =
QueryBuilder.start("exam_date").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject B1 =
QueryBuilder.start("password").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject C1 =
QueryBuilder.start("intermediate_marks").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject D1 =
QueryBuilder.start("matric_marks").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
DBObject E1 =
QueryBuilder.start("university").is(Pattern.compile(str,
Pattern.CASE_INSENSITIVE)).get();
querynew =
QueryBuilder.start().or(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,A1,B1,C1,D1,E1).get();
BasicDBObject querymongo = (BasicDBObject)querynew;
DBCurcor = coll.find(querymongo).sort(new
BasicDBObject("first_name",-1)).limit(10);


it takes around 10 to 15 mins or sometimes even more to fetch only 10
document from 86000 documents, where as same query in MYSQL takes only
some mili-secound to perform the same task;

Why it is happening..
Need help in making it lightning fast as it is the main feature of
mongodb.
I am having mongodb on my local system, so no sharding and all, I have
just install the mongodb and after that i just go to command prompt
and run the server by following command

c:\monodb\bin>mongod

so what to do to make query result fast.

[hitesh kumar]

This is the time it takes to fetch the documents
reslen:48 7531ms
reslen:48 7390ms
reslen:48 6828ms
reslen:48 7187ms
reslen:48 6531ms
reslen:48 7234ms

[Raxit Sheth]

What is explain looks like ?

--
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.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

[hitesh kumar]

here is the explain

expain through java--->
{ "cursor" : "BasicCursor" , "nscanned" : 86672 , "nscannedObjects" :
86672 , "n" : 10 , "scanAndOrder" : true , "millis" : 9859 ,
"nYields" : 0 , "nChunkSkips" : 0 , "isMultiKey" : false ,
"indexOnly" : false , "indexBounds" : { } , "allPlans" :
[ { "cursor" : "BasicCursor" , "indexBounds" : { }}] , "oldPlan" :
{ "cursor" : "BasicCursor" , "indexBounds" : { }}}

[hitesh kumar]

can you tell me how to implement index with this query

[hitesh kumar]

This is explain for all the records this query fetch

expain--->{ "cursor" : "BasicCursor" , "nscanned" : 86672 ,

"nscannedObjects" : 86672 , "n" : 10 , "scanAndOrder" : true ,

"millis" : 11859 , "nYields" : 0 , "nChunkSkips" : 0 , "isMultiKey" :

false , "indexOnly" : false , "indexBounds" : { } , "allPlans" :
[ { "cursor" : "BasicCursor" , "indexBounds" : { }}] , "oldPlan" :
{ "cursor" : "BasicCursor" , "indexBounds" : { }}}

expain--->{ "cursor" : "BasicCursor" , "nscanned" : 86672 ,

"nscannedObjects" : 86672 , "n" : 10 , "scanAndOrder" : true ,

"millis" : 12906 , "nYields" : 0 , "nChunkSkips" : 0 , "isMultiKey" :

false , "indexOnly" : false , "indexBounds" : { } , "allPlans" :
[ { "cursor" : "BasicCursor" , "indexBounds" : { }}] , "oldPlan" :
{ "cursor" : "BasicCursor" , "indexBounds" : { }}}

expain--->{ "cursor" : "BasicCursor" , "nscanned" : 86672 ,

"nscannedObjects" : 86672 , "n" : 10 , "scanAndOrder" : true ,

"millis" : 9593 , "nYields" : 0 , "nChunkSkips" : 0 , "isMultiKey" :

false , "indexOnly" : false , "indexBounds" : { } , "allPlans" :
[ { "cursor" : "BasicCursor" , "indexBounds" : { }}] , "oldPlan" :
{ "cursor" : "BasicCursor" , "indexBounds" : { }}}

expain--->{ "cursor" : "BasicCursor" , "nscanned" : 86672 ,

"nscannedObjects" : 86672 , "n" : 10 , "scanAndOrder" : true ,

"millis" : 11281 , "nYields" : 0 , "nChunkSkips" : 0 , "isMultiKey" :

false , "indexOnly" : false , "indexBounds" : { } , "allPlans" :
[ { "cursor" : "BasicCursor" , "indexBounds" : { }}] , "oldPlan" :
{ "cursor" : "BasicCursor" , "indexBounds" : { }}}

expain--->{ "cursor" : "BasicCursor" , "nscanned" : 86672 ,

"nscannedObjects" : 86672 , "n" : 10 , "scanAndOrder" : true ,

"millis" : 9015 , "nYields" : 0 , "nChunkSkips" : 0 , "isMultiKey" :

false , "indexOnly" : false , "indexBounds" : { } , "allPlans" :
[ { "cursor" : "BasicCursor" , "indexBounds" : { }}] , "oldPlan" :
{ "cursor" : "BasicCursor" , "indexBounds" : { }}}

expain--->{ "cursor" : "BasicCursor" , "nscanned" : 86672 ,

"nscannedObjects" : 86672 , "n" : 10 , "scanAndOrder" : true ,

"millis" : 7968 , "nYields" : 0 , "nChunkSkips" : 0 , "isMultiKey" :

false , "indexOnly" : false , "indexBounds" : { } , "allPlans" :
[ { "cursor" : "BasicCursor" , "indexBounds" : { }}] , "oldPlan" :
{ "cursor" : "BasicCursor" , "indexBounds" : { }}}

expain--->{ "cursor" : "BasicCursor" , "nscanned" : 86672 ,

"nscannedObjects" : 86672 , "n" : 10 , "scanAndOrder" : true ,

"millis" : 7968 , "nYields" : 0 , "nChunkSkips" : 0 , "isMultiKey" :

false , "indexOnly" : false , "indexBounds" : { } , "allPlans" :
[ { "cursor" : "BasicCursor" , "indexBounds" : { }}] , "oldPlan" :
{ "cursor" : "BasicCursor" , "indexBounds" : { }}}

expain--->{ "cursor" : "BasicCursor" , "nscanned" : 86672 ,

"nscannedObjects" : 86672 , "n" : 10 , "scanAndOrder" : true ,

"millis" : 11562 , "nYields" : 0 , "nChunkSkips" : 0 , "isMultiKey" :

false , "indexOnly" : false , "indexBounds" : { } , "allPlans" :
[ { "cursor" : "BasicCursor" , "indexBounds" : { }}] , "oldPlan" :
{ "cursor" : "BasicCursor" , "indexBounds" : { }}}

expain--->{ "cursor" : "BasicCursor" , "nscanned" : 86672 ,

"nscannedObjects" : 86672 , "n" : 10 , "scanAndOrder" : true ,

"millis" : 17796 , "nYields" : 0 , "nChunkSkips" : 0 , "isMultiKey" :

false , "indexOnly" : false , "indexBounds" : { } , "allPlans" :
[ { "cursor" : "BasicCursor" , "indexBounds" : { }}] , "oldPlan" :
{ "cursor" : "BasicCursor" , "indexBounds" : { }}}

expain--->{ "cursor" : "BasicCursor" , "nscanned" : 86672 ,

"nscannedObjects" : 86672 , "n" : 10 , "scanAndOrder" : true ,

"millis" : 8203 , "nYields" : 0 , "nChunkSkips" : 0 , "isMultiKey" :

[Raxit Sheth]

1. Find out what all fields are there in collections
2. You are trying search on only one field or multiple fields,  if search is based on multiple fields, what is the order of the fields got queried
3. What all fields you are using for sorting
4. http://www.mongodb.org/display/DOCS/Indexes  Indexing is straight forward
5. Add index on the fields (order is imp) of search + sort
6. Re-run your query

Best way is to use the same query from mongo shell. (it may take minute to convert your java query to javascript) (or if  verbose mode is on, you can copy paste javascript query from mongod log)


Raxit Sheth

[hitesh kumar]

where can i found mondod logs file and second can I have multiple
Index in a collection
That is what if I put Index on all the BSON Field in a Document so
called column in RDBMS

[Raxit Sheth]

1. you can have multiple single index on each field.
2. you can have multiple composite index on combination of fields
3. best is try to convert (without logs) to javascript
4. alternatively, you can run mongod in verbose mode, it print out logs on standard output (or you can redirect logs to --logpath option)


Raxit

[hitesh kumar]

Thanks for you help Raxit Sheth
So, what I do is , I create the index on all the BSON fields which I
require while searching,
Them I will post here, if index makes any difference

[jm]

You seem to have a huge amount of regular expressions
(Patter.compile(...)) on a single query. I suspect that is the reason
for slowness. Straight string matching is much faster and also I don't
think Mongo can use indexes when querying with regexp.

I can not give straight answers on how to solve it without regexp.
Except that, if possible try reconsider your schema so that you can
query without regexp. Maybe have some helper fields you can filter
with. Case insensitive searches could be done by converting both the
stored values and the search string to lowercase, etc..

Also I think Mongo can only use one index per query (which can, and in
this case probably should be a compound index, however).

[hitesh kumar]

ok , Jim can you please provide me a code how to write this "select *
from TableA where name like("%SOME STRING%")" in java using mongodb
java driver

i want to perform like in java on my mongodb collection..

[Barrie]

Hi Hitesh,

You can use Pattern.compile("some string") to create the regular expression.

Hope this helps.

Barrie 

[hitesh kumar]

Hi Barrie,

Thanks for your reply, I am already using it, I want some
code, where i do not have to use pattern.compile method, as when i
used it with multiple queries, it executes first then , it is passed
to the query and then the query gets executed, so it makes result very
slow, i have also write the code of my query above in my first post of
the discussion..

Thanks again,

Hope you come up with a different answer..

> --
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.

> To view this discussion on the web visit
> https://groups.google.com/d/msg/mongodb-user/-/pkiIYIOK1pAJ.

[Barrie]

Hi Hitesh,

The only way to do what you're looking for is with regular expressions, and in Java the way to do this is with Pattern.match(...)

Barrie 

> mongodb-user+unsubscribe@googlegroups.com.

[hitesh kumar]

Thanks Barriee... but i have found some different way finaly to perform the same task..
So, my queries are bit faster now, but still they are not as quick as it should be

To view this discussion on the web visit https://groups.google.com/d/msg/mongodb-user/-/unVd42WArmEJ.

To post to this group, send email to mongod...@googlegroups.com.

To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.