Why is SQL Server 2012 faster than MongoDB for this query
c#, mongodb, sql-server, sql-server-2012
Solution
MongoDB is likely five times slower in this case because it needs to read and output five times as much data.
One row in your SQL table is just 4 byte. But in MongoDB it is a 4 byte integer plus a 12 byte ObjectId. Also, the field-names are part of each individual document. The strings `_id` and `id1` are part of each document you retrieve, so some additional bytes are added.
And this is just the actual payload. When you look at BSONSpec.org you can see that your document is represented by:
- 4 byte for a 32bit integer which is the total length of the document
- 1 byte `0x01` for "now comes an ObjectId"
- 4 bytes for the null-terminated string `_id`
- 12 bytes for the ObjectId
- 1 byte `0x10` for "now comes a 32bit integer" (smallest integer there is)
- 4 bytes for the null-terminated string `id1`
- 4 bytes for the 32bit integer
- 1 trailing `0x00` which marks the end of the document
which is a total of 31 byte.
Problem
I am doing some performance tests between SQL Server 2012 and MongoDB 2.4.9. I have done a little research online and found many comparisons that compare only insert performance between these two databases. I am only interested in `select`performance without indexes. I setup a very simple test. Here are the specs SQL Server setup - Setup SQL Server with only one integer field. - Insert 1 million random integers MongoDB setup - Has two fields _id and id1 - Insert 1 million random integers into id1 field When I inserted the numbers they are the exact same numbers going into both databases. The random numbers range from 1 to 1000. SQL Server query ``` select id from a101 where id > 995 ``` MongoDB query ``` var q = Query.GT("id1", 995); foreach (var i in collection.Find(q)) { values.Add(i.id1); } ``` Results: - SQL Server: 192 ms - MongoDB: 1109 ms For my schema I will not need any joins and will have only denormalized data - this is why I considered MongoDB. I expected mongoDB to be many times faster than SQL Server after reading the benchmarks online. Is there something I may be doing wrong? Again, I wanted to test without indexes.