![[RSS]](http://tweakimg.net/g/if/v2/icons/rss_small.png){kind=icon}
![[Quicklink]](http://tweakimg.net/g/if/icons/world_link_cropped.png){kind=icon}
MongoDB vs SQLServer
I've been doing some speed comparisons between MongoDB and SQLServer (2008 R2), and it led to some spectacular differences.
Insert and Select
I'm reading 12.000 rows of data from Excel, and then create object from them. Every new object is compared to all objects in the database already, and if one is present, some data is added, else the entire object is saved.
For SQL server I use NHibernate, for MongoDB the standard C# driver.
First, MongoDB, no indexes:
115120 ms ( almost 2 minutes)
Than, MongoDB with a compound key on the lookup values:
76330 ms ( just over 1 minute)
Than SQL server 2008 R2, no indexes:
3308968 ms ( 55 minutes )
SQL server with index did worse:
3612287 ms ( 60 minutes )
So in this case, MongoDB is a factor 30 to 60 faster than SQL Server.
Note: In other cases, SQL Server may be faster, especially when using bulk-inserts.
Selects
Here you really need indexes, doing some querying on 200.000 documents in MongoDB/100.000 rows in SQLServer:
MongoDB no index: 103 ms
MongoDB with index: 1 ms
HiB/SQL no index: 51 ms
HiB/SQL with index: 2 ms
So If you don't need the ACID support or advanced features from regular DB's, a MongoDB will handle large amounts of data very well and can be spectacular fast.
Insert and Select
I'm reading 12.000 rows of data from Excel, and then create object from them. Every new object is compared to all objects in the database already, and if one is present, some data is added, else the entire object is saved.
For SQL server I use NHibernate, for MongoDB the standard C# driver.
First, MongoDB, no indexes:
115120 ms ( almost 2 minutes)
Than, MongoDB with a compound key on the lookup values:
76330 ms ( just over 1 minute)
Than SQL server 2008 R2, no indexes:
3308968 ms ( 55 minutes )
SQL server with index did worse:
3612287 ms ( 60 minutes )
So in this case, MongoDB is a factor 30 to 60 faster than SQL Server.
Note: In other cases, SQL Server may be faster, especially when using bulk-inserts.
Selects
Here you really need indexes, doing some querying on 200.000 documents in MongoDB/100.000 rows in SQLServer:
MongoDB no index: 103 ms
MongoDB with index: 1 ms
HiB/SQL no index: 51 ms
HiB/SQL with index: 2 ms
So If you don't need the ACID support or advanced features from regular DB's, a MongoDB will handle large amounts of data very well and can be spectacular fast.
16-02 Chateau Haddock![Volgende: [urbex] Heavy Metal](http://tweakimg.net/g/bullets/arrow_down.gif){kind=icon}
03-01 [urbex] Heavy Metal
Comments
By
RobIII,
Tuesday 15 January 2013 13:57
Did you use safe=true writeconcern=1 for a fair comparison? MongoDB doesn't guarantee updates are actually persisted which is why it performs so much better on inserts. You also didn't mention if you're using a single MongoDB instance or a replicated set.
This comparison is like comparing apples and oranges; MongoDB and SQL server are not comparable at this level. Also, NHibernate is not exactly known for it's speed. I would've used "plain ol' ADO.Net" or EF or Dapper which all (usually) outperform NHibernate.
This comparison is like comparing apples and oranges; MongoDB and SQL server are not comparable at this level. Also, NHibernate is not exactly known for it's speed. I would've used "plain ol' ADO.Net" or EF or Dapper which all (usually) outperform NHibernate.
[Comment edited on Tuesday 15 January 2013 14:16]
By
qless,
Tuesday 15 January 2013 14:01
Safe write is default (as is journaling) with current drivers and database version of Mongo. If I turn it off, speed increases even more 
And yes, both database aren't fully comparable, but for *this* case, mongo is by far faster and better.
And yes, both database aren't fully comparable, but for *this* case, mongo is by far faster and better.
[Comment edited on Tuesday 15 January 2013 14:02]
By
RobIII,
Tuesday 15 January 2013 14:08
I know but you didn't mention driver versions, nor the writeconcern setting which is important (and this default also has only recently changed, since 1.7 if I'm not mistaken).qless wrote on Tuesday 15 January 2013 @ 14:01:
Safe write is default (as is journaling) with current drivers and database version of Mongo.
[Comment edited on Tuesday 15 January 2013 14:14]
By
Supermario16,
Tuesday 15 January 2013 14:17
MongoDB is a nosql database, SQL Server is an old fashion RDBMS, therefor it cannot be compared to SQL Server. They're both totally different products, it's like comparing a Land Rover Discovery with a Ferarri Enzo on off road capabilities.
edit: typo.
edit: typo.
[Comment edited on Tuesday 15 January 2013 15:39]
By
Phyxion,
Tuesday 15 January 2013 15:28
This andSupermario16 wrote on Tuesday 15 January 2013 @ 14:17:
MongoDB is a nosql database, SQL Server is an old fashion RDBMS, therefor it cannot be compared to SQL Server. They're both totally different products, it's like comparing an Land Rover Discovery with a Ferarri Enzo on off road capabilities.
this. Pretty useless test.RobIII wrote on Tuesday 15 January 2013 @ 13:57:
Did you use safe=true writeconcern=1 for a fair comparison? MongoDB doesn't guarantee updates are actually persisted which is why it performs so much better on inserts. You also didn't mention if you're using a single MongoDB instance or a replicated set.
This comparison is like comparing apples and oranges; MongoDB and SQL server are not comparable at this level. Also, NHibernate is not exactly known for it's speed. I would've used "plain ol' ADO.Net" or EF or Dapper which all (usually) outperform NHibernate.
By
qless,
Tuesday 15 January 2013 16:28
True hibernate isn't a speed demon, but is sure is handy to use the same C# code for Mongo and SQL, and, if you're not insert 10000 rows, the difference isn't that noticable.
In this case, both sql via nhibernate as mongodb will be comparable. No joins or transactions needed, or any other advanced feature, just object store.
In this case, both sql via nhibernate as mongodb will be comparable. No joins or transactions needed, or any other advanced feature, just object store.
By
RobIII,
Tuesday 15 January 2013 17:36
Could you demonstrate/explain how you achieved this? I find it hard to believe you used the same code. You must have created some abstraction at least one of them, probably on both, which would mean you could as well create an abstraction on top of the alternatives (ADO.Net, EF, Dapper, llblgen, ...).qless wrote on Tuesday 15 January 2013 @ 16:28:
but is sure is handy to use the same C# code for Mongo and SQL
Also, especially when inserting many records (i.e. bulk), I would at least make sure that bulk-insert operations are used for SQL which, I'm assuming, you didn't do?
And beyond all of this it very much depends on the data itself, the use-case and many other factors. I'm sure for your use-case this might be a good (possibly the best) solution but the blog implies a pretty generic statement that MongoDB outperforms SQL server which is, which I hope we demonstrated, not always true (not even by a long shot).
[Comment edited on Tuesday 15 January 2013 20:58]
By
Deathraven,
Wednesday 16 January 2013 16:37
NHibernate isn't known for its bulk insert/update speed. You haven't even mentioned how you used NHibernate, Did you use the StatelessSesion? or the normal one? Using a StatelessSession makes a huge difference on bulk insert/update operations.qless wrote on Tuesday 15 January 2013 @ 16:28:
True hibernate isn't a speed demon, but is sure is handy to use the same C# code for Mongo and SQL, and, if you're not insert 10000 rows, the difference isn't that noticable.
In this case, both sql via nhibernate as mongodb will be comparable. No joins or transactions needed, or any other advanced feature, just object store.
But besides that, if you know that NHibernate "isn't a speed demon". And your looking to compare performance, then WHY did you use NHibernate? Its like you knowingly and willingly set yourself up to fail from the beginning?!
If your using the standard c# driver for mongoDB then why aren't you using standard SQLConnection for Sql Server?
By
GrooV,
Wednesday 16 January 2013 16:40
I think this is actually the worst DB benchmark that i've ever seen.
And I don't believe you can/should use Hibernate with MongoDB since Hibernate is for a RDMS like SQL Server or MySQL and MongoDB is a NoSQL DB
And I don't believe you can/should use Hibernate with MongoDB since Hibernate is for a RDMS like SQL Server or MySQL and MongoDB is a NoSQL DB
By
RobIII,
Wednesday 16 January 2013 17:53
I don't believe you can, let alone should...GrooV wrote on Wednesday 16 January 2013 @ 16:40:
And I don't believe you can/should use Hibernate with MongoDB
By
qless,
Wednesday 16 January 2013 18:13
The software uses either nhibernate or mongo, not both at the same time.GrooV wrote on Wednesday 16 January 2013 @ 16:40:
I think this is actually the worst DB benchmark that i've ever seen.
And I don't believe you can/should use Hibernate with MongoDB since Hibernate is for a RDMS like SQL Server or MySQL and MongoDB is a NoSQL DB
In this case, bulk inserts can't be used (every data insert has to be first looked up), and yes, if I would do a bulk-insert, I sure wouldn't use nhibernate...
As I said, this is a special case, not your everyday default data acesss application.
By
masterpoi,
Wednesday 16 January 2013 19:14
*sigh* Bulk import as a benchmark for OR/M vs NoSQL?? Wtf!!*Worst comparison ever*
Also
http://ayende.com/blog/4137/nhibernate-perf-tricks
By
qless,
Wednesday 16 January 2013 19:39
Please read again, this isn't a bulk insert, this is reading data, comparing with db, writing to db, one at a time, and the initial import hapens to have 12000 rows, but uses the same application logic as regular small imports.masterpoi wrote on Wednesday 16 January 2013 @ 19:14:
*Worst comparison ever*
Also
http://ayende.com/blog/4137/nhibernate-perf-tricks
If I would compare bulk inserts, which this isn't!, I would use native drivers. Small example:
Mongo with native drivers inserting 1.2 milion rows takes 2 minutes, using native oracle, about seven minutes.