Testing mongo with C# and it seems very slow on find compared with SQL server

[billy...@gmail.com]

Mongo 2.45 64bit

C# driver 1.8.2

Windows 7

Intel CPU, 12G RAM, SSD

Hi,

I'm looking into mongo to replace SQLServer. First step was to re-create the database in mongo, which contains about 2m documents.

Finding a subset of about 100k these in the shell (the field is indexed, and find.explain indicates it is being used) shows millis of 98.

Cycling through SQL in C# over the same record set (which requires a join) takes about 1,000ms (which is OK).

Doing the same in mongo takes about 10,000ms.  Even using a reduced database with just the 100k records takes 2,000ms.

I assume I'm doing something dumb in my setup, and even though it may not be as quick as SQL for a particular query, 10x seems wrong.

BTW, I have defined the cursor type with a class (I hope my terminology is correct), defined the query and used find followed by looping with foreach.

How do I diagnose the issue?  

Rgds

Bill

[billy...@gmail.com]

Sorry, should add

.Net 4.0

Running in visual studio web 2012

[craiggwilson]

Hi Billy... MongoDB's speed depends a lot on your schema design and your use of indexes.  Could you post the query you are running as well as what your documents look like?

[billy...@gmail.com]

Hi Craig.  Pardon the obfuscation of field codes below, but here are the C# class definitions

    public class PEntity

    {

        public int pField1 { get; set; }

        public int pField2 { get; set; }

        public int pField3 { get; set; }

        public int pField4 { get; set; }

        public int pField5 { get; set; }

        public int pField6 { get; set; }

        public double pField7 { get; set; }

        public bool pField8 { get; set; }

        public int pField9 { get; set; }

        public DateTime pField10 { get; set; }

        public int pField11 { get; set; }

        public double pField12 { get; set; }

        public DateTime pField13 { get; set; }

        public int pField14 { get; set; }

    }

    public class PartEntity

    {

        public int partField1 { get; set; }

        public DateTime partField2 { get; set; }

        public double partField3 { get; set; }

        public double partField4 { get; set; }

        public double partField5 { get; set; }

        public double partField6 { get; set; }

        public double partField7 { get; set; }

        public double partField8 { get; set; }

        public double partField9 { get; set; }

        public double partField10 { get; set; }

        public double partField11 { get; set; }

        public int partField12 { get; set; }

        public int partField13 { get; set; }

        public int partField14 { get; set; }

        public int partField15 { get; set; }

        public int partField16 { get; set; }

        public int partField17 { get; set; }

        public int partField18 { get; set; }

        public DateTime partField19 { get; set; }

        public DateTime partField20 { get; set; }

        public DateTime partField21 { get; set; }

        public DateTime partField22 { get; set; }

        public DateTime partField23 { get; set; }

        public DateTime partField24 { get; set; }

    }

    public class Entity

    {

        public ObjectId Id { get; set; }

        public DateTime Field1 { get; set; }

        public int Lookup_ID { get; set; }

        public long Field2 { get; set; }

        public long Field3 { get; set; }

        public double Field4 { get; set; }

        public double Field5 { get; set; }

        public double Field6 { get; set; }

        public double Field7 { get; set; }

        public double Field8 { get; set; }

        public int Field9 { get; set; }

        public int Field10 { get; set; }

        public double Field11 { get; set; }

        public int Field12 { get; set; }

        public double Field13 { get; set; }

        public int Field14 { get; set; }

        public bool Field15 { get; set; }

        public double Field16 { get; set; }

        public double Field17 { get; set; }

        public bool Field18 { get; set; }

        public double Field19 { get; set; }

        public int Field20 { get; set; }

        public int Field21 { get; set; }

        public double Field22 { get; set; }

        public bool Field23 { get; set; }

        public bool Field24 { get; set; }

        public int Field25 { get; set; }

        public int Field26 { get; set; }

        public double Field27 { get; set; }

        public double Field29 { get; set; }

        public double Field30 { get; set; }

        public List<PartEntity> parts { get; set; }

        public List<PEntity> ps { get; set; };

  }

Note that I also tried arrays in place of the lists and have tried without the lists altogether.

The is a simple index on "Lookup_ID" that I created in the shell using omething like db.ensureIndex ( { "Lookup_ID": 1 } ) - I did this a few days ago, so I assume this is what I did - it's indexed for sure.

The query is:

var query = Query<Entity>.EQ(e => e.Lookup_ID, iLookupId); - iLookupId is an integer

then:

var Entities = collection.Find(query);

followed by looping with foreach over Entities.  Note I also tries ToArray and then looped over the array, but sililar timing.

Hope that is enough.

Rgds

Bill

[billy...@gmail.com]

Also updated to 2.4.6 release. Minor improvement (10%), but may have been chance

[craiggwilson]

Nothing seems amiss in your code. You said you have run explain.  Could you do 2 things for me...  

1) Paste the output from the shell of db.collection.find({Lookup_Id: some_value}).explain()

2) run db.setProfilingLevel(2) in the shell.  This will log all queries to the mongodb log.  Then, run your query from your test application and paste the log entries for it here.

[billy...@gmail.com]

Thanks Craig.  I won't be in the office until Monday, so I'll post then.

Rgds

[billy...@gmail.com]

Response to 1)

MongoDB shell version: 2.4.6

connecting to: test2

{

"cursor" : "BtreeCursor Lookup_ID_1",

"isMultiKey" : false,

"n" : 81490,

"nscannedObjects" : 81490,

"nscanned" : 81490,

"nscannedObjectsAllPlans" : 81490,

"nscannedAllPlans" : 81490,

"scanAndOrder" : false,

"indexOnly" : false,

"nYields" : 0,

"nChunkSkips" : 0,

"millis" : 73,

"indexBounds" : {

"Lookup_ID" : [

[

612311,

612311

]

]

},

"server" : "Bill-PC:27017"

}

Response to 2)

Mon Aug 26 09:53:52.347 [conn25] query test2.collection query: { $query: { Lookup_ID: 612311 }, $hint: "Lookup_ID_1" } cursorid:14894175378499620 ntoreturn:0 ntoskip:0 nscanned:102 keyUpdates:0 locks(micros) r:4544 nreturned:101 reslen:163137 4ms

Mon Aug 26 09:53:52.360 [conn25] getmore test2.collection query: { $query: { Lookup_ID: 612311 }, $hint: "Lookup_ID_1" } cursorid:14894175378499620 ntoreturn:0 keyUpdates:0 locks(micros) r:4875 nreturned:2359 reslen:4195073 4ms

Plus another 35 lines almost the same as the above

Total time returned via stopwatch in C# as a result of looping through the cursor, but not doing anything with the resultant entries: 8000+ ms.

Rgds

Bill

[craiggwilson]

Thanks Billy.  Everything looks perfectly fine.  I haven't asked yet about the comparable code to read the records from your SQL database.  Perhaps that could shed some light on the difference in so far as how your application is processing the data.  Could you perhaps share your code, or some resemblance of it for what you are doing in your SQL code?

[billy...@gmail.com]

Here's the basic code I use for mongo:

            var connectionString = "mongodb://localhost";

            var client = new MongoClient(connectionString);

            var server = client.GetServer();

            var database = server.GetDatabase("test2");

            var collection = database.GetCollection<LoanEntity>("loans");

            try

            {

                iLookupId = Convert.ToInt32(lookupId);

                var query = Query<Entity>.EQ(e => e.Lookup_ID, iLookupId);

                stopwatch.Reset();

                stopwatch.Start();

              var entities = collection.Find(query).SetHint("Lookup_ID_1"); ;

                foreach (Entity entity in entities)

                    {

Note - for time testing, all code within this loop is commented out

}   //foreach (Entity entity in entities)

                stopwatch.Stop();

                int q = 0;

} //try

Note - I did try without the try/catch exception handling, but this didn't seem to make a difference.

In the SQL version, it' a pretty simple query that returns about 20 fields from two tables that are joined on three of them (one is a date, one is a long integer individual ID and the other is the integer lookupId, returned in order of the same 3 fields used in the join.  I'm not actually using a true join, it's of the "where table1.ID = table2.ID, etc" variety.

[craiggwilson]

So, is your SQL version just looping through the results or are you reading all the fields and creating your entities?  The MongoDB version is definitely deserializing all the data, creating the entities, etc...  

[billy...@gmail.com]

The SQL works a little differently - I use and SQLAdapter, then fill a datatable using sqlAdapter.Fill(datatable).  This executes the query, unlike mongo's find() which just creates a cursor.  I then loop using "foreach (DataRow row in datatable.Rows)" .  Using stopwatch from before the fill to the end of the loop is around 10x faster in SQL.

Not exactly sure what you mean in "The MongoDB version is definitely deserializing all the data, creating the entities, etc..", but if you mean does the mongo version actually work as expected, then the answer is it appears to give the correct results when I have code in the loop (which as I said previously I have commented out for this exercise).

Rgds

Bill

[craiggwilson]

Sorry, couldn't login to google groups for a while. Weird.

So, the MongoDB driver is creating your entities and deserializing data off the stream and putting it into your Entity classes.  This is a bit more overhead than what your SQL Data reader is doing as you are using a relatively low-level ADO.NET concept.  

A more comparable approach would be to either use EntityFramework/NHibernate instead of ADO.NET or use BsonDocument instead of Entities.  Really, none of these are going to be apples to apples, but they may be a bit closer.  Your bottleneck appears to be at the driver level, not the server side.  We work really hard to keep serialization performance low, but we can always do better.

[billy...@gmail.com]

Thanks Craig,

I had figured it was probably a driver issue.  I'll have a go at BsonDocument solution - any suggested reading other than the C# driver tutorial?

Rgds
Bill